-- This file sets up the database role and function for PgBouncer authentication.
CREATE ROLE pgbouncer_auth WITH LOGIN PASSWORD 'REPLACE_ME_LONG_RANDOM5432';
-- Execute the following so the pgbouncer_auth role can access the get_auth function and schema.
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;
Get Auth
-- This file sets up the database role and function for PgBouncer authentication.
CREATE SCHEMA IF NOT EXISTS pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer_auth;
-- Create the get_auth function that PgBouncer will call to retrieve user credentials.
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE(username text, password text)
LANGUAGE sql
SECURITY DEFINER
AS $$
SELECT rolname::text, rolpassword::text
FROM pg_authid
WHERE rolname = p_usename;
$$;
-- Execute the following so the pgbouncer_auth role can access the get_auth function and schema.
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO pgbouncer_auth;
Min pgbouncer.ini
[databases]
aws_dbz_001 = host=172.31.11.248 port=5432 dbname=example auth_user=pgbouncer_auth
aws_dbz_001_ro = host=172.31.9.92 port=5432 dbname=example auth_user=pgbouncer_auth
aws_dbz_sales = host=172.31.11.248 port=5432 dbname=sales auth_user=pgbouncer_auth
aws_dbz_sales_ro = host=172.31.9.92 port=5432 dbname=sales auth_user=pgbouncer_auth
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = scram-sha-256
auth_user = pgbouncer_auth
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)
auth_file = /etc/pgbouncer/userlist.txt
; Use session pooling for long-running reports
; Use transaction pooling for short transactions (e.g. BI tools)
pool_mode = transaction
max_client_conn = 200
; Cap true DB concurrency for reporting
default_pool_size = 25
reserve_pool_size = 10
reserve_pool_timeout = 5
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
