Use SSL Encryption with PostgreSQL on Ubuntu 20.04

Introduction

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.

Prerequisites

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.

1. Install PostgreSQL

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>';"

2. Install Certbot and Certificate

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

3. Create a Certbot Renewal Hook for PostgreSQL

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.

1. 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'
   

2. Create the renewal hook file.

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

3. 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
   

4. Save and exit the file.

5. Give the file executable permissions.

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

4. Configure PostgreSQL for SSL

1. Get the path of the PostgreSQL configuration file:

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

2. Edit the file shown by the previous command. For example:

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

3. 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
   

4. 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 = '*'
   

5. Save and exit the file.

5. PostgreSQL Connection Configuration

1. Get the path of the PostgreSQL configuration file:

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

2. 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
   

3. 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
   

4. Save and exit the file.

6. Renew the Certificate

1. 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
   

2. Look up the PostgreSQL data directory.

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

3. Verify that Certbot copied the certs to the PostgreSQL data directory. For example:

   $ sudo ls /var/lib/postgresql/12/main/server.*
   

4. Restart PostgreSQL

   $ service postgresql restart
   

7. Test the Connection

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

More Information

See the official documentation for more information.