In PgBouncer, how do auth_user and auth_query let clients log in without listing every user in auth_file, and how do you set them up safely?
answer
- PgBouncer checks passwords itself
- look up the hash at login
- not pg_authid as superuser
- SECURITY DEFINER, pinned search_path
basics
~10 sFor users missing from auth_file, PgBouncer connects as auth_user and runs auth_query to fetch the user's password hash from PostgreSQL. Safely: a non-superuser calling a SECURITY DEFINER lookup function with a pinned search_path.
solid answer
~40 sPgBouncer authenticates clients itself, so it needs each user's password or SCRAM secret. `auth_file` is a static list; with `auth_user` set, any user not in it is looked up by running `auth_query` as `auth_user` in the target database (or the one `auth_dbname` names). The query returns the user name and the stored hash; no row or a NULL user means no such user, and a NULL password means no password is accepted. The default query reads `pg_authid`, which only superusers can read, so the safe pattern is a non-superuser `auth_user` that may only execute a `SECURITY DEFINER` function with `SET search_path = pg_catalog, pg_temp`. Run 1.25.1 or later: CVE-2025-12819 let a client's startup `search_path` hijack an unqualified `auth_query`.
code
ini · 8 lines[databases]
ticketing = host=10.0.0.5 dbname=ticketing auth_dbname=ticketing
[pgbouncer]
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)go deeper
Know that PgBouncer checks client passwords itself and needs either a user list file or a lookup query against PostgreSQL.
Explain the login flow: auth_user connects, auth_query returns user name and secret, and what no row, a NULL user or a NULL password mean.
Set it up with least privilege: a SECURITY DEFINER function, a pinned search_path, revoked public access, auth_dbname, and a release with the CVE-2025-12819 fix.
Decide between a managed auth_file and live lookups, weighing credential sprawl against a login-time dependency on the database.
## Why PgBouncer needs password hashes PgBouncer is not a transparent TCP proxy: it **authenticates clients itself**, according to `auth_type`, before it lends them a server connection. To check a password, and to log in to PostgreSQL on the client's behalf, it needs the user's stored secret: a SCRAM-SHA-256 secret or an MD5 hash. `auth_type` defaults to `md5`, which still uses SCRAM automatically when the stored secret is a SCRAM one; new setups usually set `scram-sha-256` explicitly. There are two places PgBouncer can get the secret: - **`auth_file`**: a text file of `"user" "secret"` lines that must be kept in step with PostgreSQL by hand or by a script. - **`auth_user` + `auth_query`**: a lookup in PostgreSQL itself at login time. ## How the lookup works 1. A client connects as `ticket_app`, which is not in `auth_file`. 2. PgBouncer opens a server connection as `auth_user`, whose own password comes from `auth_file`, to the database the client asked for, or to `auth_dbname` if that is set. 3. It runs `auth_query` with the user name as `$1`. The query must return **two columns**: the user name and the stored secret. 4. **No row**, or a row with a **NULL user name**, means the user does not exist; PgBouncer 1.26 treats the NULL case like no row instead of as an error. The NULL case matters because a lookup through a record-returning function, like the one below, returns one row of NULLs for an unknown user rather than no row at all. A **NULL password** means the user exists but no password will be accepted. 5. PgBouncer verifies the client against the secret and uses it to log in to PostgreSQL as that user. With SCRAM this pass-through works because the secret came from the server itself, so salt and iteration count match. `auth_user` and `auth_query` can also be set per database in `[databases]`. ## Doing it safely The default `auth_query` is `SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END FROM pg_authid WHERE rolname=$1 AND rolcanlogin`. It reads `pg_authid`, which only a superuser can read, so using it means giving PgBouncer a superuser login. The documented pattern avoids that: ```sql CREATE SCHEMA pgbouncer AUTHORIZATION postgres; CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text) RETURNS record AS $$ BEGIN SELECT rolname, CASE WHEN rolvaliduntil < now() THEN NULL ELSE rolpassword END FROM pg_authid WHERE rolname = i_username AND rolcanlogin INTO uname, phash; RETURN; END; $$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp; REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public; GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer; ``` Then in `pgbouncer.ini`: `auth_user = pgbouncer` and `auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)`. The points that matter: - **`SECURITY DEFINER`** runs the function with its owner's rights, so the `pgbouncer` role itself never reads `pg_authid`. - **`SET search_path = pg_catalog, pg_temp`** stops a caller's `search_path` from redirecting the `<` operator or `now()` to an attacker's object. - **`REVOKE ... FROM public`** keeps other roles from fetching password hashes. - The query runs **inside the target database**, so the function must exist in every database clients connect to, unless `auth_dbname` points all lookups at one database. - A role that cannot log in or whose `rolvaliduntil` has passed returns a NULL secret, so it cannot log in. - `pg_roles` is no shortcut: its `rolpassword` column always reads `********`. ## The CVE to know **CVE-2025-12819**, fixed in PgBouncer **1.25.1**, let an unauthenticated client run arbitrary SQL during login when three things were true: `search_path` was tracked from the startup packet, `auth_user` was set, and `auth_query` used unqualified names. The default query qualifies none of its objects, and the `<` operator is the classic example. Since 1.26 tracks `search_path` by default for PostgreSQL 18 servers, the first condition is now common. The defences are to run 1.25.1 or later and to use the qualified `SECURITY DEFINER` function with a pinned `search_path`. ## Operating notes - `RELOAD` (or `SIGHUP`) re-reads `auth_file`, which still holds the `auth_user` secret, so rotating that one password means editing the file and reloading. - In the ticketing fleet, a new role created in PostgreSQL for a batch job can log in through PgBouncer with no user-list file to edit or redeploy.
- Why does a PgBouncer auth_query function have to be installed in every database?PgBouncer runs `auth_query` inside the database the client asked for, so the function must exist there. Setting `auth_dbname` sends every lookup to one database instead, so a single copy of the function is enough.
- How can PgBouncer log in to PostgreSQL with SCRAM when it never sees the client's plain-text password?With `auth_query` it holds the same SCRAM secret that PostgreSQL stores, same salt and iterations, so it can complete SCRAM with the server using keys from the client's exchange. A secret typed into `auth_file` works only if it is identical to the server's, not merely made from the same password.
- auth_user must be a superuser because auth_query reads pg_authid.
- auth_query runs once in the postgres database for every database.
- PgBouncer passes the client's login straight through to pg_hba.conf.
- pg_roles is a safe source for auth_query because it exposes rolpassword.
- A SECURITY DEFINER lookup function is safe without its own search_path.
Assumes PgBouncer 1.26: CVE-2025-12819 is fixed from 1.25.1, and 1.26 tracks search_path by default and treats a NULL user name from auth_query as no such user.