How to SCRAM in Postgres with pgBouncer

I've made it no secret that I am a huge fan of PostgreSQL's SCRAM support: it provides a secure, standardized way of performing authentication with passwords, and is a method that can be universally applied.

The beauty of SCRAM is that both authenticating parties (in this case, your client/application and PostgreSQL) can both verify that each party knows a secret without ever exchanging the secret. In this case, the secret is a PostgreSQL password!

That is incredible, and is also a huge step up from PostgreSQL's previous password methods. If you're curious about how SCRAM works in PostgreSQL, I invite you to review one of my presentations on the topic, or stay tuned as I give a brief overview in this blog!

Given SCRAM's advantages, and given that the universe of PostgreSQL connection drivers now support it, SCRAM is going to be the default method in PostgreSQL 14 (in which case, you should upgrade your passwords to use SCRAM).

Additionally, PgBouncer, a connection pooler and state manager for PostgreSQL, added support for SCRAM in a way that is very impressive. It's so impressive, in fact, that I wanted to write an article about why it works so well, and how you can deploy PostgreSQL with PgBouncer and safely SCRAM!

A Brief Overview of SCRAM in PostgreSQL

As mentioned above, the beauty of SCRAM is that two parties can verify that each knows a secret without every exchanging the secret itself. This is a very powerful notion: even if someone is eavesdropping on the traffic and witnessing the SCRAM exchange, they will have a very tough time coming up with the secret on their own.

The basis of SCRAM is that both a client (e.g. your application [likely your PostgreSQL driver]) and a server (e.g. PostgreSQL) will send each other a series of cryptographic proofs stating demonstrating that they know the secret (i.e. the password). The proofs utilize a series of one-time information (nonces, information about the connection) as well as a few cryptographic elements that are derived during the process, including a:

• "Client Key"
• "Server Key"
• "Stored Key"

We'll define what these are below. Note that before performing SCRAM authentication, the server will need to have knowledge of the "server key" and "stored key".

So, what are these keys? Let's start with the "Client Key":

To create a client key, one must take a plaintext password, apply a PBKDF2 type function over it to generate a salted hashed password, and then make a HMAC with the literal string "Client Key" as the cryptographic key and the salted-hashed password as the message.

A similar method is employed to create a server key, where the HMAC cryptographic key is the literal string "Server Key":

Finally, to create the Stored Key, one takes a SHA hash of the client key. In the case of PostgreSQL's current SCRAM support, this is a SHA-256 hash:

As part of the SCRAM authentication workflow, the client (e.g. your application) sends the server a "client proof" that demonstrates that it knows the secret. The client proof is the "client key" XOR'd with a "client signature", where the "client signature" is a HMAC with part of the one-time information used as the signing key, and a message containing the "stored key". The client sends the "client proof" to PostgreSQL.

As PostgreSQL already has the "stored key", it is able to create the "client signature". PostgreSQL then performs an XOR between its client signature and the "client proof" to yield a "client key." It checks if the "client key" is valid by taking a SHA-256 hash of it -- if it matches the stored key, then the client (e.g. your application) has proven it knows the password. The client is authenticated!

Note the above bit -- this will be important in our discussion about PgBouncer.

Also note that to finish the SCRAM authentication process, the client then needs to validate that the server knows the secret as well. This is an important step for PostgreSQL client drivers to implement, otherwise a PostgreSQL server can just claim to know a password and do bad things.

SCRAM and PgBouncer

As mentioned above, the great thing about SCRAM is that it lets two parties verify that the other party knows the secret without every exchanging the secret. But what if we introduce a third party?

As mentioned above, PgBouncer is a connection pooler, and as such, authentication must happen twice:

• Between the application and PgBouncer
• Between PgBouncer and PostgreSQL

It's easy enough to provide SCRAM authentication between the application and PgBouncer, but if PgBouncer has no knowledge of the password, how can it authenticate to PostgreSQL?

Enter the "client key".

Recall that the "client key" is a HMAC of the salted hashed password. Also recall that the "stored key", in the case of PostgreSQL, is just a SHA-256 hash of the "client key".

Assuming that an application provides PgBouncer with a valid client proof, PgBouncer is able to get a valid "client key" from the application. All PgBouncer has to do is generate a valid "client proof" based upon the challenge that PostgreSQL presents to it, and as such, PgBouncer can then authenticate using SCRAM with PostgreSQL!

(Side note: this is also why it's very important to perform SCRAM authentication with a server you trust. In an untrusted network, you should run PgBouncer with TLS with a sslmode of verify-full or, based on your CA, verify-ca...and PostgreSQL with TLS as well. We'll save the discussion about SCRAM channel binding for a different blog post!).

This is pretty cool stuff. The next question is, how can I use it? PgBouncer lets you do so in two ways:

• Adding the SCRAM verifiers to the userlist.txt file that the pgbouncer.ini references
• Setting up an auth_query to be used by the pgbouncer.ini method

My preference is to use auth_query. For an example for how set up an auth_query, check out the PgBouncer initialization script as part of the Crunchy Container Suite project.

Conclusion: Extending SCRAM with Care

As the above demonstrates, SCRAM is a powerful authentication technique, and by knowing how to use it, you can leverage tools that can sit between PostgreSQL and your application without revealing your secret. However, as mentioned above, you do need to be aware of how these tools work: any intermediary that has your client key could end up impersonate your user account.

Thus, in production systems, and particularly those in untrusted network, you should combine SCRAM with TLS and, in particular, with a TLS mode that protects the client against MITM attacks.

That all said, PgBouncer's support for SCRAM is very awesome and I strongly encourage you to check it out. And upgrade your passwords to use SCRAM. 😀