How To Improve PgBouncer Security with TLS/SSL
PgBouncer is a commonly deployed and recommended connection pooler for PostgreSQL. It supports a number of authentication methods including TLS/SSL client certificate authentication.
Since PgBouncer is located logically between the client and PostgreSQL you have the option of using TLS and cert authentication from client to PgBouncer and from PgBouncer to PostgreSQL. In this brief blog post, we’ll describe configuring securing the client-to-PgBouncer transport first, then build on that to use client certificate authentication to PgBouncer.
A central part of this is TLS and tools for creating and maintaining keys, certificates, signing requests, signing and more. For this talk we use the widely used open source software OpenSSL, but any utilities that produce valid keys and certificates could be used.
The client certificates will need to be signed by the same CA (certificate authority) that signed the PgBouncer certificate. For testing and for this article we’ll use self-signed certificates but for production you should at least create a local CA, or preferably, use a public CA, though the latter can get expensive if you have many client certificates. Both PgBouncer and PostgreSQL have a configuration option that determines the level of root certificate verification, ranging from no verification to strict verification. This accommodates a range of uses, including self-signed certificates for internal use to more secure environments that must use certs signed by a public CA.
Testing for this post was done with PgBouncer 1.12.0 on Linux.
Creating a TLS certificate for PgBouncer
We’ll use openssl
to create a certificate for PgBouncer, to enable TLS transport security. Here are the steps:
Generate a private key (you must provide a passphrase).
openssl genrsa -des3 -out server.key 1024
Remove the passphrase (but remember it).
openssl rsa -in server.key -out server.key
Set appropriate permission and owner on the private key file.
chmod 400 server.key chown postgres.postgres server.key
Create the server certificate signing request. Note that this is where the process differs depending on whether you use self-signed certificates (like here) or create a CSR (certificate signing request) and send it to a CA to be signed, and who will return you the signed certificate, private key and root (or intermediate) certificates for your new signed cert. Note the
-x509
below that produces a self-signed certificate instead of a CSR, and-subj
is a shortcut to avoid prompting for the info and typing it interactively.
Creating a self-signed cert with the
-x509
argument. You probably don't want to do this in production:openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj '/C=US/ST=Washington/L=Redmond/O=Crunchy Data/CN=crunchy-testuser1/emailAddress=testuser1@example.com'
or instead, generate a CSR (certificate signing request) for a real certificate, use this and send the
.csr
file to your CA to be signed:openssl req -new -key server.key -out server.csr -subj '/C=US/ST=Washington/L=Redmond/O=Crunchy Data/CN=crunchy-testuser1/emailAddress=testuser1@example.com'
Your CA will return a signed certificate and key to you.
Change the
-subj
arg details for your environment of course, and note that theCN=
part of the cert's needs to be the hostname of your PgBouncer host. You can use SAN (Subject Alt Names) to define more than one hostname in the cert, but that's outside the scope of this post.
At this point, you have a signed certificate, its private key and a root certificate from the signing CA, or your self-signed cert.
Configuring PgBouncer to use TLS transport security (prerequisite for cert authentication)
Once you have a signed certificate for PgBouncer, configuring for TLS transport security is pretty straightforward.
You need to set these options in your pgbouncer.ini
file (/etc/pgbouncer/pgbouncer.ini
on Linux):
client_tls_sslmode = require
client_tls_ca_file = /etc/pgbouncer/root.crt
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_ciphers = normal
Note that there are stricter checking options for client_tls_sslmode
but the require
value will not allow non-TLS/SSL connections from clients. And we can restrict the allowed TLS cipher suites and versions with client_tls_ciphers
but one step at a time.
For now, leave auth_type
to something other than cert, for example md5
.
Restart PgBouncer using whatever your platform requires, for example systemctl restart pgbouncer
on a Linux system that uses systemd.
And let’s test that it’s working. Or not. Here’s a simple test using psql with auth_type = trust
in pgbouncer.ini
:
bash> psql "sslmode=require host=localhost port=6432"
Password for user testuser1:
psql (12.1)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.testuser1=# \q<
If the client requests a SSL connection, it succeeds.
bash> psql "host=localhost port=6432"
psql: ERROR: SSL required
bash>
If the client does not request a SSL connection, it fails.
OK. encrypted TLS connections between client and PgBouncer are working, and using a fairly secure cipher suite and TLS version. With client_tls_sslmode = require
, if the client doesn’t request a TLS/SSL connection, it’s denied. So we have the transport layer from client to PgBouncer using TLS.
Note that this process is almost identical to configuring PostgreSQL to use TLS/SSL transport authentication. Later, in a follow up blog post, we’ll see how to configure PgBouncer to use TLS/SSL from PgBouncer to the DB. But first, let’s continue to configure TLS client cert authentication to PgBouncer.
Client Certs and options for CA's
At this point, we have TLS transport encryption between client and PgBouncer configured and working. What's next is to create and deploy client certificates and enable cert authentication in PgBouncer.
These steps closely mirror the procedure described here in and earlier blog post on how to set up TLS authentication within Docker containers.
An important consideration and a choice to make when doing this is what you will use for a CA (Certificate Authority). There are at least three options, depending on your needs and requirements for security.
The simplest is to not have a CA and use self-signed certificates. This requires that you use one of the less strict verification options for
client_tls_sslmode
. It's also not recommended for use in production. For this method, we create a self-signed cert for the PgBouncer server, then use its private key to sign the client certs, and the PgBouncer cert is also the root CA cert.Build and manage your own local CA.
For help building a local, private CA, see one of these:
- the open source minica project https://github.com/jsha/minica
- the new open source mkcert project https://github.com/FiloSottile/mkcert
- Vault (if you’re already using it) can be a local CA
- Most Linux distros have tools and instructions for creating and managing a local CA, see for example the easy-rsa package, also see the official Ubuntu docs here: https://help.ubuntu.com/lts/serverguide/certificates-and-security.html
Use a public CA, though this can get expensive and complicated to administer if you have many clients (or clients and servers).
The high-level steps are:
Create the client certificate and Certificate Signing Request. The key thing here is that the CN (Common Name) in the client cert must be a valid user in the PostgreSQL instance.
openssl req -newkey rsa:4096 -keyout testuser1_key.pem -out testuser1_csr.pem -nodes -days 365 -subj "/CN=testuser1"
Sign the client certificate with the root certificate that's installed in PgBouncer. For the example here, we're using self-signed certs, so we'll use the private key for the PgBouncer server certificate to sign the client cert.
openssl x509 -req -in testuser1_csr.pem -CA server.crt -CAkey server.key -out testuser1_cert.pem -set_serial 01 -days 365
Install the signed client cert on the client(s).
cp server.crt ~/.postgresql/root.crt cp testuser1_cert.pem ~/.postgresql/postgresql.crt cp testuser1_key.pem ~/.postgresql/postgresql.key chmod 400 ~/.postgresql/postgresql.key
Note that there are number of connection parameters you can set for SSL/TLS and related environment variables.
Also note that these are for clients that use the
libpq
PostgreSQL library. If your client does not use it, see the docs for your client for TLS/SSL support and location of client certs and keys.Update the PgBouncer config to use certificate authentication with an acceptable level of signature verification. Edit your
pgbouncer.ini
file and setauth_type = cert ;; required for cert auth client_tls_sslmode = verify-full
Restart PgBouncer
Test
Simple psql test
testuser1-bash> psql -U testuser1 -p 6432 -h crunchy-testuser1 psql (12.1) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. testuser1=# \q
Test with
PGSSLMODE=require
vsPGSSLMODE=verify-ca
vsPGSSLMODE=verify-full
client-side environment variables.Try different DB username (fails)
testuser1-bash> psql -U postgres -p 6432 -h crunchy-testuser1 psql: error: could not connect to server: ERROR: TLS certificate name mismatch
Copy client cert and key from user
testuser1
to different user's~/.postgresql
and try connecting as that user (fails)testuser-bash> PGSSLMODE=verify-full psql -h crunchy-testuser1 -p 6432 psql: error: could not connect to server: ERROR: TLS certificate name mismatch
but note this, logged in as user
testuser
and you have installed the key and cert for usertestuser1
testuser-bash> PGSSLMODE=verify-full psql -U testuser1 -h crunchy-testuser1 -p 6432 psql (12.1) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. testuser1=#
The moral for this test: Protect your key and password.
Note on this test environment
For this article, we're implementing to a common configuration, where PgBouncer and PostgreSQL are both running on the same host, and we use only local connections (Unix domain sockets) from PgBouncer to PostgreSQL.
Here's the pgbouncer.ini
from the test environment:
[databases]
;; Three DB's, with PgBouncer connecting only on local/UDS
testuser1 =
template1 =
postgres =
[users]
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
client_tls_sslmode = verify-full
client_tls_ca_file = /etc/pgbouncer/root.crt
client_tls_key_file = /etc/pgbouncer/server.key
client_tls_cert_file = /etc/pgbouncer/server.crt
client_tls_ciphers = normal
auth_type = cert
auth_file = /etc/pgbouncer/userlist.txt
admin_users = testuser1
Contents of /etc/pgbouncer/pgbouncer.ini
:
bash> cat /etc/pgbouncer/userlist.txt
"testuser1" "<hashed password from pg_shadow for user testuser1 here>"
"testuser2" "<hashed password from pg_shadow for user testuser2 here>"
And contents of ${PGDATA}/pg_hba.conf
. Note that this PostgreSQL instance is listening only on localhost
and on a local Unix domain socket:
# Database administrative login by Unix domain socket
local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
local all testuser1 md5
local all testuser2 md5
# "local" is for Unix domain socket connections only
local all all peer
#
# IPv4 local connections:# IPv6 local connections:
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
Note that for user postgres
, peer
auth works because PgBouncer is running as user postgres
in this environment.
For authenticating from PgBouncer to PostgreSQL, we're still relying on passwords (md5 hashed in this case).
Connection from PgBouncer to PostgreSQL
At this point, we have a secure transport from the app client to PgBouncer, the client-supplied DB username (which can be specified in the client) must match the CN in the client certificate, and the hostname parameter of the connection string from the client must match the signer (Issuer) in the client cert. On the client/app side, you can adjust how strict validation is using the PGSSLMODE
environment variable for apps that use libpq
.
But we're still relying on the credentials in PgBouncer's userlist.txt
auth_file
parameter together with PostgreSQL's pg_hba.conf
file to authenticate from PgBouncer to PostgreSQL.
In a follow up blog post, we will describe how to reduce the overhead of managing passwords in the auth_file
using the method described in Doug Hunley's post here: https://hunleyd.github.io/posts/pgbouncer-and-auth-pass-thru/
Certificate Authentication from PgBouncer to PostgreSQL
Another common PgBouncer configuration is where the PgBouncer service does not reside on the DB server. It might be on a dedicated server, or there may be a PgBouncer service on each of several app or web servers.
In that case you will have one or more TCP connections from each PgBouncer service to the PostgreSQL server that can be secured using TLS transport as well as configured to user cert auth to the PostgreSQL DB. The configuration is very similar to the description above but in this case, you configure the PgBouncer services to each have a client cert and use hostssl ... cert
authentication in the PostgreSQL server after you have enabled SSL in the DB server and configured TLS certs for it and the PgBouncer clients.
That's a topic for another blog post.
To prepare, and to enable SSL/TLS in the PostgreSQL DB, start with https://www.postgresql.org/docs/current/ssl-tcp.html and https://www.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers.