pgBouncer is excellent as a connection pooler, but its default configuration concedes a hostility: you have to write your user list into userlist.txt by hand. Every time you add a new DB user, you update the file, compute the hash, reload pgBouncer. An operational headache.

auth_query solves this: pgBouncer queries PostgreSQL itself for authentication.

Setting up auth_user

On the PostgreSQL side we create a low-privilege user and a pg_shadow-like view:

CREATE ROLE pgbouncer LOGIN PASSWORD 's3cr€t_p@ssw0rd';

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text,
                                                 out uname text,
                                                 out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

SECURITY DEFINER matters — the function uses the privileges of the user who defined it to access pg_shadow. Otherwise the pgbouncer role can’t read pg_shadow.

pgBouncer config

pgbouncer.ini:

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432

auth_type = scram-sha-256
auth_user = pgbouncer
auth_query = SELECT uname, phash FROM pgbouncer.user_lookup($1)

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

No more userlist.txt. Create a new role in PostgreSQL and authentication works without requiring a pgBouncer restart.

md5 vs scram-sha-256

PostgreSQL 14+ uses scram-sha-256 by default. pgBouncer 1.18+ supports it fully. If you’re stuck falling back to md5 on older clusters:

auth_type = md5

But if you’re standing up a new install, use scram-sha-256 — md5 is weak now.

Choosing a pool mode

There are three modes; for most Laravel/Django apps the choice is:

  • session — the connection stays the same from start to finish. Prepared statements work, but the pooling benefit is weak.
  • transaction — the connection is released at the end of each transaction. The most common choice. You can’t use prepared statements by default (server-side prepare).
  • statement — released at the end of each query. Problematic with most ORMs.

Practical: start with transaction, switch to session if you run into trouble.

Common mistake: the read-only role has no password

auth_query reads pg_shadow.passwd. If the user comes in via SSO or uses peer authentication, the passwd field is NULL — pgBouncer rejects the login. In that case you’d need to set up trust or peer through pg_hba.conf, but you don’t want that in production.

Monitoring

pgBouncer provides its own admin DB:

psql -p 6432 pgbouncer -U pgbouncer
> SHOW POOLS;
> SHOW STATS;
> SHOW CLIENTS;

If you see cl_waiting > 0, you may need to raise default_pool_size. If sv_idle is very high, it’s the opposite — the pool is overprovisioned.