AUTH_USER never applies: the per-database auth_user added in #56 always overrides it

[jclaveau]

AUTH_USER sets auth_user in the [pgbouncer] section, which pgbouncer treats as the default for databases that do not name one of their own. But generate_config_db_entry always writes a per-database auth_user:

port=${DB_PORT:-5432} auth_user=${DB_USER:-postgres}

and the per-database value wins. So AUTH_USER never applies to a generated entry.

Reproducing

Per-entry auth_user pointing at a role that does not exist, global AUTH_USER pointing at one that does and whose password is in userlist.txt:

$ docker run -d --network app \
    -e DB_HOST=pg -e DB_NAME=appdb \
    -e DB_USER=ghostuser -e DB_PASSWORD=ghostpw \
    -e AUTH_USER=postgres \
    edoburu/pgbouncer

[databases]
appdb = host=pg port=5432 auth_user=ghostuser
[pgbouncer]
auth_user = postgres

Connecting as a user absent from userlist.txt, so that the auth_query path runs:

$ psql "postgres://other@pooler:5432/appdb"
psql: error: FATAL:  password authentication failed for user "ghostuser"

The lookup was attempted as ghostuser, never as the configured AUTH_USER.

The second symptom

The same line explains a confusing error in the default configuration. Started with neither DB_USER nor AUTH_USER, an entry still advertises auth_user=postgres, so an unknown user gets:

C-0x…: appdb/(nouser)@… closing because: password authentication failed for user "postgres"
S-0x…: appdb/postgres@… closing because: password authentication failed for user "postgres"

naming a role the operator never configured. The real situation is that the connecting user is absent from userlist.txt and no usable auth_user exists. Without an auth_user on the entry, pgbouncer would have said so plainly.

How the two came to collide

AUTH_USER arrived in 99b51ec (2019) and is the README's documented route to auth_query. The per-database auth_user arrived much later, in 6591cb9 (2023, #56), as a one-word change:

-port=${DB_PORT:-5432} user=${DB_USER:-postgres}
+port=${DB_PORT:-5432} auth_user=${DB_USER:-postgres}

That fixed a real problem — the forced user= collapsed every client into a single identity on the server — and putting DB_USER into auth_user was the point of it. AUTH_USER is not mentioned anywhere in #56, so the shadowing looks like something nobody had reason to notice: for the four years before, the two variables wrote different settings and could not conflict.

Suggested fix

Reconcile the two rather than undo either:

auth_user =  DB_USER      when set
             AUTH_USER    otherwise, when set
             omitted      otherwise

• use auth_user instead of user option in the default db config #56 keeps working: DB_USER still drives auth_user, and clients still reach the server under their own identity
• AUTH_USER becomes usable for the first time since it was added
• with neither set, the entry stops advertising a postgres credential nobody configured, and an unknown user gets pgbouncer's own clear message instead of a failure naming postgres

One case to weigh before changing the last line: a mounted AUTH_FILE may hold a postgres credential with no DB_* and no AUTH_USER set, and that setup would lose auth_query if the entry stops defaulting. Keeping the postgres fallback while still honouring AUTH_USER would cover it, at the cost of the confusing error staying.

Related

• pooler error: password authentication failed #58 reached the same password authentication failed message from a different cause: their auth_user existed but could not read pg_shadow on RDS. Whatever the reason auth_query cannot run, the error names the auth_user rather than the user who was refused, and the report starts by looking at the wrong credential.
• FATAL: server login failed: wrong password type #93 and userlist.txt md5 "wrong type" issue #115 are the neighbouring case, where the stored verifier is of a type the configured auth_type cannot check.

Reproduced on edoburu/pgbouncer:latest.

Assisted-by: Claude:claude-opus-5[1m]

[jclaveau]

Some measurements on what the postgres fallback costs, since "the error names a role nobody configured" turns out to be the least of it.

Same postgres, same userlist.txt (holding neither postgres nor the connecting user), one client connecting as other. Only the entry's auth_user differs.

With auth_user=postgres:

client: FATAL:  password authentication failed for user "postgres"
log:    C-…: appdb/(nouser)@… closing because: password authentication failed for user "postgres"
        S-…: appdb/postgres@172.20.0.2:5432 closing because: password authentication failed for user "postgres"

With no auth_user at all:

client: FATAL:  password authentication failed
log:    C-…: (nodb)/other@… login attempt: db=appdb user=other tls=no replication=no
        C-…: (nodb)/other@… closing because: password authentication failed

Two things worth noting.

Neither message is strictly better; they drop opposite halves. Naming postgres does say which mechanism failed — read with the S-… line it means "the auth_query lookup was attempted and could not log in", which implies the client was absent from userlist.txt. But the client is logged as (nouser), so you never learn who tried. Without auth_user, the log names user=other and adds an explicit login attempt line, at the cost of not saying why.

The S-… line is the part that is not cosmetic. Every rejected client login opens a connection to PostgreSQL and fails authentication there. With no auth_user the client is turned away without the server being touched at all. On a pooler reachable from outside, that turns unauthenticated connection attempts into server-side login attempts — connection churn on the database, entries in its log, and an amplification an idle scanner gets for free.

So the case for dropping the fallback is not really about the error text. It is that the default makes pgbouncer attempt a lookup that cannot succeed, once per failed login, in a configuration where nothing has provided the credential it would need.

The mounted-AUTH_FILE case still argues for keeping it — a postgres credential can exist in a file the entrypoint never wrote, with no DB_* and no AUTH_USER set — so I have kept the fallback in the PR and left this as a separate question.

Assisted-by: Claude:claude-opus-5[1m]