Article

Use SSL Encryption with PostgreSQL on Ubuntu 20.04

Last Updated: Fri, Sep 10, 2021
PostgreSQL Security Ubuntu

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.

Want to contribute?

You could earn up to $600 by adding new articles.