PostgreSQL SSL with Letsencrypt

This tutorial will explain how to setup PostgreSQL server with SSL certificates provided by letsencrypt.

1. Install certbot

Follow instructions at https://certbot.eff.org/

2. Generate Certificate for Your Domain

sudo certbot certonly --standalone -d example.com

If you already have webserver running on your database server then replace --standalone with --webroot or something else according to your webserver

3. Verify Generated Certificate

sudo certbot certificates

4. Create Renewal Hook

Create file/etc/letsencrypt/renewal-hooks/deploy/postgresql.deploy

#!/bin/bash
umask 0177
export DOMAIN=example.com
export DATA_DIR=/var/lib/pgsql/data
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
# only for SELinux - CentOS, Red Hat
# chcon -t postgresql_db_t $DATA_DIR/server.crt $DATA_DIR/server.key

Make it executable:

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

5. Configure postgresql.conf

File postgresql.conf usually located in data folder at /var/lib/pgsql/data , if you can not find it, check it with psql: sudo -u postgres psql -U postgres -c ‘SHOW config_file’

Find SSL section and change these lines:

# - SSL -
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_prefer_server_ciphers = on

6. Configure pg_hba.conf

File pg_hba.conf is in same folder as postgresql.conf and we will need to add new line with hostssl. If you want to allow external connections from all IPs to add databases then new line should look like this

hostssl    all    all    0.0.0.0/0    md5

(would be more secure to allow access only to your application subnet and only to few databases)

7. Restart PostgreSQL Server

# on Ubuntu and Debian:
service postgresql restart
# CentOS, Fedora and Red Hat
systemctl restart postgresql

8. Try Connecting

psql -h example.com -U YOUR_USER

And you should see something like this:

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)

*SSL compression is usually disabled because of CRIME issue in SSL

9. Make Sure Clients Uses SSL

SELECT * from pg_catalog.pg_stat_ssl

list of connections, including system connections from postgres itself

To get better view we can combine with pg_stat_activity

SELECT ssl.pid, usename, datname, ssl, client_addr, backend_type, wait_event
FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity a
WHERE ssl.pid = a.pid

Column ssl indicates that all external clients use SSL 🎉

If some of the clients still don’t use SSL try to set connection URL param: sslmode=require or environment variable PGSSLMODE=require

Links: About sslmode, Postgres official docs for SSL

Screenshots rendered by Postbird.app — Free Cross-platform Desktop Client which I build