Differences

This shows you the differences between two versions of the page.

--- pgbouncer [2024/08/27 14:51] – jbi
+++ pgbouncer [2024/08/30 11:35] (current) – jbi
@@ Line 5: / Line 5: @@
 <code>
-SELECT usename, passwd FROM pg_shadow WHERE usename= 'pgbouncer';
+psql   -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"
 </code>
-insert encrypted password into /etc/pgbouncer/userlist.txt
+insert encrypted password into **/etc/pgbouncer/userlist.txt**
+ie:
 "pgbouncer" "SCRAM-SHA-256$4096:LPciR/nnlo7XYeC29ANVig==$i........="
-In /etc/pgbouncer/pgbouncer.ini
+Per database setup. pgbouncer schema and the function should be in every database that pgbouncer access. You could alter template1 to ensure this.
-set
+<code>
+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;
-  - auth_type = scram-sha-256
+CREATE OR REPLACE FUNCTION pgbouncer.get_auth(username TEXT)
-  - auth_file = /etc/pgbouncer/userlist.txt
+RETURNS TABLE(username TEXT, password TEXT) AS
-  - auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
+$$
-  - auth_user=pgbouncer
+ 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;
-When this is working, change auth_query to something more secure, ie from pgbouncer.org
-<code>
-CREATE OR REPLACE FUNCTION pgbouncer.user_lookup(in i_username text, out uname text, out phash text)
-RETURNS record AS $$
-BEGIN
-    SELECT usename, passwd FROM pg_catalog.pg_shadow
-    WHERE usename = i_username INTO uname, phash;
-    RETURN;
-END;
-$$ LANGUAGE plpgsql SECURITY DEFINER;
-REVOKE ALL ON FUNCTION pgbouncer.user_lookup(text) FROM public, pgbouncer;
-GRANT EXECUTE ON FUNCTION pgbouncer.user_lookup(text) TO pgbouncer;
 </code>
+Minimum /etc/pgbouncer/pgbouncer.ini
+<code>
+[databases]
+* = host=10.97.97.97 port=5432 auth_user=pgbouncer
+[users]
+[pgbouncer]
+logfile = /var/log/postgresql/pgbouncer.log
+pidfile = /var/run/postgresql/pgbouncer.pid
+listen_addr = *
+listen_port = 6432
+unix_socket_dir = /var/run/postgresql
+auth_type = md5
+auth_file = /etc/pgbouncer/userlist.txt
+auth_query = SELECT username, password from pgbouncer.get_auth($1)
+admin_users = pgbouncer
+stats_users = pgbouncer
+</code>
+pg_hba.conf at the cluster
+<code>
+..
+..
+host    all             all             PG_BOUNCER_CIDR        scram-sha-256
+</code>