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

SELECT usename, passwd FROM pg_shadow WHERE usename= 'pgbouncer';

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

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

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

In /etc/pgbouncer/pgbouncer.ini

set:

1. auth_type = scram-sha-256
2. auth_file = /etc/pgbouncer/userlist.txt
3. auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
4. auth_user=pgbouncer

When this is working, change auth_query to something more secure, ie from pgbouncer.org

CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
RETURNS record AS $$
BEGIN
    SELECT usename, passwd FROM pg_catalog.pg_shadow
    WHERE usename = i_username INTO uname, phash;
    RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;

and set the auth_query to

auth_query =