PgBouncer SCRAM / auth_type mismatch: md5 vs scram-sha-256 login failures

Clients cannot log in through PgBouncer. Authentication fails with errors that look like wrong passwords, but the passwords are correct and PostgreSQL accepts them on direct connection.

The root cause is a mismatch between PgBouncer’s auth_type and PostgreSQL’s password_encryption or pg_hba.conf method. PostgreSQL 12+ defaults to scram-sha-256 for password_encryption. Deployments that upgrade PostgreSQL without updating PgBouncer’s auth configuration break silently because the authentication mechanisms are incompatible, not the credentials.

This article covers how to identify the specific mismatch, align auth_type with PostgreSQL’s password method, store SCRAM verifiers correctly, and migrate from md5 to scram without breaking existing connections.

What this means

PgBouncer authenticates in two phases. First, it authenticates the incoming client connection against its own auth_file or via auth_query, governed by auth_type. Second, it authenticates itself to PostgreSQL, governed by the credentials it holds and what PostgreSQL’s pg_hba.conf accepts. A mechanism mismatch in either phase causes failures.

flowchart TD
    A[Client connects] --> B["Phase 1: client auth via auth_type + auth_file"]
    B --> C{Client auth OK?}
    C -->|No| D["Log: SASL or password auth failed"]
    C -->|Yes| E["Phase 2: server auth via pg_hba.conf"]
    E --> F{Server auth OK?}
    F -->|No| G["Log: server login failed
sv_login elevated, pool drains"]
    F -->|Yes| H[Connection established]

SCRAM-SHA-256 pass-through (PgBouncer 1.14+) adds a constraint. PgBouncer can relay the client’s SCRAM exchange to PostgreSQL without storing the plaintext password, but only if the SCRAM verifier in auth_file (or returned by auth_query) is byte-identical to what PostgreSQL stores in pg_authid. The same password encrypted with a different salt or iteration count produces a different verifier. Pass-through fails because the keys do not match.

The most confusing variant: auth_type=md5 with md5 hashes in auth_file works for client-to-PgBouncer authentication. But when PgBouncer tries to authenticate to a PostgreSQL that requires scram-sha-256 and stores SCRAM verifiers, the server connection fails. Clients connect to PgBouncer but queries error out. Logs show “server login failed” while clients see intermittent connectivity or timeouts.

Common causes

Quick checks

# Check PgBouncer's current auth_type
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW CONFIG;" | grep auth_type

# Check PostgreSQL's password encryption method
psql -h <postgres_host> -U postgres -Atc "SHOW password_encryption;"

# Check what pg_hba.conf requires for your connection
psql -h <postgres_host> -U postgres -Atc "SELECT type, database, user, auth_method FROM pg_hba_file_rules WHERE auth_method IN ('scram-sha-256', 'md5') LIMIT 10;"

# Inspect auth_file contents for SCRAM vs md5 format
grep -c 'SCRAM-SHA-256' /etc/pgbouncer/userlist.txt
grep -c 'md5' /etc/pgbouncer/userlist.txt

# Check how PostgreSQL stores a specific user's password
psql -h <postgres_host> -U postgres -Atc "SELECT rolname, left(rolpassword, 20) FROM pg_authid WHERE rolname = 'app_user';"

# Look for authentication failures in PgBouncer logs
grep -iE "authentication failed|SASL|login failed|server login" /var/log/pgbouncer/pgbouncer.log | tail -20

# Check sv_login for stuck backend authentication
# Column position varies by PgBouncer version; $(NF-3) is portable across layouts
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW POOLS;" | awk -F'|' '{print $1, $2, "sv_login="$(NF-3)}'

# Check PgBouncer version for SCRAM support (1.11+ for auth, 1.14+ for pass-through)
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -Atc "SHOW VERSION;"

How to diagnose it

Step 1: Identify which phase is failing. PgBouncer log messages distinguish client-side auth failures from server-side failures.

Client-side failures (client cannot authenticate to PgBouncer):

• “SASL authentication failed” - client did not complete SCRAM correctly, or PgBouncer’s auth_file has the wrong verifier
• “password authentication failed” - md5 mismatch between client and auth_file
• “cannot do SCRAM authentication: password is SCRAM secret but client authentication did not provide SCRAM keys” - PgBouncer has a SCRAM verifier but the client attempted md5

Server-side failures (PgBouncer authenticated the client but cannot authenticate to PostgreSQL):

• “closing because: server login failed” - PostgreSQL rejected PgBouncer’s credentials
• “server login timed out” - PostgreSQL did not respond to auth in time

Step 2: Compare auth mechanisms across all three layers. The chain is: client auth method, then PgBouncer auth_type, then auth_file contents, then PgBouncer-to-PostgreSQL auth, then pg_hba.conf, then PostgreSQL stored password format. All must be compatible.

Step 3: Verify the SCRAM verifier if using pass-through. If auth_type is scram-sha-256 or auth_type is md5 with SCRAM secrets in auth_file, the verifier must match PostgreSQL’s stored verifier byte-for-byte. Extract the exact verifier from PostgreSQL:

-- On PostgreSQL, get the exact SCRAM verifier for a user
SELECT rolpassword FROM pg_authid WHERE rolname = 'app_user';

Compare this to the entry in auth_file. They must be identical, including iteration count and salt.

Step 4: Check for version-specific behavior. PgBouncer 1.11 added SCRAM authentication support. PgBouncer 1.14 added SCRAM pass-through. If your PgBouncer is older than 1.14, it cannot relay SCRAM to PostgreSQL using stored verifiers. Check SHOW VERSION.

Metrics and signals to monitor

Fixes

Align auth_type with PostgreSQL’s password method

If PostgreSQL uses scram-sha-256 (the default since PostgreSQL 12), set PgBouncer’s auth_type to scram-sha-256 and ensure auth_file contains SCRAM verifiers:

# pgbouncer.ini
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

auth_type is reloadable. Apply the change with RELOAD and verify with SHOW CONFIG.

Store SCRAM verifiers correctly in auth_file

The auth_file must contain the exact SCRAM verifier from PostgreSQL, in double-quoted format:

"app_user" "SCRAM-SHA-256$4096:somebase64salt$storedkey:serverkey"

To populate auth_file from PostgreSQL, query the stored verifier directly:

# Extract SCRAM verifiers from PostgreSQL and format for auth_file
psql -h <postgres_host> -U postgres -Atc \
  "SELECT '\"' || rolname || '\" \"' || rolpassword || '\"' FROM pg_authid WHERE rolcanlogin;" \
  > /etc/pgbouncer/userlist.txt

Then RELOAD PgBouncer.

If you are using auth_type=md5 but auth_file contains SCRAM verifiers, PgBouncer 1.11+ automatically uses SCRAM for that user. This backward-compatibility path works for client-to-PgBouncer auth. For server auth, the SCRAM pass-through path (1.14+) handles relay to PostgreSQL if the verifier matches.

Use auth_query instead of static auth_file

With auth_query, PgBouncer fetches credentials from PostgreSQL at login time. This eliminates stale auth_file problems entirely:

# pgbouncer.ini
auth_type = scram-sha-256
auth_query = SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END FROM pg_authid WHERE rolname=$1 AND rolcanlogin

The auth_user must connect with a plaintext password, not a SCRAM secret. PgBouncer cannot derive client-side SCRAM keys from a server-side verifier, so a SCRAM hash for auth_user in auth_file will not work for the bootstrap connection. Store the auth_user entry with a plaintext password.

Note: reading rolpassword from pg_authid requires superuser or appropriate grants. Create a dedicated role with SELECT on pg_authid or use a custom SECURITY DEFINER function.

Handle SCRAM pass-through requirements

For SCRAM pass-through to work, three conditions must hold:

• No user= override in the [databases] section for the target database. If user= is specified, PgBouncer uses fixed credentials instead of relaying the client’s SCRAM exchange.
• Verifier must match. The SCRAM verifier in auth_file (or from auth_query) must be byte-identical to PostgreSQL’s stored verifier. Different salt or iteration count means the verifier is different.
• Client must use SCRAM. If the client authenticates to PgBouncer using md5, PgBouncer has no SCRAM keys to relay. The error “cannot do SCRAM authentication: password is SCRAM secret but client authentication did not provide SCRAM keys” indicates exactly this situation.

Migrate from md5 to scram-sha-256

To migrate cleanly:

1. Ensure PgBouncer is version 1.14 or later (required for SCRAM pass-through).
2. Set PostgreSQL’s password_encryption to scram-sha-256.
3. Re-encrypt all user passwords in PostgreSQL. This requires knowing or resetting each password:

-- WARNING: this changes the stored password hash.
-- All connections using this credential are affected.
-- Ensure the application has the correct password configured.
ALTER ROLE app_user PASSWORD 'the_actual_password';

PostgreSQL stores the new password using the current password_encryption setting.

4. Export the new SCRAM verifiers to auth_file (see above).
5. Change PgBouncer auth_type to scram-sha-256.
6. Apply the change and verify with a test connection through PgBouncer.

During migration, auth_type=md5 with SCRAM secrets in auth_file provides a transitional state where PgBouncer handles both md5 and SCRAM clients. Once all clients are confirmed using SCRAM, switch auth_type to scram-sha-256.

Prevention

• Track PostgreSQL version defaults. PostgreSQL 12 changed the default password_encryption to scram-sha-256. Upgrades without updating PgBouncer auth configuration are the most common cause of this failure.
• Monitor auth failure rates from logs. PgBouncer exposes zero error counters in SHOW commands. Parse logs for “SASL authentication failed”, “password authentication failed”, and “server login failed”.
• Automate auth_file synchronization. If using a static auth_file, sync it from pg_authid after any password change. Stale auth_file is the second most common cause after version upgrades.
• Use auth_query where possible. auth_query eliminates the stale auth_file problem by fetching credentials live from PostgreSQL. Ensure auth_user has a plaintext password, not a SCRAM secret.
• Pin and document PgBouncer version. SCRAM behavior changed across 1.11, 1.14, 1.16, 1.17, and 1.25. Know your version’s capabilities before troubleshooting.
• Test after credential rotation. Any password change, PostgreSQL upgrade, or PgBouncer config change should be followed by a functional login test through PgBouncer, not just a direct PostgreSQL connection.

How Netdata helps

• Log-based auth failure detection. Since PgBouncer exposes zero error counters in SHOW commands, Netdata’s log parser can surface spikes in “SASL authentication failed” and “server login failed” messages as anomalies, turning a log-only signal into an alertable metric.
• sv_login correlation. Netdata collects sv_login per pool at per-second resolution. A sustained sv_login elevation with growing cl_waiting distinguishes backend auth failure from pool exhaustion.
• PostgreSQL password_encryption visibility. Netdata’s PostgreSQL collector surfaces the password_encryption setting, making it visible when a PostgreSQL upgrade changes the default.
• Change correlation. When auth failures begin, Netdata’s anomaly detection correlates them with nearby events such as PostgreSQL restarts, PgBouncer RELOAD operations, or configuration deployments, narrowing the cause window.
• Connection churn detection. A spike in new connection attempts following a credential rotation or upgrade, visible in cl_active and cl_waiting patterns, often signals the onset of an auth mismatch problem.

Related guides

• PgBouncer advisory locks in transaction mode: orphaned locks and mysterious contention
• PgBouncer avg_query_time high: reading backend slowdown through the pooler
• PgBouncer avg_wait_time high: the latency the pool itself is injecting
• PgBouncer backend unreachable: PostgreSQL down and the pool draining
• PgBouncer capacity planning: runway for pools, clients, and PostgreSQL slots
• PgBouncer client connection leak: idle clients that never disconnect
• PgBouncer database paused or disabled: maintenance state that looks like an outage
• PgBouncer event loop stall: the single thread that freezes every pool at once
• PgBouncer high CPU: single-core saturation, TLS, and connection churn
• How PgBouncer actually works in production: a mental model for operators
• PgBouncer idle in transaction: the silent pool killer in transaction mode
• PgBouncer LISTEN/NOTIFY not working: why pub/sub needs session pooling