PgBouncer With Auth_query

By Clemens Schwaighofer

- 3 minutes read - 582 words

PgBouncer with auth_query

This is a copy from Medium: https://medium.com/@gullevek/pgbouncer-with-auth-query-79c622d08ea4

While preparing to upgrade to PostgreSQL 15 I realized I never pushed the auth type from md5 to scram-sha-256 and while researching this I found out that there is “auth_query” to query the PostgreSQL auth table directly and not having a double set of of logins in the userlist file for PgBouncer.

Problem is that all the documentations were great but none perfect. Here I just want to list down the things I did to make sure it all worked fine.

  • make sure the function is in its on schema and insert it into template1 so it will be used for all new databases
  • this schema + function must exist in all databases that will be accessed by PgBouncer

Upgrade to scram-sha-256

  • Be sure that pg_hba.conf is setup to use scram-sha-256 instead of md5.
  • Be sure that PostgreSQL is setup to store the password in scram-sha-256

Use the query below to check which users have not yet been upgraded to scram-sha-256

SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;

Upgrade the users with the usual alter role command or \password call.

List all current users

SELECT rolname::TEXT, rolpassword::TEXT,
  pg_authid.rolsuper, pg_authid.rolreplication, pg_authid.rolcanlogin,
  pg_authid.rolvaliduntil, pg_authid.rolvaliduntil
FROM pg_catalog.pg_authid;

PostgreSQL side

After the first run, be sure to setup a password for the PgBouncer user with

ALTER ROLE pgbouncer WITH PASSWORD '<password>';

The basic script below:

  • on default the user does not auth from super users
  • It should be installed in template1 so that it will be auto copied over to all new databases
  • If never setup before it must be added to all databases, or at least the ones where PgBouncer will access to
DO $$
BEGIN
 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'pgbouncer') THEN
 CREATE ROLE pgbouncer LOGIN;
 -- set password for user
 -- ALTER ROLE pgbouncer WITH PASSWORD '<password>';
 END IF;
END
$$;

REVOKE ALL PRIVILEGES ON SCHEMA public FROM pgbouncer;
CREATE SCHEMA IF NOT EXISTS pgbouncer;
REVOKE ALL PRIVILEGES ON SCHEMA pgbouncer FROM pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;
CREATE OR REPLACE FUNCTION pgbouncer.get_auth(username TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS
$$
 SELECT rolname::TEXT, rolpassword::TEXT
 FROM pg_catalog.pg_authid
 WHERE
 -- disable line below if superusers (postgres) should connect to
 NOT pg_authid.rolsuper AND
 NOT pg_authid.rolreplication AND
 pg_authid.rolcanlogin AND
 pg_authid.rolname <> 'pgbouncer' AND (
   pg_authid.rolvaliduntil IS NULL OR
   pg_authid.rolvaliduntil >= CURRENT_TIMESTAMP
 ) AND
 pg_authid.rolname = $1;
$$
LANGUAGE SQL STABLE SECURITY DEFINER;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(username TEXT) FROM PUBLIC, pgbouncer;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(username TEXT) TO pgbouncer;

pgbouncer.ini setup

Key points are

  • auth_user
  • auth_query
  • admin_users/stats_users set with user that exists in the userlist.txt file
[databases]
;; list of databases, but not needed, fallback is enough
;; dbname =
 
;; fallback connect string
* = host=127.0.0.1 port=5432

[pgbouncer]
; ...
listen_addr = localhost
listen_port = 6432
; ...
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_query = SELECT username, password from pgbouncer.get_auth($1)
auth_user = pgbouncer
; ...
admin_users = pgbouncer
stats_users = pgbouncer

auth_file (userlist.txt)

The auth user must exist in the userlist.txt and as we have defined it as md5 be sure to setup the correct string as below

echo -n "md5"; echo -n "<pgbouncer password>pgbouncer" | md5sum

so the userlist.txt entry looks like

"pgbouncer" "md5<string>"

If there are additional monitoring users for PgBouncer (eg from zabbix) they need to be added in the stats_users and userlist.txt too

Test

psql -U <user> -p 6432 <database>

Blogs I read for this

https://www.crunchydata.com/blog/pgbouncer-scram-authentication-postgresql

https://www.2ndquadrant.com/en/blog/pg-phriday-securing-pgbouncer/

https://www.cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/

https://www.enterprisedb.com/postgres-tutorials/pgbouncer-authquery-and-authuser-pro-tips

https://www.pgbouncer.org/config.html