postgres.dk

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
pgbouncer and scram-sha-256

create an auth user, ie pgbouncer
fetch the encrypted password

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

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.

<code>
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;


</code>

Minimum /etc/pgbouncer/pgbouncer.ini

<code>
[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
</code>

pg_hba.conf at the cluster
<code>
..
..
host    all             all             PG_BOUNCER_CIDR        scram-sha-256
</code>