How we manage connection permissions from pgBouncer to PostgreSQL’s

Our company actively uses pgBouncer. We almost always recommend its use to our customers. This is a really cool connection pooling solution for PostgreSQL. In addition, pgBouncer allows as to flexibly manage connections for different PostgreSQL instances, helps to perform database maintenance without downtime and much more. In this article I want to talk about what problem we recently encountered and how we solved it. It's about connections permissions. 

There are several mechanisms in pgBouncer allow you to manage connections permissions. For example, there is pg_hba.conf file, similar to  pg_hba.conf  in PostgreSQL. You can set allowed addresses for connection using the listen_addr parameter in the same way as in PostgreSQL. This provides an additional layer of control over connections.

PgBouncer uses userlist.txt file to authenticate users. There are two approaches to working with this file. The most common is "use auth_user with auth_query if user not present in  auth_file" as pgBouncer documentation says. 

Authentication file userlist.txt stores username and password hash for auth_user only. auth_user connects to database, reads pg_shadow table and checks username/password. Therefore, pgBouncer does not need to store a list of users. It is very comfortable. But there is a problem with this approach. 

For example, suppose we have two servers with physical replication and we have 50 applications. 

Each application has its own user. 10 applications can connect to primary and standby and 40 only to standby. To implement access control we can create two user groups "read_write" and "read_only". The first role has access to both servers, the second only to standby. 
Add to pg_hba.conf file on the primary: 

TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all        +read_write        10.0.0.0/8               md5

and on the standby:

TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all        +read_write        10.0.0.0/8               md5
host    all        +read_only         10.0.0.0/8               md5

After that, we can add 10 roles to "read_write" group and other 40 roles to "read_only" group. But... If we use the approach with auth_user, then we will not be able to configure connection rules using pg_hba.conf in PostgreSQL. This is due to fact that connections from pgBouncer to Postgres always occur through auth_user. The only way is to explicitly specify all roles in userlist.txt and not use authentication with auth_user/auth_query. But then at any change of roles in database you need to edit userlist.txt file. Not very convenient, right? I will describe below the way to overcome this issue we use sometimes.  

PgBouncer has a script developed by Marko Kreen(author of pgBouncer). This script is written in python and allows to automatically create userlist.txt file.
We modified it a bit by changing query to pg_shadow table, cause we couldn't use the role with superuser permissions for security reasons. Python script example below:

#! /usr/bin/env python
 
import sys, os, tempfile, psycopg2
 
if len(sys.argv) != 3:
print 'usage: mkauth DSTFN CONNSTR'
sys.exit(1)
 
# read old file
fn = sys.argv[1]
try:
old = open(fn, 'r').read()
except IOError:
old = ''
 
# create new file data
db = psycopg2.connect(sys.argv[2])
curs = db.cursor()
curs.execute("select usename, passwd from get_pg_shadow() order by 1")
lines = []
for user, psw in curs.fetchall():
user = user.replace('"', '""')
if not psw: psw = ''
psw = psw.replace('"', '""')
lines.append('"%s" "%s"\n' % (user, psw))
db.commit()
cur = "".join(lines)
 
# if changed, replace data securely
if old != cur:
fd, tmpfn = tempfile.mkstemp(dir = os.path.split(fn)[0])
f = os.fdopen(fd, 'w')
f.write(cur)
f.close()
os.rename(tmpfn, fn) 

Script is calling get_pg_shadow() function described later.

Then we created "pgbouncer" role in database: 

CREATE ROLE pgbouncer LOGIN PASSWORD 'password'; 

After that, allowed "pgbouncer" role to connect to database:

TYPE  DATABASE        USER            ADDRESS                 METHOD 
host    postgres       pgbouncer      10.0.0.1/32              md5 

Created ~/.pgpass file for root user like:

10.0.0.1:5432:postgres:pgbouncer:password 

And created get_pg_shadow() function in postgres DB:

 CREATE OR REPLACE FUNCTION public.get_pg_shadow()
    RETURNS TABLE (usename text, passwd text)
    LANGUAGE sql 
    SECURITY DEFINER 
    SET search_path = pg_catalog AS
       $$SELECT usename, passwd 
         FROM pg_shadow s 
         WHERE 
            NOT (s.userepl and s.usesuper = false) 
            AND (s.valuntil > CURRENT_TIMESTAMP OR s.valuntil IS NULL);$$;

 ALTER FUNCTION  public.get_pg_shadow() OWNER TO postgres;
 REVOKE EXECUTE ON FUNCTION public.get_pg_shadow() FROM PUBLIC;
 GRANT EXECUTE ON FUNCTION public.get_pg_shadow() TO pgbouncer; 

We created a SECURITY DEFINER function to provide pgBouncer access to pg_shadow table.
SECURITY DEFINER function is executed with the privileges of the user that owns it. Therefore, we declare "postgres" user as owner of function and give "pgbouncer" user permissions to execute function. In addition, we revoke  permissions to execute function for all other users.

Now we needed to determine how to update "userlist.txt" file. The easiest way was to use crontab and run file generation per minute for example. But this method would cause a large number of unnecessary database queries.

Therefore, we thought about the other way.

We implemented the idea by Hubert Lubaczewski (depesz). The idea was to monitor changes with incron in authentication file in postgresql directory - pg_authid and automatically update userlist.txt file using mkauth.py script. Let's check where pg_authid file is located:

$ SELECT pg_relation_filepath('pg_authid');  
pg_relation_filepath 
----------------------  
global/17906516
(1 ROW) 

But since the file name could be changed, we were forced to monitor "/pg_globals" directory. In our case, pg_globals was located at: 

 /var/lib/pgsql/12/data/global

Added this directory to incron command:

 /var/lib/pgsql/12/data/global/ IN_MODIFY /etc/pgbouncer/mkauth.py "/etc/pgbouncer/userlist.txt" "dbname='postgres' user='pgbouncer' host='10.0.0.1'" 

It means: on MODIFICATION of any file in /var/lib/pgsql/12/data/global directory, run /etc/pgbouncer/mkauth.py script. 
Then we just run incrontab:

 sudo incrond 

Done!
The only thing to remember is to run CHECKPOINT command after creating/editing/deleting a user, because Postgres has to flush changes to disk.

Conclusion

Perhaps there is a more elegant solution for this problem... But how do you manage/secure your connections in pgBouncer? Feel free to share your solutions in the comments! Thanks!