pgbouncer and scram-sha-256 setup

Question

I was able to get SCRAM-SHA-256 authentication to work with pgpool, but I haven't been able to find a good example how to set this up in pgbouncer. I'm trying to use auth_query. In postgres, the user that pgbouncer will connect as has the password encrypted and stored in SCRAM-SHA-256. But I don't know how to create the entries in userlist.txt. This is supposed to be the format:

SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>

What exactly are the storedkey and serverkey and how do I generate those? Which tools can I use to create this? In pgpool, I can use pg_enc but I don't see anything for pgbouncer.

Answer 1

You don't construct the SCRAM hashed password yourself, you get it by querying the pg_authid table in the PostgreSQL database:

SELECT rolpassword
FROM pg_authid
WHERE rolname = 'pgbouncer';

However, as the documentation says:

The passwords or secrets stored in the authentication file serve two purposes. First, they are used to verify the passwords of incoming client connections, if a password-based authentication method is configured. Second, they are used as the passwords for outgoing connections to the backend server, if the backend server requires password-based authentication (unless the password is specified directly in the database's connection string). The latter works if the password is stored in plain text or MD5-hashed. SCRAM secrets can only be used for logging into a server if the client authentication also uses SCRAM, the PgBouncer database definition does not specify a user name, and the SCRAM secrets are identical in PgBouncer and the PostgreSQL server (same salt and iterations, not merely the same password). This is due to an inherent security property of SCRAM: The stored SCRAM secret cannot by itself be used for deriving login credentials.

So if that user is used as auth_user, you cannot use a SCRAM hashed password for that user, but you have to use the clear text password.