How to configure PostgreSQL to use Let’s Encript certificates

Motivation

Recently for a client I needed to setup a PostgreSQL instance, public available, so the challenge of encrypted connections was there since day one.

After digging on the internet, and putting pieces together I found a reliable solution deployed and working on Debian Buster

Preconditions

• PostgreSQL 13 (this will work only for versions 10+ since before there is not hot reload of certificates)
• certbot installed on debian
• Apache installed on debian

Configuring certbot

Once we have the website enabled on Apache we need to setup certbot to generate certificates for our domain.

Assuming postgres.mydomain.com as a subdomain we run certbot

certbot --apache -d postgres.mydomain.com --post-hook "/var/postgres.mydomain.com/certbot/postgres.mydomain.com.post-hook.sh"

Here they key is the post hook script, let’s look at the contents:

#!/bin/bash

/usr/sbin/apache2ctl graceful
cp /etc/letsencrypt/live/postgres.mydomain.com/fullchain.pem /var/lib/postgresql/13/main/fullchain.pem-1
cp /etc/letsencrypt/live/postgres.mydomain.com/privkey.pem /var/lib/postgresql/13/main/privkey.pem-1
chmod 600 /var/lib/postgresql/13/main/fullchain.pem-1 /var/lib/postgresql/13/main/privkey.pem-1
chown postgres:postgres /var/lib/postgresql/13/main/fullchain.pem-1 /var/lib/postgresql/13/main/privkey.pem-1
mv -f /var/lib/postgresql/13/main/fullchain.pem-1 /var/lib/postgresql/13/main/fullchain.pem
mv -f /var/lib/postgresql/13/main/privkey.pem-1 /var/lib/postgresql/13/main/privkey.pem

certbot will create two links to the actual downloaded certificates

/etc/letsencrypt/live/postgres.mydomain.com/fullchain.pem
/etc/letsencrypt/live/postgres.mydomain.com/privkey.pem

We will have to copy them to PostgreSQL $PGDATA but the important piece is that we will not replace the certificates until they have the right permission and the correct owner, to make sure postgres will be able to access them.

PostgreSQL configuration

The following is required on postgresql.conf file

ssl = on
ssl_cert_file = '/var/lib/postgresql/13/main/fullchain.pem'
ssl_key_file = '/var/lib/postgresql/13/main/privkey.pem'