PostgreSQL SSL with Letsencrypt

Image for post
Image for post

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

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

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

sudo certbot certificates

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

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

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)

# on Ubuntu and Debian:
service postgresql restart
# CentOS, Fedora and Red Hat
systemctl restart postgresql
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

SELECT * from pg_catalog.pg_stat_ssl
Image for post
Image for post
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
Image for post
Image for post
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. You may also find interesting Kuber.host — kubernetes cloud with postgres database (have free plan)

Written by

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store