Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more

Security

How To Improve PgBouncer Security with TLS/SSL

Avatar for David Youatt

David Youatt

10 min read

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:

  1. Generate a private key (you must provide a passphrase).

    openssl genrsa -des3 -out server.key 1024

  2. Remove the passphrase (but remember it).

    openssl rsa -in server.key -out server.key

  3. Set appropriate permission and owner on the private key file.

    chmod 400 server.key
chown postgres.postgres server.key

  4. 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 the CN= 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.

  1. 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.

  2. Build and manage your own local CA.

    For help building a local, private CA, see one of these:

  3. 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:

  1. 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"

  2. 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

  3. 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.

  4. Update the PgBouncer config to use certificate authentication with an acceptable level of signature verification. Edit your pgbouncer.ini file and set

    auth_type = cert
;; required for cert auth
client_tls_sslmode = verify-full

    Restart PgBouncer

  5. Test

    1. 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

    2. Test with PGSSLMODE=require vs PGSSLMODE=verify-ca vs PGSSLMODE=verify-full client-side environment variables.

    3. 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

    4. 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 user

      testuser1

      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.

Related Articles