Understanding user management in PgBouncer

PgBouncer is a popular connection proxy and pooler for PostgreSQL. As PgBouncer presents a PostgreSQL protocol interface to client applications, it also handles client authentication. For that, it maintains its own directory of users and passwords. That is sometimes a source of confusion, so in this blog post I want to try to describe how this works.

auth_file

There are several ways in which users and passwords can be defined in PgBouncer. Let’s start with the simplest. You can define users and passwords in a text file. The location of this file is set by the configuration parameter auth_file. It is typically named userlist.txt, but you can use any name of course. This file has the following format:

"username1" "password1"
"username2" "password2"
...

This file essentially fulfills the same purpose as the system catalog pg_authid (formerly pg_shadow (formerly pg_user)) in the PostgreSQL backend. In fact, PostgreSQL used to keep its own user database in a file like this in the data directory, and the original intent of this option was that you could point PgBouncer directly at that file, to share users directly between PostgreSQL and PgBouncer. But this no longer works, because PostgreSQL no longer works that way, so for all intents and purposes this is now a textual configuration file. One way to keep this keep this file in sync with the users defined in PostgreSQL is to query pg_authid and dump the results into this file in the appropriate format. The script mkauth.py that comes with PgBouncer can be used for that.

The passwords in this file may be plain-text passwords or passwords encrypted using MD5 or SCRAM, depending on the authentication method to be used. The details of these authentication methods are not covered here. That said, if you want to store encrypted passwords, it is probably useful to decide on a way to maintain this information in an automatic or scripted way and not edit it by hand.

Note that the access permissions of this file should be restricted, typically to 0600 mode.

auth_query, auth_user

Another way to define users is to have PgBouncer query the PostgreSQL backend directly when needed. This is set by the configuration parameter auth_user, which can be set either globally or per database. When this is set, PgBouncer uses that user to connect to the PostgreSQL backend and runs the query defined by the setting auth_query to look up a user and password. If the auth_user itself needs a password for that connection, it needs to be set in userlist.txt. But you might also set it up that auth_user doesn’t need a password, perhaps using TLS certificates. (For more information on setting up TLS with PgBouncer, see this webinar.)

The default value of auth_query is

SELECT usename, passwd FROM pg_shadow WHERE usename=$1

This means that the auth_user needs to be a superuser on the PostgreSQL side. For example, you could do

[databases]
mydata = host=myhost dbname=mydata auth_user=postgres

However, it’s preferable to write SECURITY DEFINER function in the backend that can look up users and is callable only by a specific user. This blog post has an extensive discussion about how to set that up properly.

Also note that with auth_user/auth_query you can have different users and passwords per database, whereas auth_file defines users for the entire PgBouncer instance.

PAM

Finally, if you use PAM by setting auth_type = pam, then the system works differently. auth_file and auth_user are ignored. All the user and password information comes from the PAM system.

Forced users

Note that in each case, the user and password information maintained by PgBouncer is used both for authenticating connections coming in from the client as well as supplying authentication information for connections going out to the server. This is a fundamental design decision: PgBouncer wants to be a more or less transparent connection proxy as far as authentication is concerned. If you want to log in to PgBouncer with different user names or passwords than are used for the PostgreSQL backend, this will be more complicated to set up and maintain.

There is a way to assign a specific user in a database definition, like

[databases]
mydata = host=myhost dbname=mydata user=foo password=secret

This is internally called a “forced user”. This user exists only for outgoing connections to the backend server and does not really exist otherwise in the PgBouncer user database.

Which to use?

Which one of these you use depends on various considerations about your whole setup. If your PostgreSQL server itself uses PAM or perhaps LDAP for authentication, then using PAM in PgBouncer also makes sense. (PgBouncer does not support a native LDAP authentication method, but LDAP can be used via PAM.) Otherwise, auth_user/auth_query is more elegant and requires less maintenance in the long run, but it requires a more careful setup initially. auth_file is the more traditional setup that requires more maintenance if users and passwords change a lot, but it’s simpler and probably more robust and less surprising.