If you are still using md5 in PostgreSQL/PgBouncer today “because it has always worked,” you are heading towards a forewarned problem:PostgreSQL has already deprecated MD5and warns thatit will be removed in a future major version. PostgreSQL+1
The real alternative (and the one worth understanding) isSCRAM-SHA-256.
1) What is SCRAM-SHA-256 in one sentence (and why it matters)
SCRAM-SHA-256is a challenge-response authentication mechanismthat: que:
prevents the password from traveling “usable” over the network(reduces the risk of sniffing),
and allows the servernot to store the password, butderived secrets(more resistant to leaks). PostgreSQL+2IETF Datatracker+2
PostgreSQL implements it as the scram-sha-256 method (based on RFC 7677). PostgreSQL
2) The differential 🔥: “SCRAM is not a hash like MD5”
With md5, many people think: “I store the hash, done.” The problem is thatthat hash ends up being a kind of reusable passwordin various scenarios.
With SCRAM, what is stored isa package of secrets(derived with salt + iterations) thatare used to verify cryptographic proofs, not to reconstruct a “ready-to-use password.”
In fact, PgBouncer explains it very directly:
“The stored SCRAM secret cannot, by itself, be used to derive login credentials..” PgBouncer
That point (which seems theoretical) is what saves you from various attacks… and it’s also what generates “weird bugs” if you don’t understand it when integrating PgBouncer.
3) How SCRAM works (without smoke): the mini-handshake
Instead of sending a password, SCRAM does a back-and-forth with:
noncefrom the client + nonce from the server (prevents replay),
salt + iteration count(hardens against brute force),
aclient proof(“client proof”) and aserver signature(mutual verification). IETF Datatracker+2Crunchy Data+2
Mental idea:
“I prove to you that I know the secretwithout showing it to you..”
4) What PostgreSQL really stores (and how you verify it)
In PostgreSQL, the SCRAM password is stored (in pg_authid.rolpassword) in this format:
SCRAM-SHA-256$<iterations>:<salt>$<StoredKey>:<ServerKey> PostgreSQL
Example of a check (only for superuser/roles with access):
SELECT rolname,
rolpassword ~ '^SCRAM-SHA-256\$' AS is_scram
FROM pg_authid
WHERE rolcanlogin
ORDER BY 1;
5) Why PgBouncer + SCRAM is where security is won (or broken)
PgBouncer has its own client authentication (auth_type) and its own "directory" of credentials (auth_file).
5.1 auth_file with SCRAM (yes, it can be done)
PgBouncer documents the auth file format as follows:
"username" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"
and notes that the file serves two purposes:
to authenticate clients entering PgBouncer
to authenticate outgoing connections to PostgreSQL (if the DB requires it) PgBouncer
5.2 The "deadly detail" (🔥) when PgBouncer also needs to log into Postgres
PgBouncer warns of a key limitation:
SCRAM secrets do NOT always work for logging into Postgresunless specific conditions are met (same salt/iterations/secrets on both sides, etc.). PgBouncer
Practical translation:
You can have perfect SCRAM forclient → PgBouncer,
but fail atPgBouncer → PostgreSQLif you didn't align how that secret was stored.
6) Clean migration from MD5 to SCRAM (without cutting production)
Step A — Enable new passwords to be stored as SCRAM
In PostgreSQL:
password_encryption = scram-sha-256
(This affects how new passwords arestoredwhen you do ALTER ROLE ... PASSWORD ....) PostgreSQL+1
Step B — Rotate passwords of roles that use login (this is what "moves" to SCRAM)
Example:
ALTER ROLE odoo PASSWORD 'a_long_and_unique_key';
Step C — Change pg_hba.conf to scram-sha-256
In the corresponding entry:
host all all 10.0.0.0/8 scram-sha-256
💡 Helpful tip: PostgreSQL makes the transition easier because if you set md5 in pg_hba.conf but the user already has a SCRAM password, PostgreSQL can automatically choose SCRAM for that user. PostgreSQL+1
Step D — Adjust PgBouncer
In pgbouncer.ini:
[pgbouncer] auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt scram_iterations = 4096
scram_iterations exists to increase the cost of brute force (safer, but authenticates slower). PgBouncer
7) Real security: SCRAM does not replace TLS
SCRAM improvesauthentication(and the storage of the secret), but does not encrypt queries or results.
If your DB traffic crosses networks you do not control,use TLSas well. (SCRAM + TLS is the serious combo; the RFCs themselves suggest it as desirable.) RFC Editor
8) Quick checklist (to paste at the end of the post)
PostgreSQL stores passwords as SCRAM (password_encryption = scram-sha-256) PostgreSQL+1
pg_hba.conf uses scram-sha-256
I confirmed in pg_authid who is already on SCRAM PostgreSQL
PgBouncer has auth_type = scram-sha-256 and the correct auth_file PgBouncer+1
I understood the limitation: SCRAM secret ≠ reusable credential PgBouncer
(Optional) TLS enabled if there is an untrusted network RFC Editor
Plan to phase out MD5 (already deprecated) PostgreSQL+1