Cobra Softwares Blog

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

postgres=# \q

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…

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…