Pgbouncer

https://pgbouncer.github.io/ is a lightweight postgresql connection pooler.

Opening postgresql connection will create a new process on the postgresql server, unless you're using a persistent connection you want a connection pooler.

• http://richyen.com/pgbouncer/postgres/2018/11/21/pgbouncer_auth_user.html
• https://hunleyd.github.io/posts/pgBouncer-and-auth-pass-thru/

The idea is to use a dedicated role to perform authentication. For better security we only allow the role to login and to execute a PL/SQL function in the target database.

First make local connections (unix socket) use password instead of peer authentication:

local    all   all    md5  # instead of "peer"

sudo systemctl reload postgresql

Then create a role “pgbouncer” with a password:

postgres=# create role pgbouncer with login password 'secret';
CREATE ROLE
postgres=# SELECT passwd FROM pg_shadow WHERE usename = 'pgbouncer';
               passwd                
-------------------------------------
 md509d12ff67352814e4c467c7f55a3a1d7
(1 row)

Then configure userlist.txt from pgbouncer with this account

"pgbouncer" "md509d12ff67352814e4c467c7f55a3a1d7"

Then create the user lookup function in the template1 database (so it will be copied uppon new database creations). You will also need to copy this to all existing databases which will be used through pgbouncer:

cat << 'EOF' | psql -d template1
CREATE SCHEMA pgbouncer;
REVOKE ALL ON SCHEMA pgbouncer FROM public, pgbouncer;
GRANT USAGE ON SCHEMA pgbouncer TO pgbouncer;
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;
EOF

Then configure pgbouncer auth_user and auth_query

[databases]
# You will need max_db_connections=1 on template1 database, otherwise multiple connections to the template1 database will not allow running "createdb" through pgbouncer
# Also for unknown reason, not repeating auth_user here don't work
template1 = auth_user=pgbouncer max_db_connections=1
# connect all other database through unix socket
* =                    
 
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer
auth_query = SELECT * FROM pgbouncer.user_lookup($1);
admin_users = pgbouncer
stats_users = pgbouncer
pool_mode = transaction
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20
verbose = 0

Now you should be able to pass all connections through pgbouncer.

Tests:

postgres=# create role foo with login createdb password 'foo';
CREATE ROLE
postgres=# create role bar with login password 'bar';
CREATE ROLE

% PGPASSWORD=foo psql -p 6432 -U foo -d template1 -c 'select current_user;'
 current_user 
--------------
 foo
(1 row)

% PGPASSWORD=wrong psql -p 6432 -U foo -d template1 -c 'select current_user;'
psql: ERROR:  auth failed

% PGPASSWORD=bar createdb -p 6432 -U bar bar
createdb: database creation failed: ERROR:  permission denied to create database

% PGPASSWORD=foo createdb -p 6432 -U foo foo

% PGPASSWORD=foo psql -p 6432 -U foo -d foo -c 'select current_user;'
 current_user 
--------------
 foo
(1 row)

% PGPASSWORD=secret psql -p 6432 -U pgbouncer -d pgbouncer -c 'show databases'
   name    | host | port | database  | force_user | pool_size | reserve_pool | pool_mode | max_connections | current_connections | paused | disabled 
-----------+------+------+-----------+------------+-----------+--------------+-----------+-----------------+---------------------+--------+----------
 pgbouncer |      | 6432 | pgbouncer | pgbouncer  |         2 |            0 | statement |               0 |                   0 |      0 |        0
 template1 |      | 5432 | template1 |            |        20 |            0 |           |               1 |                   0 |      0 |        0
(2 rows)