If you want a stackmodern and defendable, this is the way:PostgreSQL with SCRAM passwords + PgBouncer authenticating clients with SCRAM. It is more secure than md5 (also, MD5 isdeprecatedin PostgreSQL and will be removed in a future version). PostgreSQL
Below you have a procedurecopy/paste, with validations and real “pitfalls.”
0) Quick requirements (to avoid stumbling)
Your client (Odoo/psycopg/libpq) must support SCRAM: PostgreSQL warns that scram-sha-256is not supported by old client libraries. PostgreSQL
PgBouncer supports auth_type = scram-sha-256 and allows the auth_file to containSCRAM secrets or plaintext passwords. PgBouncer
1) PostgreSQL: enable SCRAM as password format
1.1 Adjust password_encryption
In postgresql.conf:
password_encryption = scram-sha-256
This makes it so thatnew passwords(and rotated ones) are stored as SCRAM. PostgreSQL
Reload/restart PostgreSQL according to your method.
1.2 Rotate the password of the user that uses Odoo
In psql as superuser:
ALTER ROLE odoo WITH PASSWORD 'A_LONG_AND_UNIQUE_KEY';
1.3 Validate that it is in SCRAM format
SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'odoo';
You should see a value that starts with SCRAM-SHA-256$... (that is the stored "SCRAM secret"). PgBouncer+1
2) PostgreSQL: enforce SCRAM authentication in pg_hba.conf
In the rule that applies to PgBouncer (by IP/network), change to:
host all all 10.0.0.0/8 scram-sha-256
Reload PostgreSQL.
Zero-downtime migration tip:PostgreSQL can facilitate the transition because if you set md5 in pg_hba.conf but the user already has a SCRAM password, SCRAM is automatically used. Useful for phased migration. PostgreSQL
3) PgBouncer: prepare auth_file (userlist.txt)
PgBouncer defines the file format as follows:
"user" "password|md5...|SCRAM-SHA-256$..." PgBouncer
You have2 options(choose one):
Option A (the simplest): store password in plain text in userlist.txt
/etc/pgbouncer/userlist.txt:
"odoo" "A_LONG_AND_UNIQUE_PASSWORD"
✅ Pros: works well forclient→PgBouncerand forPgBouncer→PostgreSQL.
⚠️ Cons: you must secure the file (permissions 0600, correct owner).
Option B (more "purist"): store the SCRAM secret in userlist.txt
Get the secret from PostgreSQL (pg_authid.rolpassword) and paste it:
"odoo" "SCRAM-SHA-256$4096:...$...:..."
✅ Pros: PgBouncer can authenticate clients with SCRAM using secrets. PgBouncer
⚠️ Important note:that secret does not always work for PgBouncer to log in to PostgreSQL, because the stored secret cannot be used as a password by design, except under specific conditions (same salt/iterations/identical secret, etc.). In many cases, if you only put the SCRAM secret, PgBouncerwill not be able to authenticate against Postgresand will ask you for a plaintext password. Stack Overflow+1
If your priority is "to make it work and be operable", use Option A + strict permissions, or use auth_user/auth_query (see section 6).
4) PgBouncer: configure auth_type = scram-sha-256
In pgbouncer.ini:
[pgbouncer] listen_addr = 0.0.0.0 listen_port = 6432 auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt ; optional (default 4096) scram_iterations = 4096
PgBouncer explicitly documents auth_type = scram-sha-256 and that auth_file can containSCRAM secrets or plaintext. PgBouncer
scram_iterations controls the computational cost ofencryptingpasswords in SCRAM (more iterations = harder against brute force, but slower auth). PgBouncer
5) Reload PgBouncer (without a hard restart)
You can reload the PgBouncer config and take changes from the auth_file with RELOAD (admin console) or SIGHUP.
PgBouncer documents that when reloading, it updates the configuration and alsoreloads auth_file and auth_hba_file. PgBouncer
6) (Recommended for large teams) Centralize auth with auth_user + auth_query
If you don't want to keep editing userlist.txt for each user, PgBouncer can query credentials from PostgreSQL using auth_user and auth_query. PgBouncer
Example (conceptual):
[pgbouncer] auth_type = scram-sha-256 auth_file = /etc/pgbouncer/userlist.txt auth_user = pgbouncer_auth ; use the default or a custom one, ideally via SECURITY DEFINER function
PgBouncer explains that the password for auth_user is taken from the auth_file and that accessing pg_authid requires elevated privileges (recommends SECURITY DEFINER function). PgBouncer
7) Odoo: points to PgBouncer
In your odoo.conf:
db_host = 127.0.0.1 db_port = 6432 db_user = odoo db_password = A_LONG_AND_UNIQUE_PASSWORD
Then restart Odoo.
8) Quick verification (to be 100% sure)
8.1 From the same Odoo machine, test with psql against PgBouncer
psql "host=127.0.0.1 port=6432 dbname=YOUR_DB user=odoo"
8.2 Confirm in PostgreSQL that the user has SCRAM password
(already seen in step 1.3). PostgreSQL
Typical problems and solutions (the 3 that occur most often)
“FATAL: SASL authentication failed”
Incorrect password, or userlist.txt did not reload, or old client without SCRAM. PostgreSQL+1
“PgBouncer cannot connect to Postgres when I put the SCRAM secret in userlist”
Expected: the secret does not always serve as a login credential. Use clear text password (with strict permissions) or set up auth_user/auth_query. GitHub+1
Migration from md5 with unexpected downtime
Do it in stages: first set password_encryption = scram-sha-256, rotate passwords, and then change pg_hba.conf to scram-sha-256 when all clients are ready. PostgreSQL