PostgreSQL SSL with Letsencrypt

2 min readFeb 14, 2019

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 0177export DOMAIN=example.com
export DATA_DIR=/var/lib/pgsql/datacp /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