By default, most installations of PostgreSQL use insecure connections instead of encrypted connections. This guide explains how to use a free Let's Encrypt certificate to secure connections to your PostgreSQL server.
Before beginning this guide:
Deploy an Ubuntu 20.04 LTS cloud server at Vultr.
Create a fully-qualified domain name (DNS "A" record) that points to your server's IP address.
Install the main PostgreSQL packages.
$ sudo apt install postgresql postgresql-contrib
Set the password for the postgres account.
$ sudo -u postgres psql -c "ALTER USER postgres PASSWORD '<new_password>';"
Certbot is the free tool to automatically request Let's Encrypt certificates.
Follow our guide to install Certbot with Snap.
Request a certificate for your server. Replace psql.example.com with the fully-qualified domain name of your server.
$ sudo certbot certonly --standalone -d psql.example.com
Certbot's certificates are only accessible by root. To allow PostgreSQL to use the certificate, it must create a copy with a Certbot renewal hook.
Look up the PostgreSQL data directory. You'll use this value in the renewal hook file.
$ sudo -u postgres psql -U postgres -c 'SHOW data_directory'
Create the renewal hook file.
$ sudo nano /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
Paste the following. Replace psql.example.com with your server's fully-qualified domain name. Replace the value for DATA_DIR with your PostgreSQL data directory.
#!/bin/bash
umask 0177
DOMAIN=psql.example.com
DATA_DIR=/var/lib/postgresql/12/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
Save and exit the file.
Give the file executable permissions.
$ sudo chmod +x /etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy
Get the path of the PostgreSQL configuration file:
$ sudo -u postgres psql -U postgres -c 'SHOW config_file'
Edit the file shown by the previous command. For example:
$ sudo nano /etc/postgresql/12/main/postgresql.conf
Locate the SSL section and edit your file to match these SSL settings:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_prefer_server_ciphers = on
Locate the Connection Settings section and verify the listen_address is to * for all addresses. Make sure the line is not commented out. For example:
listen_address = '*'
Save and exit the file.
Get the path of the PostgreSQL configuration file:
$ sudo -u postgres psql -U postgres -c 'SHOW config_file'
Edit the pg_hba.conf file, which is in the same directory as the configuration file. For example:
$ sudo nano /etc/postgresql/12/main/pg_hba.conf
Add the following line to enable secure SSL traffic from the internet.
hostssl all all 0.0.0.0/0 md5
Optionally, to also allow insecure connections, add the following line:
host all all 0.0.0.0/0 md5
Save and exit the file.
Perform a forced renewal, which triggers the Certbot renewal hook to copy the certificates to the correct location for PostgreSQL.
$ sudo certbot renew --force-renewal
Look up the PostgreSQL data directory.
$ sudo -u postgres psql -U postgres -c 'SHOW data_directory'
Verify that Certbot copied the certs to the PostgreSQL data directory. For example:
$ sudo ls /var/lib/postgresql/12/main/server.*
Restart PostgreSQL
$ service postgresql restart
Connect to the database from another machine with the PostgreSQL client installed. Replace psql.example.com with your server's fully qualified domain name.
$ psql -d "dbname=postgres sslmode=require" -h psql.example.com -U postgres
You should see the PostgreSQL prompt.
Password for user postgres:
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Type \Q to exit the PostgreSQL client.
postgres=# \q
See the official documentation for more information.