How to use SSL in PostgreSQL The Right Way: Encrypt Your Data in Transit

Let's say you are using PostgreSQL (and who doesn't these days, really).

PostgreSQL supports SSL connections, so that complete traffic between your database client and a database server is encrypted.

Let's connect to the database with psql:

psql -h mkdev-demo.cbyqoievqh37.eu-central-1.rds.amazonaws.com -U mkdev mkdev_demo

This works great and in fact requires SSL by default. We can verify this by running a simple query:

select datname, usename, ssl, client_addr
from pg_stat_ssl
inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid
where ssl is true;

As indicated by "ssl" column, connection is encrypted.

But is that enough?

When we open a website with https in the browser, we see a lock icon telling us that this website is safe.

This lock is there because our browser or our laptop trusts the certificate of the website.

And it trusts the certificate by trusting one of may certificate authorities that come pre-installed on your system.

In this case, certificate of the website is verified by Let's Encrypt and my laptop trusts Let's Encrypt Certificate Authority.

Now going back to the database connection, we never specified which certificate authorities we trust when we connect to the database server.

Our database client blindly trusts any database server, without checking it's identity.

Even though the connection is encrypted, we don't know if the database server is the one we want to connect to.

Imagine a hacker tricking you into writing your data into a fake database and potentially stealing huge amounts of valuable information from you.

PostgreSQL clients support multiply modes to enforce ssl. The one that we saw before is called "require". What it does is it forced connection to go over SSL.

But if we want to make sure that we connect to the right server, we should use "verify-full" option.

With this option, client will not only check it connects to a trusted server, it will also check it's exactly the server that was requested.

Let's see this configuration in action.

We can enable verify-full mode with a simple environment variable:

export PGSSLMODE=verify-full

If we try to connect now, we will get an error:

psql -h mkdev-demo.cbyqoievqh37.eu-central-1.rds.amazonaws.com -U mkdev mkdev_demo

This error happens, because client tries to verify the server, but can not find any certificate authority to verify the identity of the server.

This is why we need to set another environment variable, that will point to the certificate:

PGSSLROOTCERT=/etc/ssl/certs/rds-ca-2019-root.pem

With both variables in place, we are able to connect to the database.

Now we are 100 % sure that data is encrypted in transit and that we send data to a trusted server.

One final thing to note is that enabling verify-full ssl mode can impact the performance.

It takes extra processing power to encrypt and decrypt the data flow between the client and the server, so in some cases have to be extra carefull with this configuration.

DevOps consulting: DevOps is a cultural and technological journey. We'll be thrilled to be your guides on any part of this journey. About consulting