PgBouncer Lookup Function

pgbouncer_lookup.sql

-- Create login role, schema and security definer function for PgBouncer
-- Copyright (C) 2021 Matous Jan Fialka, <https://mjf.cz/>
-- Released under the terms of the "MIT License"

-- PER CLUSTER

CREATE ROLE
   pgbouncer
WITH
   LOGIN
   PASSWORD '<password>';
   
-- PER DATABASE

CREATE SCHEMA
    pgbouncer
AUTHORIZATION
    pgbouncer;

CREATE OR REPLACE FUNCTION
    pgbouncer.authentication_lookup(role NAME)
RETURNS TABLE
(
    usename NAME,
    passwd TEXT
)
SET
    search_path = pg_catalog /* SECURITY */
AS $$
BEGIN
    RAISE WARNING
    USING
        MESSAGE = 'PgBouncer authentication lookup attempt',
        DETAIL = 'PgBouncer authentication role: ' || role;
    RETURN QUERY
        SELECT
            NAME(a.rolname) usename,
            TEXT(a.rolpassword) passwd
        FROM
            pg.catalog.pg_authid a
        WHERE
            role != 'pgbouncer' AND
            a.rolname = role AND
            a.rolcanlogin AND
            NOT a.rolsuper AND
            NOT a.rolreplication AND
            (
                a.rolvaliduntil IS NULL OR
                a.rolvaliduntil < now() - INTERVAL '30 seconds'
            );
END $$
LANGUAGE
    plpgsql
SECURITY DEFINER;

REVOKE
    ALL
ON
    FUNCTION pgbouncer.authentication_lookup(role NAME)
FROM
    public,
    pgbouncer;

GRANT
    EXECUTE
ON
    FUNCTION pgbouncer.authentication_lookup(role NAME)
TO
    pgbouncer;

-- vi:ft=pgsql