How to Enable SSL for PostgreSQL connections
On this page
- Prerequisites
- Step 1 - Configure Firewall
- Step 2 - Install PostgreSQL 14
- Step 3 - Install SSL
- Step 4 - Configure Certbot renewal for PostgreSQL
- Step 5 - Configure PostgreSQL
- Step 6 - Configure PostgreSQL Connection
- Step 7 - Renew Certificate
- Step 8 - Test the Connection
- Step 9 - Check the Clients
- Conclusion
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 '<new_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.
$ 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 [email protected] -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.
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:[email protected]: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.
