When you put PgBouncer between Odoo and PostgreSQL, the real question is not "Does it connect?" but:
Who can connect?
Where are the credentials stored?
What minimum permissions does each role have?
What happens when you rotate passwords?
This post gives you a baseline ofusers/roles, how to keep auth_file healthy, and aminimum securitythat avoids the most common mistakes (and scares).
1) Quick mental model: there are 2 authentications
With PgBouncer, there are always two "logins":
Client → PgBouncer(Odoo authenticates against PgBouncer)
PgBouncer → PostgreSQL(PgBouncer opens real connections to the server)
That's why your decisions about users/auth_file affecttwo links, not one. Crunchy Data
2) Recommended users (minimum) and what each one is for
A) odoo (application user)
This is the user that Odoo works with in the database. It must have permissions onitsDB (tables/functions/sequences that Odoo uses).
Recommendation:one user per instance or per "group" of instances (multi-tenant), depending on your operation.
B) pgbouncer_auth (technical user for auth_user / auth_query) — optional, but very useful
If you want to centralize credentials and not live syncing userlist.txt, PgBouncer can query the password in PostgreSQL using auth_user + auth_query. PgBouncer+1
Advantage:userlist.txt ends up with very few users (and rotating keys is simplified).
C) postgres / DBA (admin only)
Do not use it for Odoo or PgBouncer.
3) auth_file (userlist.txt): format, permissions, and best practices
Correct format
PgBouncer documents the format as follows:
"username" "password" (plain text)
"username" "md5..." (Postgres style MD5)
"username" "SCRAM-SHA-256$<iterations>:<salt>$<storedkey>:<serverkey>" PgBouncer
Example:
"odoo" "SCRAM-SHA-256$4096:....$....:...." "pgbouncer_auth" "super-secure-in-another-system" ; if you decide to use plain text (see note below)
Note: PgBouncer "ignores the rest of the line" after the second field, so you can comment on the right without breaking the format. PgBouncer
File permissions (minimum real security)
Owner: the service user (e.g. pgbouncer)
Permissions:0600(owner read/write only)
Location: outside of repositories and "open" backups
This is one of the most important things: if someone reads that file, they can open your DB.
Rotation without downtime
PgBouncer canreloadthe auth_file with reload (without a hard restart). The usage document indicates that the reload also reloads auth_file and auth_hba_file. PgBouncer
4) SCRAM, auth_user, and the most common trap
If you use auth_type = scram-sha-256 (the recommended option today), two realities emerge:
To validate the client, userlist.txt may contain aSCRAM secret. PgBouncer+1
For PgBouncer to authenticate to PostgreSQLusing auth_user, it may sometimes requireplain text(it depends on how you configure it and the exact flow), because the SCRAM secret is not always reusable as a "login credential" by itself. Stack Overflow+1
Practical rule to avoid headaches:
If you do NOT need auth_user/auth_query: keep everything simple with auth_file (SCRAM secrets) and that's it.
If you DO need auth_user/auth_query: review the case carefully, because you may end up needingthe password in plain textof the auth_user in userlist.txt (and that requires even more care with permissions/secret management). GitHub+1
5) Option 1 (simple): everything in auth_file (without auth_user)
PgBouncer (pgbouncer.ini)
[pgbouncer] auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt pool_mode = transaction
Pros
Easy to understand and debug.
You do not depend on DB queries for auth.
Cons
Each password rotation requires updating userlist.txt (and reloading PgBouncer).
6) Option 2 (recommended for large teams): auth_user + auth_query to centralize credentials
The idea:PgBouncer queries Postgresthe hash/password of the user trying to log in, using a technical user (auth_user). PgBouncer has supported this for years (auth_user and auth_query parameters). PgBouncer+2Crunchy Data+2
PgBouncer
[pgbouncer] auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt auth_user = pgbouncer_auth auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
Note: the classic default was pg_shadow, although in recent versions there have been changes/adjustments to the default auth_query (the "VALID UNTIL" issue), so if you modify auth_query custom, keep PgBouncer updated and check the changelog/release notes. PgBouncer+1
Pros
userlist.txt remains minimal (only pgbouncer_auth + maybe 1-2 more).
Password rotation: the source of truth is Postgres.
Cons
More moving parts.
You must ensure permissions for auth_user and understand SCRAM/credentials (see section 4).
7) Additional minimum security (the one that matters)
A) Do not use auth_type=trust (except in labs)
"Trust" literally means "no password". Even guides show it as an example, but for production it is a NO. Percona
B) Restrict by network (firewall) and by listen_addr
PgBouncershould not listen on 0.0.0.0unless necessary.
Allow only IPs from your Odoo / your private network.
PostgreSQL: allows connections only from PgBouncer (and from your bastion/DBA).
C) Separate credentials by environment
odoo_prod, odoo_stage, etc.
No reusing passwords between environments.
D) TLS if there is an untrusted network
If Odoo↔PgBouncer or PgBouncer↔Postgres cross subnets that you do not control, consider TLS. (SCRAM does not replace channel encryption.)
8) Quick checklist (to paste at the end of the post)
I have a userodoowith permissions only for its DB
userlist.txt in the correct format ("user" "secret") PgBouncer
userlist.txt with permissions0600
I avoid trust Percona
I can reload PgBouncer after rotation (reload reloads auth_file) PgBouncer
If I use auth_user/auth_query, I understood the SCRAM topic and the possible requirement for clear text password for auth_user GitHub+1
Firewall: Postgres acepta solo desde PgBouncer; PgBouncer solo desde Odoo