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 by maintaining its own directory of users and passwords which sometimes is a source of confusion, so in this article, we try to describe how this works.

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, for example:

auth_file = /etc/pgbouncer/userlist.txt

This file has the following format:

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

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

Besides the auth_file, we can configure authentication with 2 other settings:

auth_type = hba
auth_hba_file = /etc/pgbouncer/hba.conf

Using hba for the auth_type means that authentication type will depend on the configure read in from the auth_hba_file. The format of the auth_hba_file follows the format of the PostgreSQL pg_hba.conf file. For example, with the expectation to use md5 authentication:

# TYPE DATABASE USER ADDRESS METHOD
local all all md5
host all all 0.0.0.0/0 md5
host all all ::/0 md5

The password used in auth_file can either be in clear text or the md5 version that is stored in the pg_shadow database table. We recommend using the md5 password, which can be retrieved by running the following query:

SELECT passwd
FROM pg_shadow
WHERE usename = 'myuser';

Then this information can be saved in the auth_file:

"pgbouncer" "md5..."

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, for example:

auth_user = pgbouncer

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.

The default value of auth_query is:

auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1

Only privileged users can query the pg_shadow table so a function is needed such that a non-superuser role (for example called pgbouncer) can use to validate passwords. This functions needs to be created in every database that the user pgbouncer will be connecting to:

CREATE OR REPLACE FUNCTION shadow(uname TEXT)
RETURNS table(username NAME, passwd TEXT) AS
$$
SELECT usename, passwd
FROM pg_shadow
WHERE usename = $1
$$
LANGUAGE SQL SECURITY DEFINER;

Then the auth_query parameter needs to be set to to use this function to verify passwords:

auth_query = SELECT * FROM shadow($1)

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.

Finally, if you use PAM by setting:

auth_type = pam

Then the system works differently: auth_file and auth_user are ignored and all the user and password information comes from the PAM system.

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 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.