---
title: "Multiple DB Users with pgBouncer auth_query"
description: "A practical setup for dynamic user authentication via `auth_query` instead of `userlist.txt` when running pgBouncer"
url: https://sade.dev/en/notes/pgbouncer-auth-query/
lang: en
author: "Muhammet Şafak"
published: 2026-04-19
updated: 2026-09-06
section: Note
tags: ["postgresql","pgbouncer","production"]
summary: "auth_query moves pgBouncer's user list out of userlist.txt and into PostgreSQL itself, so adding a role no longer means editing a file and reloading. The bill comes in three parts: the lookup function is installed separately in every database it serves, it needs SECURITY DEFINER, and a user whose pg_shadow.passwd is NULL can never log in."
---

# Multiple DB Users with pgBouncer auth_query

> auth_query moves pgBouncer's user list out of userlist.txt and into PostgreSQL itself, so adding a role no longer means editing a file and reloading. The bill comes in three parts: the lookup function is installed separately in every database it serves, it needs SECURITY DEFINER, and a user whose pg_shadow.passwd is NULL can never log in.

`pgBouncer` is excellent as a connection pooler, but its default configuration comes with one 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` lookup function:

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

CREATE SCHEMA pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;

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`.

`auth_query` runs inside the target database, so the function has to be installed into every database pgBouncer serves.

## pgBouncer config

`pgbouncer.ini`:

```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 or scram-sha-256?

PostgreSQL 14+ uses `scram-sha-256` by default. pgBouncer has supported it since 1.11, and SCRAM pass-through with `auth_query` since 1.14. If you're forced to fall back to md5 on older clusters:

```ini
auth_type = md5
```

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

## Which pool mode should you choose?

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

- **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. Protocol-level prepared statements need 1.21+; since 1.24 they are enabled by default (`max_prepared_statements = 200`).
- **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.

## Frequently asked

**Does the auth_query function have to be installed in every database?**

Yes. auth_query runs inside the target database, so the lookup function has to be installed into each database pgBouncer serves. Installing it in one and forgetting the rest produces logins that fail silently on those databases.

**Why can a user with the correct password still fail to log in?**

auth_query reads pg_shadow.passwd. If the user arrives via SSO or peer authentication that field is NULL, and pgBouncer rejects the login. The password is not wrong; there is no password hash to read.


## Sources

- [pgbouncer(5): auth_query, auth_user and pool_mode](https://www.pgbouncer.org/config.html) — PgBouncer
- [PgBouncer changelog](https://www.pgbouncer.org/changelog.html) — PgBouncer
- [PostgreSQL 14 release notes: password_encryption now defaults to scram-sha-256](https://www.postgresql.org/docs/release/14.0/) — PostgreSQL
- [CREATE FUNCTION: SECURITY DEFINER](https://www.postgresql.org/docs/current/sql-createfunction.html) — PostgreSQL