How to Enable SSL for PostgreSQL connections

By default, all PostgreSQL connections are insecure, which can cause security issues when run in high-traffic production environments. SSL encryption ensures that any data transferred is not intercepted by anyone in the middle of a connection.

This tutorial will teach you how to enable SSL/TLS for PostgreSQL connections.

Prerequisites

• A Linux server. For our tutorial, we are running a Ubuntu 22.04 server.
• A non-root sudo user.
• A fully qualified domain name (FQDN) like postgresql.example.com.
• Make sure everything is updated.
• $ sudo apt update
  $ sudo apt upgrade
• Few packages that your system needs.
• $ sudo apt install curl nano software-properties-common apt-transport-https ca-certificates lsb-release ubuntu-keyring -y
• Some of these packages may already be installed on your system.

Step 1 — Configure Firewall

Before installing any packages, the first step is configuring the firewall to open ports for HTTP, HTTPS, and PostgreSQL.

Check the status of the firewall.

$ sudo ufw status

You should see something like the following.

Status: active



To                         Action      From

--                         ------      ----

OpenSSH                    ALLOW       Anywhere

OpenSSH (v6)               ALLOW       Anywhere (v6)

Open the HTTP, HTTPS, and PostgreSQL ports in the firewall.

$ sudo ufw allow 5432

$ sudo ufw allow http

$ sudo ufw allow https

Check the status again to confirm.

$ sudo ufw status

Status: active



To                         Action      From

--                         ------      ----

OpenSSH                    ALLOW       Anywhere                  

80/tcp                     ALLOW       Anywhere                  

443                        ALLOW       Anywhere                  

5432                       ALLOW       Anywhere                  

OpenSSH (v6)               ALLOW       Anywhere (v6)             

80/tcp (v6)                ALLOW       Anywhere (v6)             

443 (v6)                   ALLOW       Anywhere (v6)

5432 (v6)                  ALLOW       Anywhere (v6)

Step 2 — Install PostgreSQL 14

Ubuntu 22.04 ships with PostgreSQL 14 by default. To install, issue the following command.

$ sudo apt install postgresql postgresql-contrib

The postgresql-contrib package contains some extra utilities.

You can also use PostgreSQL's official APT repository to install. Run the following command to add the PostgreSQL GPG key.

$ curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql-key.gpg >/dev/null

Add the APT repository to your sources list.

$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/postgresql-key.gpg arch=amd64] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update the system repository.

$ sudo apt update

Now, you can install PostgreSQL using the command mentioned above.

Check the status of the PostgreSQL service.

$ sudo systemctl status postgresql

? postgresql.service - PostgreSQL RDBMS

     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)

     Active: active (exited) since Mon 2022-12-12 00:01:06 UTC; 19s ago

   Main PID: 3497 (code=exited, status=0/SUCCESS)

        CPU: 1ms



Dec 12 00:01:06 postgresql systemd[1]: Starting PostgreSQL RDBMS...

Dec 12 00:01:06 postgresql systemd[1]: Finished PostgreSQL RDBMS.

You can see that the service is enabled and running by default.

Set the password for the postgres account.

$ sudo -i -u postgres psql -c "ALTER USER postgres PASSWORD '';"

Step 3 — Install SSL

To install an SSL certificate using Let’s Encrypt, we need to download the Certbot tool. We will use the Snapd package installer for that. Ubuntu 22.04 comes pre-installed with Snap.

Ensure that your version of Snapd is up to date.

$ sudo snap install core 

$ sudo snap refresh core

Install Certbot.

$ sudo snap install --classic certbot

Use the following command to ensure that the Certbot command runs by creating a symbolic link to the /usr/bin directory.Advertisement

$ sudo ln -s /snap/bin/certbot /usr/bin/certbot

Generate an SSL certificate.

$ sudo certbot certonly --standalone --agree-tos --no-eff-email --staple-ocsp --preferred-challenges http -m name@example.com -d postgresql.example.com

The above command will download a certificate to the /etc/letsencrypt/live/postgresql.example.com directory on your server.

Generate a Diffie-Hellman group certificate.

$ sudo openssl dhparam -dsaparam -out /etc/ssl/certs/dhparam.pem 4096

Step 4 — Configure Certbot renewal for PostgreSQL

PostgreSQL does not have permission to access the certificates from the Let’s Encrypt folder therefore we cannot tell it to use the certificates from the folder directly. The alternative is to copy the certificates to the PostgreSQL directory but that works only temporarily as they will get expired and you will have to copy them again manually.

The best method is to use a renewal hook which will run automatically on every renewal and perform the copy operations.Advertisement

Look up the PostgreSQL data directory.

$ sudo -i -u postgres psql -U postgres -c 'SHOW data_directory'

Create the renewal hook file and open it for editing.

$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh

Paste the following code in it.

#!/bin/bash

umask 0177

DOMAIN=postgresql.example.com

DATA_DIR=/var/lib/postgresql/15/main

cp /etc/letsencrypt/live/$DOMAIN/fullchain.pem $DATA_DIR/server.crt

cp /etc/letsencrypt/live/$DOMAIN/privkey.pem $DATA_DIR/server.key

chown postgres:postgres $DATA_DIR/server.crt $DATA_DIR/server.key

# only for SELinux - CentOS, Red Hat

# chcon -t postgresql_db_t $DATA_DIR/server.crt $DATA_DIR/server.key

Save the file by pressing Ctrl + X and entering Y when prompted.

Make the file executable.

$ sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.sh

Step 5 — Configure PostgreSQL

Find the path for the PostgreSQL configuration file.

$ sudo -i -u postgres psql -U postgres -c 'SHOW config_file'

Open the file for editing.

$ sudo nano /etc/postgresql/15/main/postgresql.conf

Locate the Connection Settings section and uncomment the listen_address variable and change its value to *. Make sure it looks as follows.

listen_address = '*'		# what IP address(es) to listen on;

Locate the SSL section and edit the file to match the following values.

ssl = on  

ssl_cert_file = 'server.crt'  

ssl_key_file = 'server.key'  

ssl_prefer_server_ciphers = on

ssl_dh_params_file = '/etc/ssl/certs/dhparam.pem'

Save the file by pressing Ctrl + X and entering Y when prompted.

Step 6 — Configure PostgreSQL Connection

Open the /etc/postgresql/15/main/pg_hba.conf file for editing.

$ sudo nano /etc/postgresql/15/main/pg_hba.conf

Add the following line to enable SSL for PostgreSQL.

hostssl all  all  0.0.0.0/0  scram-sha-256

Save the file by pressing Ctrl + X and entering Y when prompted.

Step 7 — Renew Certificate

Run the following command to perform a forced renewal. This will trigger the deployment script which copies the certificates to the correct location for PostgreSQL to use.

$ sudo certbot renew --force-renewal

Verify that the certificates are copied to the PostgreSQL data directory.

$ sudo ls /var/lib/postgresql/15/main/

You will see the following output which will show you the certificates.

base          pg_dynshmem   pg_notify    pg_snapshots  pg_subtrans  PG_VERSION  postgresql.auto.conf  server.crt

global        pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_wal      postmaster.opts       server.key

pg_commit_ts  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  pg_xact     postmaster.pid

Restart PostgreSQL to apply the changes.

$ sudo systemctl restart postgresql

Step 8 — Test the Connection

Connect to the database from another machine with the PostgreSQL client installed.

$ psql -d "dbname=postgres sslmode=require" -h postgresql.example.com -U postgres

You should see the following PostgreSQL prompt. We are using a client with PostgreSQL 14 client therefore, you will see a warning about incompatible versions.

Password for user postgres:

psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1), server 15.1 (Ubuntu 15.1-1.pgdg22.04+1))

WARNING: psql major version 14, server major version 15.

         Some psql features might not work.

SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

Type "help" for help.



postgres=#

This confirms a successful SSL connection.

Exit the shell.

postgres=# \q

If your app is using a connection string, use it in the following format for an SSL connection.

postgres://user:password@postgresql.example.com:5432/database_name?sslmode=require

You can change the SSL mode to verify-full or verify-ca if you have the root certificate for Let's encrypt available in the /var/lib/postgresql/.postgresql location on the client side.

Create the /var/lib/postgresql/.postgresql directory.

$ sudo mkdir -p /var/lib/postgresql/.postgresql

The Let’s Encrypt root certificate is ISRG Root X1 which is located on the server at the /usr/share/ca-certificates/mozilla/ISRG_Root_X1.crt location.

Copy the root certificate to the /var/lib/postgresql/.postgresql directory.

$ sudo cp /usr/share/ca-certificates/mozilla/ISRG_Root_X1.crt /var/lib/postgresql/.postgresql/root.crt

Test the connection using verify-full or verify-ca mode and you should see a successful connection.

Step 9 — Check the Clients

Log in to the PostgreSQL shell on the server.

$ sudo -i -u postgres psql

Run the following SQL command to check the connected clients.

SELECT ssl.pid, usename, datname, ssl, ssl.version, ssl.cipher, ssl.bits, client_addr

FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity activity

WHERE ssl.pid = activity.pid;

You should see a similar output.

pid  | usename  | datname  | ssl | version |         cipher         | bits |  client_addr

------+----------+----------+-----+---------+------------------------+------+----------------

 5126 | postgres | postgres | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384 |  256 | 122.161.84.220

 5154 | postgres | postgres | f   |         |                        |      |

(2 rows)

This confirms the client connection from the server side.

Conclusion

This concludes the tutorial on enabling SSL on PostgreSQL connections. If you have any questions, post them in the comments below.

How to Enable SSL for PostgreSQL connections