The PostgreSQL that ships with most linux distributions is setup with Snakeoil TLS certificates by default. Managing these certificates is not any different to any other service, yet for some reason this is an often neglected task that leaves your database connections exposed to certain types of attack. This post discusses the — very few — steps required to use Let’s Encrypt certificates to secure your PostgreSQL sessions as well as an easy way to monitor your servers.
What’s the risk
Self signed certificates, particularly of the Snakeoil variety, offer no meaningful way for the TLS client to validate them. Marking the snakeoil certificate as trusted for the TLS client — i.e., telling the client that that particular certificate is to be trusted — is the only way that the model allows. This is almost never done because the task becomes more complex than running your own private CA with just a few clients.
Being unable to validate the certificate presented by the PostgreSQL server, the clients will have to operate without verify-full or verify-ca (see the PostgreSQL documentation). This means that clients will actually accept any TLS certificate that the server provides.
You should operate all your database connections with full TLS encryption. No less than
sslmode=require
Without the client validating the PostgreSQL server certificate, TLS offers no protection against Man-In-The-Middle attacks. MITMs happen when an attacker can position itself between the client and server, in a way that allows intercepting (and modifying!) traffic. The client believes it’s talking to the server and vice versa, with none the wiser.
The lesson is that while operating with sslmode=require provides some protection against snooping, anything less than sslmode=verify-full is leaving a potential attack vector.
Configuring certificates, the easy way
In Multiple Certs with Certbot and Wildcard Certificates with Let’s Encrypt, I introduced some tools that automate a rich workflow for managing my own certificates, complete with maintaining and reusing the certificate keys for using DNSSEC TLSA to Safeguard my Website Visitors.
For my setup, I create a certificate for the specific database server — db.lem.click in this example. You’ll need to use the latest version of my certificate environment and tools.
./bootstrap.sh db-test.lem.click
Then I edit db-test.lem.click/template.conf to tweak the certificate parameters. For this application, only the specific dns_name is required.
|
|
Then follow the instructions in my prior posts to generate your new certificate. Once you have it, simply tell your PostgreSQL server where to find it by editing the postgresql.conf file — the location of this file varies on an instance by instance basis — and on a distribution by distribution basis. Continuing with this example on my Debian linux server, this would be:
|
|
Make sure the permissions on the private key are tight. On my servers I use the permissions you can see below. Notice the certs group, of which postgres is part, so as to be able to have other programs read the private key to share the certificate if needed. Also note the lack of write permissions, as there’s no reason to allow changes to the private keys at any time.
$ ls -l /etc/letsencrypt/seed/db-test.lem.click/cert-0.key
-r--r----- 1 root certs 8167 Apr 1 04:20 /etc/letsencrypt/seed/db-test.lem.click/cert-0.key
At this point simply reloading your PostgreSQL server will cause the new certificates to be loaded and used.
sudo systemctl reload postgresql@9.6-mydbinstance.service
If all is well, your server will start using the new certificates for any inbound connection.
Keeping an eye on your certificates
PostgreSQL negotiates TLS in a manner resembling SMTP STARTTLS — there’s a command send from client to server requesting TLS to be setup. In order to verify the actual certificates being used by a PostgreSQL server, it’s necessary to establish a connection, send the right command, setup the TLS session and then inspect the certificates that were handed by the server.
This is precisely what the latest version of my ccheck tool does. You can easily see it in action as follows:
$ ccheck --tap --postgres babar.elephantsql.com:5432
TAP version 13
not ok 1 - babar.elephantsql.com:5432 x509: certificate is valid for ip-10-164-15-12.ec2.internal, not babar.elephantsql.com
1..1
In the example above, the TLS certificate presented by the PostgreSQL server babar.elephantsql.com was verified. This is an example of how a self signed TLS certificate would look like. For valid certificates, the remaining period can be verified and an alert issued when the expiration is near. I use a script similar to the following as a daily cron job that tells me when the expiration of the certificate is close enough to warrant manual intervention.
$ ccheck --quiet --postgres --min-days 180 db-test.lem.click:5432
db-test.lem.click:5432: expires in 85 days
Just change the 180 to whatever number of days you feel is appropriate. In my case, I use 20 days as my automation will typically renew my certificates automatically at the 30 day mark. When enough life is left in the certificate, the above script produces no output — hence no email from cron. When the expiration is closer than the indicated --min-days threshold, output like the above is produced, which is sent to me via email so that I can troubleshoot the situation.