PgBouncer Auth File Out of Sync

Scenario

After a password rotation, PgBouncer starts rejecting connections with ERROR: password authentication failed. The DBA updated pg_hba.conf and rotated the PostgreSQL password but forgot to update PgBouncer’s userlist.txt (static authentication file). Alternatively, PgBouncer is configured with auth_type = hba but its HBA file doesn’t include the updated credentials path.

How to Identify

Conditions:

• PgBouncer logs show auth failed even though psql direct connection works
• userlist.txt (PgBouncer’s auth file) has old MD5 hashes for the user
• PgBouncer’s auth_file path in pgbouncer.ini doesn’t point to the correct file
• auth_type = scram-sha-256 in PgBouncer but userlist.txt has MD5 hashes
• PgBouncer version doesn’t support SCRAM (versions before 1.14)

Analysis Steps

-- On PostgreSQL: verify the password is correct (psql connects successfully)
-- psql -h localhost -U app_user -d mydb -c "SELECT 1;" ← should work

-- On PostgreSQL: check what hash format the password is stored in
SELECT rolname, left(rolpassword, 20) AS hash_prefix
FROM pg_authid
WHERE rolname = 'app_user';
-- 'SCRAM-SHA-256$...' = SCRAM format (PgBouncer 1.14+ required)
-- 'md5...'            = MD5 format (older PgBouncer OK)

-- Check PgBouncer's userlist.txt format (OS-level):
-- cat /etc/pgbouncer/userlist.txt
-- Format: "username" "password_hash_or_plain"
-- Must match PostgreSQL's current password hash format

-- Check PgBouncer config for auth_type:
-- grep auth_type /etc/pgbouncer/pgbouncer.ini
-- auth_type = hba          = PgBouncer uses its own HBA file
-- auth_type = md5          = requires MD5 hashes in userlist.txt
-- auth_type = scram-sha-256 = requires SCRAM hashes (PgBouncer 1.14+)
-- auth_type = plain        = plaintext passwords (insecure)

-- Check PgBouncer version:
-- pgbouncer --version
-- versions < 1.14 do NOT support SCRAM

Pitfalls

• PgBouncer’s userlist.txt must be manually updated every time a PostgreSQL password changes. This is often forgotten during credential rotations.
• PgBouncer with auth_type = md5 stores MD5 hashes in userlist.txt. If PostgreSQL is migrated to scram-sha-256, PgBouncer needs upgrade to 1.14+ AND userlist.txt must be updated with SCRAM hashes.
• PgBouncer’s auth_query feature lets PgBouncer query PostgreSQL for credentials dynamically — this eliminates the userlist.txt sync problem but requires a special lookup function.
• PgBouncer in transaction pool mode cannot support prepared statements natively — applications using extended query protocol or LISTEN/NOTIFY must use session pool mode.
• Reloading PgBouncer (pgbouncer -R or kill -HUP) re-reads userlist.txt without dropping existing connections.

Resolution Approach

Update userlist.txt with the new password hash from pg_authid. Reload PgBouncer to apply the change. For long-term, configure auth_query so PgBouncer fetches credentials from PostgreSQL directly — eliminating manual sync.