Knowledgebase

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 Rcs.
• 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 BackslashQ to exit the PostgreSQL client.

postgres=# \q

More Information

See the official documentation for more information.