PgBouncer auth_query failures: the authentication dependency loop on PostgreSQL

Clients cannot connect. The PgBouncer log fills with “password authentication failed” and “S: login failed” messages. SHOW POOLS shows sv_login connections stuck in the login state, cl_waiting climbing. If you use auth_query to authenticate clients against PostgreSQL, the root cause may be neither a wrong password nor a down database: it may be the circular dependency that auth_query creates between the pooler and the backend.

auth_query tells PgBouncer to authenticate each connecting client by running a SQL query against PostgreSQL to look up that user’s password hash. You still need auth_file (userlist.txt) for auth_user credentials, but you do not need to sync every application user’s password into it. The cost: every new client connection requires a working PostgreSQL connection before the client can be pooled. When PostgreSQL is healthy, this is invisible. When PostgreSQL is under load, or when PgBouncer restarts and hundreds of clients re-authenticate simultaneously, auth_query becomes the bottleneck that amplifies the outage.

What this means

The authentication path for each client is:

1. Client connects to PgBouncer.
2. PgBouncer opens a connection to PostgreSQL as auth_user.
3. PgBouncer runs the configured auth_query SQL to retrieve the connecting user’s password hash.
4. PgBouncer validates the client’s supplied password against the returned hash.
5. If valid, PgBouncer authenticates to PostgreSQL as the target user and assigns a pooled server connection.

Step 2 is where the dependency loop lives. The auth_user connection competes for the same PostgreSQL connection slots that the client is ultimately trying to use. Under normal load, auth_user connections are short-lived and cheap. Under stress, they become another consumer of an already-saturated backend.

The feedback loop: PostgreSQL is overloaded, so auth queries take longer. Clients wait longer to authenticate. Applications time out and retry. Each retry triggers another auth query. More auth queries consume more PostgreSQL resources. The cycle accelerates.

A failed auth_query means the query returned no row for the user (user does not exist, cannot log in, or auth_user lacks permission to read pg_authid), or the returned password hash did not match what the client supplied. But the failure mode is worse than a simple password mismatch because of the blocking behavior: if auth_user itself cannot connect to PostgreSQL (wrong credentials, network issue, PostgreSQL at max_connections), PgBouncer retries the auth_user connection every server_login_retry seconds (default: 15s) until query_wait_timeout expires (default: 120s). The client is blocked the entire time.

PgBouncer treats a NULL returned password as an impossible MD5 value, so the client sees a password failure; if the query returns no row at all, the client receives the more direct “no such user” error.

flowchart TD
    A[Client connects to PgBouncer] --> B[auth_query connects to PostgreSQL]
    B --> C{PostgreSQL responsive?}
    C -->|Yes| D[Client authenticated and pooled]
    C -->|No: overloaded or full| E[auth_query slow or fails]
    E --> F[Client blocked up to 120s]
    F --> G[Application times out and retries]
    G --> H[New connections trigger more auth queries]
    H --> B
    E --> I[sv_login stuck, pool drains]
    I --> H

Common causes

Quick checks

# Check auth_query configuration
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW CONFIG;" | grep -E "auth_type|auth_user|auth_query|auth_dbname"

# Check for connections stuck in backend login state
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW POOLS;"

# Check for clients stuck in PgBouncer's own auth phase
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer -c "SHOW LISTS;"

# Grep recent auth failures in the log
grep -E "auth failed|login failed|S: login" /var/log/pgbouncer/pgbouncer.log | tail -30

# Test auth_user can connect to PostgreSQL and run the auth query
psql -h <postgres_host> -p 5432 -U <auth_user> -d postgres -c \
  "SELECT rolname, rolpassword FROM pg_authid WHERE rolname='test_user' AND rolcanlogin;"

# Check PostgreSQL connection saturation
psql -h <postgres_host> -p 5432 -U postgres -c \
  "SELECT count(*) AS active, (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max_conn FROM pg_stat_activity;"

How to diagnose it

1. Check whether auth_user can connect to PostgreSQL at all. If auth_user credentials are wrong, every client authentication fails and blocks for up to query_wait_timeout (120s). Test the auth_user connection directly from the PgBouncer host. If this fails, fix auth_user credentials before anything else.

2. Examine sv_login in SHOW POOLS. A sustained non-zero sv_login means PgBouncer is churning through backend connection attempts. If sv_login is high but sv_active is low, connections are failing during login, not being held by slow queries.

3. Check the auth_user password format in auth_file. When auth_type=scram-sha-256, the auth_user’s password in userlist.txt must be in plain text or MD5 format, not a SCRAM-SHA-256 hash. PgBouncer uses this password to establish its own connection to PostgreSQL as auth_user. A SCRAM secret cannot be used to initiate a new connection. This is the most common auth_query setup mistake.

4. Verify auth_query returns the expected hash format. The query must return a password hash that matches auth_type. If auth_type is scram-sha-256, the query must return the SCRAM-SHA-256 hash from pg_authid.rolpassword. If the query returns an MD5 hash instead, authentication fails for every user.

5. Check pg_hba.conf on PostgreSQL. PgBouncer logs in as auth_user to run the auth query, then logs in again as the target user to establish the pooled connection. Both logins must be permitted by pg_hba.conf. A common mistake is configuring pg_hba.conf for auth_user but forgetting the target application users.

6. Check PostgreSQL connection saturation. If PostgreSQL is at max_connections, auth_user cannot connect to run the auth query, and no new clients can be authenticated. This is the dependency loop in its purest form: the pooler needs a PostgreSQL connection to authenticate clients, but PostgreSQL has no connections to give.

7. Check whether the failure started after a PgBouncer upgrade. PgBouncer 1.24.1 changed the default auth_query to include a VALID UNTIL check. The default query now wraps rolpassword in CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END, returning NULL for expired passwords. Users whose rolvaliduntil has passed will fail authentication on the new default query even though they succeeded on the old one.

The change shipped in PgBouncer 1.24.1 as the fix for CVE-2025-2291.

8. Check for search_path injection if running an older version. PgBouncer 1.25.1 fixed CVE-2025-12819: before that release, an untrusted search_path in the client StartupMessage could allow SQL injection during the auth_query handler. The vulnerability applied before 1.25.1 when track_extra_parameters included search_path, auth_user was set to a non-empty value, and auth_query did not use fully-qualified object names (the default query met the last condition). Upgrade or use fully-qualified object names in auth_query.

Metrics and signals to monitor

PgBouncer has zero error counters in any SHOW command. Authentication failure counts, connection rejection counts, and timeout events appear only in log files. Any monitoring strategy that relies solely on SHOW commands is blind to auth_query failures.

Fixes

Fix auth_user credentials and password format

If auth_user cannot connect to PostgreSQL, nothing else works. Verify the auth_user password in auth_file (userlist.txt) is correct and in the right format. For auth_type=scram-sha-256, the password must be plain text or MD5 in the file, not a SCRAM-SHA-256 hash. After updating auth_file, run RELOAD on PgBouncer.

Break the dependency loop with auth_dbname

By default, auth_query runs against the same database the client is connecting to. If that database is overloaded, auth_query is slow. If you use a custom SECURITY DEFINER function for auth_query, it must be installed in every target database.

auth_dbname is a PgBouncer setting added in 1.21.0; it can be set globally or overridden in a [databases] connection string. It names the database entry to use for authentication, decoupling authentication from the workload of any individual application database and eliminating the need to install the auth function in every database.

Reserve a PostgreSQL connection slot for auth_user

PostgreSQL reserves connections for superusers via superuser_reserved_connections. If auth_user has SUPERUSER privilege, it can connect using these reserved slots even when PostgreSQL is at max_connections for normal users. This ensures auth_user can always run auth queries during a connection storm.

The tradeoff is security: auth_user with SUPERUSER carries elevated privileges. PostgreSQL 16 introduced reserved_connections and the pg_use_reserved_connections predefined role, which offer slot reservation without SUPERUSER.

Stagger restarts to avoid thundering herd

When PgBouncer restarts, all server connections are lost and every client triggers a new auth query simultaneously. With auth_query, this means hundreds of concurrent auth_user connections hitting PostgreSQL at once, each competing for the connections they are trying to establish. Mitigations:

• Set min_pool_size above zero to pre-warm connections at startup, reducing the burst.
• Use rolling restarts across multiple PgBouncer instances if running behind a load balancer.
• Ensure PostgreSQL max_connections can absorb the login storm from all PgBouncer instances simultaneously.

Fix pg_hba.conf for both auth_user and target users

PgBouncer connects as auth_user to run the query, then connects as the target user for the actual session. Both paths must be allowed in pg_hba.conf. The typical mistake is adding auth_user but missing application users.

Prevention

• Reserve a dedicated PostgreSQL slot for auth_user. If auth_user can always connect, authentication survives load spikes that would otherwise cascade into full outages.
• Use auth_dbname to isolate auth queries. Route auth queries to a database that is not under application load.
• Monitor sv_login and login_clients. These are early warning signals. Sustained non-zero sv_login with low sv_active means connections are failing to establish, not being held by slow queries.
• Parse the PgBouncer log for auth failures. No SHOW command exposes failure counts. Set up log-based alerting for “auth failed”, “S: login failed”, and “query_wait_timeout” patterns.
• Verify the auth_user password format after any auth_type change. Switching auth_type to scram-sha-256 requires the auth_user password to be plain text or MD5 in auth_file. Easy to miss during security hardening.
• Test the restart path. PgBouncer restart with auth_query creates a synchronized auth storm. Measure how long pool recovery takes under realistic load. If recovery exceeds 60 seconds, add min_pool_size or stagger client reconnection.

How Netdata helps

The key correlations for catching auth_query failures before they cascade:

• sv_login per second: catches auth_user connection failures early, before cl_waiting grows and clients time out.
• cl_waiting and maxwait correlation with sv_login: distinguishes “auth path is blocked” from “pool is exhausted by slow queries.”
• PostgreSQL connection utilization cross-correlated with sv_login: reveals when backend saturation is the root cause, not a PgBouncer config issue.
• avg_query_time and avg_wait_time side by side: elevated avg_query_time with growing avg_wait_time indicates PostgreSQL slowdown propagating through the auth path into client-visible latency.
• Log anomaly detection on auth failure patterns: provides the error signal that SHOW commands cannot expose.

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