Linux x86-64 (RHEL 8) 13 SQL Profiler
Richard Yen Principal Support Engineer Nov 6, 2019
Anyone running a database in a production environment with over a hundred users should seriously consider employing a connection pooler to keep resource usage under control. PgBouncer is one such tool, and it’s great because it’s lightweight and yet has a handful of nifty features for DBAs that have very specific needs.
One of these nifty features that I want to share about is the
auth_query combo that serves as an alternative to the default authentication process that uses
userlist.txt “What’s wrong with
userlist.txt” you may ask. For starters, it makes user/role administration a little tricky. Every time you add a new user to PG, you need to add it to
userlist.txt in PgBouncer. And every time you change a password, you have to change it in
userlist.txt as well. Multiply that by the 30+ servers you’re managing, and you’ve got a sysadmin’s nightmare on your hands. With
auth_query, you can centralize the password management and take one item off your checklist.
[databases] section of your
pgbouncer.ini, you would typically specify a
password= with which PgBouncer will connect to the Postgres database with. If left blank, the user/password are declared at the connection string (i.e.,
psql -U <username> <database>). When this happens, PgBouncer will perform a lookup of the provided username/password against
userlist.txt to verify that the credentials are correct, and then the username/password are sent to Postgres for an actual database login.
auth_user is provided, PgBouncer will still read in credentials from the connection string, but instead of comparing against
userlist.txt, it logs in to Postgres with the specified
auth_user(preferably a non-superuser) and runs
auth_query to pull the corresponding md5 password hash for the desired user. The validation is performed at this point, and if correct, the specified user is allowed to log in.
Assuming Postgres is installed and running, you can get the
auth_query combo running with the following steps:
- Create a Postgres user to use as
- Create the user/password lookup function in Postgres
1. Create a Postgres user to use as
In your terminal, run
psql -c "CREATE ROLE myauthuser WITH PASSWORD 'abc123'" to create
myauthuser. Note that
myauthuser should be an unprivileged user, wiht no
GRANTs to read/write any tables.
myauthuser is used strictly for assisting with PgBouncer authentication.
For the purposes of this example, we’ll also have a database user called
mydbuser, which can be created with:
2. Create the user/password lookup function in Postgres
psql prompt, create a function that will be used by
myauthuser to perform the user/password lookup:
As mentioned in the documentation, the
SECURITY DEFINER clause enables the non-privileged
myauthuser to view the contents of
pg_shadow, which would otherwise be limited to only admin users.
[databases] section with an alias, like:
auth_query in the
[pgbouncer] section with:
4. Let ‘er rip!
Spin up PgBouncer and try logging in:
As you can see, providing the wrong password for
mydbuser led to a
pg_shadow lookup failure and the user was prevented from logging in. The subsequent
psql call used the correct password and successfully logged in.
I’ve seen a few customers try to implement this, and one of the common mistakes I’ve seen is the failure to set
pg_hba.conf properly in Postgres. Bear in mind that once the provided credentials are validated, PgBouncer will attempt to log in with the specified user. Therefore, if your
myauthuser and you’ve got a
host all myauthuser 127.0.0.1/32 md5, but you want to ultimately login with
mydbuser, you won’t be able to do so because there’s no
pg_hba.conf entry for
mydbuser, and you’ll probably see something like this:
Also, make sure
auth_type is not set to
pgbouncer.ini – instead, you should set
auth_user and clamp it down to only the IP(s) that will be running PgBouncer. Set
md5 so that your login attempt will be challenged with a password request.