Securing database traffic inside your network can be a great step for defense in depth. It’s also a necessity for Zero Trust Networks.
Both Amazon RDS and PgBouncer have built-in support for TLS, but it’s a little bit of work to get it set up. This tutorial will show you how.
The first step is to make sure all direct connections are secure. Luckily, Amazon RDS has a parameter named
rds.force_ssl for this. Once it’s applied, you’ll see an error if you try to connect without TLS. You can test this out with:
You’ll see an error like
FATAL: no pg_hba.conf entry ... SSL off if everything is configured correctly.
There are a number of possible values for
sslmode, which you can read about here. The most secure (and one we want) is
verify-full, as it provides protection against both eavesdropping and man-in-the-middle attacks. This mode requires you to provide a root certificate to verify against. AWS makes this certificate available on their website.
To use it with
Once connected, you should see an
SSL connection line before the first prompt.
There’s also an extension you can use (useful for non-
CREATE EXTENSION IF NOT EXISTS sslinfo;
Now direct connections are good, so let’s secure connections from PgBouncer to the database.
PgBouncer to the Database
Follow this guide to set up PgBouncer. Once that’s completed, there are two settings to add to
server_tls_sslmode = verify-full
server_tls_ca_file = /path/to/rds-combined-ca-bundle.pem
Restart the service
sudo service pgbouncer restart
And test it
The connection should succeed and the server should report SSL is used.
We’ve now successfully encrypted traffic between the bouncer and the database!
However, you’ll notice the
psql prompt does not have an
SSL connection line as it did before. You can also use
sslmode=disable to successfully connect, and programs like
tcpdump or tshark will show unencrypted traffic between the client and the bouncer. You can test this out with:
sudo tcpdump -i lo -X -s 0 'port 6432'
Run commands in
psql and you’ll see plaintext statements printed.
Clients to PgBouncer
We’ll use Minica. Install the latest version:
sudo apt-get install minica
minica --domains bouncerhost
We now have the files we need to connect. Add the key and certificates to
client_tls_sslmode = require # not verify-full
client_tls_key_file = /path/to/bouncerhost/key.pem
client_tls_cert_file = /path/to/bouncerhost/cert.pem
And restart the service. To connect, we once again use
verify-full but this time with the root certificate we generated above:
SSL connection line is printed and
sslmode=disable no longer works.
We’ve now successfully encrypted traffic end-to-end!
July 2018: Changed PKI example to Minica. Here are Vault instructions.