Debugging auth in PgBouncer

When something fails inOdoo → PgBouncer → PostgreSQL, auth errors often seem “mysterious”… until you break the problem into two hops:

1. Client (Odoo/psql) → PgBouncer(incomingauth)

2. PgBouncer → PostgreSQL(outgoingauth)

PgBouncer does both things and, by design, the auth_file can be usedbothto validate clientsandto authenticate against the backend (if the backend requests a password). PgBouncer

This post is a “copy/paste” debugging playbook.

1) Quick triage: where does it break?

Minimal test (without Odoo): connect to PgBouncer with psql

psql "host=PGBOUNCER_HOST port=6432 dbname=YOUR_DB user=odoo"

• Ifthis fails, the problem is betweenclient → PgBouncer(userlist, auth_type, TLS, HBA in PgBouncer, etc.).

• Ifthis worksbut Odoo fails, it is usually: dbfilter, wrong database, different password, or connectivity from another IP/host.

2) Enable useful signals (without “eternal debug”)

In pgbouncer.ini, make sure to have logs that really explain auth:

[pgbouncer]
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
verbose = 1

• log_connections: logs successful logins. PgBouncer

• log_disconnections: logs disconnections with reason. PgBouncer

• log_pooler_errors: logs errors that PgBouncer sends to the client. PgBouncer

• verbose: increases verbosity (up to 3). PgBouncer

If you rotate the logfile: PgBouncer keeps the file open; after rotation, do kill -HUP or RELOAD to reopen the log. PgBouncer+1

3) Enter the PgBouncer admin console (your best friend)

Connect to the “admin DB” pgbouncer:

psql "host=PGBOUNCER_HOST port=6432 dbname=pgbouncer user=ADMIN_USER"

Make sure ADMIN_USER is in admin_users (or at least in stats_users if you only want read access). PgBouncer

Essential commands (recommended order)

SHOW CONFIG;
SHOW USERS;
SHOW DATABASES;
SHOW CLIENTS;
SHOW SERVERS;
SHOW POOLS;
SHOW STATS;

And when you changed config or userlist:

RELOAD;

RELOAD reloads the configand alsothe auth_file and auth_hba_file. PgBouncer+1

Power tip: you can enable logs “on the fly” with:

SET log_connections = 1;
SET verbose = 2;

(this is executed in the admin console and changes PgBouncer settings). PgBouncer

4) 80% of “auth failed” is in auth_type + auth_file

4.1 Check auth_type (and its most common trap)

PgBouncer clearly defines:

• scram-sha-256: auth_file must haveSCRAM secrets or passwords in plain text. PgBouncer+1

• trust:does not validate password, but the userstill must existin auth_file. PgBouncer

• hba: the actual method comes from the auth_hba_file. PgBouncer

4.2 Validates the actual format of userlist.txt

The expected format is:

"username1" "password" ...
"username2" "md5abcdef..." ...
"username3" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>"

• There must beat least 2 fieldsin quotes.

• PgBouncerignores the rest of the line.

• Double quotes within a field are escaped as "". PgBouncer

5) Is your problem outgoing? (PgBouncer → PostgreSQL)

This case is typical when:

• PgBouncer accepts the client, but then fails to open a connection to the backend.

• You see SASL/SCRAM errors or “password authentication failed” even though the userlist “seems correct”.

Key point (🔥): SCRAM secret does not always work to log into the backend

PgBouncer explains an important limitation:

• The secrets in the auth_file serve 2 purposes:

  1. to verify passwords of incoming clients

  2. to use them as passwords for connections to the backend (if the backend requires it) PgBouncer

• But: aSCRAM secretit can only be used to log into the server if strict conditions are met (SCRAM also in client auth, no forced user in the DB definition, andidentical secretin PgBouncer and Postgres: same salt and iterations). This exists because of a property of SCRAM: the stored secret alone is not sufficient to derive login credentials. PgBouncer

👉 Operational translation:

• If your Postgres requires SCRAM andPgBouncer does not knoweither theclear text passwordor theexact SCRAM secret, you will see auth fail "on the backend side".

6) If you use auth_user + auth_query, debug this first

When auth_user is configured:

• Users not in auth_file are queried with auth_query in the DB using auth_user.

• The password for auth_user is taken from the auth_file. PgBouncer

PgBouncer even comes with a default auth_query that queries pg_authid and considers rolvaliduntil. PgBouncer

Classic pitfall:direct access to pg_authid requires high permissions; PgBouncer recommends using a non-superuser that calls a SECURITY DEFINER function. PgBouncer+1

What to look for in logs when it fails here

• Can PgBouncer connect to the auth_dbname/target DB?

• Does the auth_user have the correct password in auth_file?

• Does the auth_query return 2 columns (user + password/secret) and not NULL due to expiration?

7) "Dictionary" of typical errors and what they mean

no such user

Almost always:

• user is not in auth_file, and there is no active auth_user; or

• the userlist was not reloaded (RELOAD was missing). PgBouncer+1

SASL authentication failed (SCRAM)

• incorrect password or bad userlist.

• or PgBouncer→Postgres fails because it does not have valid credentials for SCRAM (see section 5). PgBouncer+1

“Changed the userlist and nothing happens”

Standard solution:

• edit userlist.txt

• RELOAD; (admin console)

  Because RELOAD also reloads auth_file and auth_hba_file. PgBouncer

8) Final checklist ("fire-fighting" mode)

• I tested with psql directly to PgBouncer (without Odoo)

• I entered dbname=pgbouncer and ran SHOW USERS/POOLS/CLIENTS/SERVERS

• I confirmed auth_type and if auth_hba_file applies PgBouncer+1

• I validated the format of userlist.txt (quotes, at least 2 fields) PgBouncer

• After changes: RELOAD PgBouncer

• If there is SCRAM: I understood the limitation of the SCRAM secret for logging into the backend PgBouncer

• If I use auth_user/auth_query: I checked permissions and the default/query result PgBouncer+1
  
  
  

Next chapter ->