pgbouncer quick setup

gistfile1.txt

cybertec-postgresql.com/en/pgbouncer-authentication-made-easy/

apt-get install postgresql pgbouncer

sudo su - postgres

psql << EOF
CREATE ROLE pgbouncer LOGIN;
-- set a password for the user
\password pgbouncer
 
CREATE FUNCTION public.lookup (
   INOUT p_user     name,
   OUT   p_password text
) RETURNS record
   LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;
 
-- make sure only "pgbouncer" can use the function
REVOKE EXECUTE ON FUNCTION public.lookup(name) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.lookup(name) TO pgbouncer;
EOF


verify

#psql 

postgres=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | pgbouncer
Attributes | 
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}

postgres=# \df+ *public.lookup*
List of functions
-[ RECORD 1 ]-------+-------------------------------------------------------------
Schema              | public
Name                | lookup
Result data type    | record
Argument data types | INOUT p_user name, OUT p_password text
Type                | func
Volatility          | volatile
Parallel            | unsafe
Owner               | postgres
Security            | definer
Access privileges   | postgres=X/postgres                                         +
                    | pgbouncer=X/postgres
Language            | sql
Source code         | SELECT usename, passwd FROM pg_shadow WHERE usename = p_user
Description         | 


# check ini config

root@sh1:~# cat /etc/pgbouncer/userlist.txt 
"postgres" "md524bb002702969490e41e26e1a454036c"
root@sh1:~# cat /etc/pgbouncer/pg_hba.conf 
host    all all 0.0.0.0/0 md5
host    all all 127.0.0.1 md5
root@sh1:~# cat /etc/pgbouncer/pgbouncer.ini 
[databases]

* = host=localhost


[pgbouncer]

;; auth_type = hba ;; if set then only allow user from pg_hba.conf below
auth_type = md5 ;; allow via md5 auth
;; auth_hba_file = /etc/pgbouncer/pg_hba.conf
auth_file = /etc/pgbouncer/userlist.txt. ;; list of user/md5password text file

admin_users = postgres ;; Comma-separated list of database users that are allowed to connect and run all commands on the console. Ignored when auth_type is any, in which case any user name is allowed in as admin.

auth_user = postgres ;; If auth_user is set, then any user not specified in auth_file will be queried through the auth_query query from pg_shadow in the database, using auth_user. The password of auth_user will be taken from auth_file. (If the auth_user does not require a password then it does not need to be defined in auth_file.)
auth_query = SELECT p_user, p_password FROM public.lookup($1)

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

;;;
;;; Where to wait for clients
;;;

;; IP address or * which means all IPs
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql



########

root@sh1:~# systemctl restart pgbouncer
root@sh1:~# su - postgres
postgres@sh1:~$ 
postgres@sh1:~$ psql -d pgbouncer -p 6432 -U pgbouncer
psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1), server 1.12.0/bouncer)
Type "help" for help.

pgbouncer=# show clients;
pgbouncer=# \x
Expanded display is on.
pgbouncer=# show clients;
-[ RECORD 1 ]+------------------------
type         | C
user         | pgbouncer
database     | pgbouncer
state        | active
addr         | unix
port         | 6432
local_addr   | unix
local_port   | 6432
connect_time | 2022-05-26 14:38:29 IST
request_time | 2022-05-26 14:38:35 IST
wait         | 3
wait_us      | 564582
close_needed | 0
ptr          | 0xaaaad571d420
link         | 
remote_pid   | 57494
tls          | 

pgbouncer=# \q
postgres@sh1:~$ psql -d pgbouncer -p 6432 -U postgres
Password for user postgres: 
psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1), server 1.12.0/bouncer)
Type "help" for help.

pgbouncer=# \x
Expanded display is on.
pgbouncer=# show clients;
-[ RECORD 1 ]+------------------------
type         | C
user         | postgres
database     | pgbouncer
state        | active
addr         | unix
port         | 6432
local_addr   | unix
local_port   | 6432
connect_time | 2022-05-26 14:38:44 IST
request_time | 2022-05-26 14:38:52 IST
wait         | 0
wait_us      | 0
close_needed | 0
ptr          | 0xaaaad571d420
link         | 
remote_pid   | 57505
tls          | 

pgbouncer=# \q
postgres@sh1:~$ psql -d postgres -p 5432 -U postgres
psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# \q
postgres@sh1:~$ psql -d postgres -p 5432 -U postgres -h 127.0.0.1
Password for user postgres: 
psql (12.11 (Ubuntu 12.11-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.