pgbouncer.get_auth.sql

CREATE SCHEMA pgbouncer AUTHORIZATION pgbouncer;
 
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS
$$
BEGIN
    RAISE WARNING 'PgBouncer auth request: %', p_usename;
 
    RETURN QUERY
    SELECT u.rolname::TEXT
         , u.rolpassword::TEXT
      FROM pg_authid g
      JOIN pg_auth_members m
        ON (m.roleid = g.oid)
      JOIN pg_authid u
        ON (u.oid = m.member)
     WHERE NOT u.rolsuper
       AND g.rolname = 'use_proxy'
       AND u.rolname = p_username;

END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

REVOKE ALL ON FUNCTION pgbouncer.get_auth(p_usename TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(p_usename TEXT) TO pgbouncer;