pgbouncer and scram-sha-256

create an auth user, ie pgbouncer fetch the encrypted password

psql   -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

insert encrypted password into /etc/pgbouncer/userlist.txt

ie: “pgbouncer” “SCRAM-SHA-256$4096:LPciR/nnlo7XYeC29ANVig==$i……..=”

Per database setup. pgbouncer schema and the function should be in every database that pgbouncer access. You could alter template1 to ensure this.

REVOKE ALL PRIVILEGES ON SCHEMA public FROM pgbouncer;
CREATE SCHEMA IF NOT EXISTS pgbouncer;
REVOKE ALL PRIVILEGES ON SCHEMA pgbouncer FROM pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(username TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS
$$
 SELECT rolname::TEXT, rolpassword::TEXT
 FROM pg_catalog.pg_authid
 WHERE
 -- disable line below if superusers (postgres) should connect to
 NOT pg_authid.rolsuper AND
 NOT pg_authid.rolreplication AND
 pg_authid.rolcanlogin AND
 pg_authid.rolname <> 'pgbouncer' AND (
   pg_authid.rolvaliduntil IS NULL OR
   pg_authid.rolvaliduntil >= CURRENT_TIMESTAMP
 ) AND
 pg_authid.rolname = $1;
$$
LANGUAGE SQL STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(username TEXT) FROM PUBLIC, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(username TEXT) TO pgbouncer;

Minimum /etc/pgbouncer/pgbouncer.ini

[databases]
* = host=10.97.97.97 port=5432 auth_user=pgbouncer
[users]

[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT username, password from pgbouncer.get_auth($1)
admin_users = pgbouncer
stats_users = pgbouncer

pg_hba.conf at the cluster

..
..
host    all             all             PG_BOUNCER_CIDR        scram-sha-256