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

sudo certbot certonly --standalone -d

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

umask 0177
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    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 -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, usename, datname, ssl, client_addr, backend_type, wait_event
FROM pg_catalog.pg_stat_ssl ssl, pg_catalog.pg_stat_activity a
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 — Free Cross-platform Desktop Client which I build. You may also find interesting — 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