pgbouncer authentication made easy

UPDATED August 2024
pgbouncer is the most widely used connection pooler for PostgreSQL.
This blog will provide a simple cookbook recipe for how to configure user authentication with pgbouncer.

I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site.
But it should work pretty similarly anywhere.

What is a connection pooler?

Setting max_connections to a high value can impact performace and can even bring your database to its knees if all these connections become active at the same time.

Also, if database connections are short-lived, a substantial amount of your database resources can be wasted just opening database connections.

To mitigate these two problems, we need a connection pooler. A connection pooler is a proxy between the client and the database: clients connect to the connection pooler, which handles the SQL requests via a relatively stable set of persistent database connections (the “connection pool”).

Since clients connect to pgbouncer, it will have to be able to authenticate them, so we have to configure it accordingly.

The very simple method (authentication file)

This method is useful if the number of database users is small and passwords don't change frequently.

For that, we create a configuration file userlist.txt in the pgbouncer configuration directory (on my system /etc/pgbouncer).
The file contains the database users and their passwords, so that pgbouncer can authenticate the client without resorting to the database server.
It looks like this:

You can write the file by hand using the information from the pg_shadow catalog table, or you can create it automatically.
For that to work, you need

• the PostgreSQL command line client psql
  If it is not on your PATH, you'll have to use the absolute path (something like /usr/pgsql-11/bin/psql or "C:Program FilesPostgreSQL11binpsql").
• write access to the pgbouncer configuration file, which may require that you run this as root user or administrator

Then you can simply create the file like this:

Once you have created userlist.txt, add the following to the [pgbouncer] section of /etc/pgbouncer/pgbouncer.ini:

Done!

Note: the above example uses md5 authentication, which is pretty much obsolete now. Instead, you should be using scram-sha-256. For that to work, you have to use a password hashed with scram-sha-256 in userlist.txt. You cannot use the trick above to generate these hashes. Instead, query the catalog table pg_authid for the actual password hashes. See my article about moving from md5 authentication to scram-sha-256 for more information.

The slightly more complicated method (querying the database)

If users and passwords change frequently, it would be annoying to have to change the user list all the time.
In that case it is better to use an “authentication user” that can connect to the database and get the password from there.

You don't want everyone to see your database password, so we give access to the passwords only to this authentication user.
Using psql, we connect to the database as superuser and run the following:

pg_shadow is only accessible to superusers, so we create a SECURITY DEFINER function to give pgbouncer access to the passwords.

Then we have to create a userlist.txt file as before, but it will only contain a single line for user pgbouncer.

The configuration file in /etc/pgbouncer/pgbouncer.ini should look like this:

Now whenever you authenticate as a user other than pgbouncer, the database will be queried for the current password of that user.

Since the auth_query connection will be made to the destination database, you need to add the function to each database that you want to access with pgbouncer.

Advanced authentication with pg_hba.conf

You can determine which connections pgbouncer will accept and reject using a pg_hba.conf file like in PostgreSQL, although pgbouncer only accepts a subset of the authentication methods provided by PostgreSQL.

To allow connections only from two application servers, the file could look like this:

If the file is called /etc/pgbouncer/pg_hba.conf, your pgbouncer.ini would look like this:

You can of course also use auth_query in that case.

Further References

• pgbouncer: Types of PostgreSQL Connection Pooling by Hans-Jürgen Schönig
• TCP Keepalive for a Better PostgreSQL Experience

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.