PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

For this section, we will have a PgBouncer server on pgha-proxy, and a standard PostgreSQL server at pgha1.

Follow these steps to improve PgBouncer authentication:

1. Connect to pgha1 as the postgres user to the postgres database, and execute this 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 NOTICE 'PgBouncer auth request: %', p_usename; RETURN QUERY SELECT usename::TEXT, passwd::TEXT FROM pg_catalog.pg_shadow WHERE usename = p_usename; 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 ...