postgres.dk

This is an old revision of the document!

---

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:

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