pgbouncer authentication made easy

pgbouncer is the most widely used connection pooler for PostgreSQL.
This blog will provide a simple cookbook 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:

"laurenz" "md565b6fad0e85688f3f101065bc39552df"
"postgres" "md553f48b7c4b76a86ce72276c5755f217d"

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 Files\PostgreSQL\11\bin\psql").
• 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:

psql -Atq -U postgres -d postgres -c "SELECT concat('\"', usename, '\" \"', passwd, '\"') FROM pg_shadow"

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

[pgbouncer]
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt/userlist.txt

Done!

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:

CREATE ROLE pgbouncer LOGIN;
-- set a password for the user
\password pgbouncer

CREATE FUNCTION public.lookup (
   INOUT p_user     name,
   OUT   p_password text
) RETURNS record
   LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;

-- make sure only "pgbouncer" can use the function
REVOKE EXECUTE ON FUNCTION public.lookup(name) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.lookup(name) TO pgbouncer;

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:

[pgbouncer]
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
auth_user = pgbouncer
auth_query = SELECT p_user, p_password FROM public.lookup($1)

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:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    mydatabase      appuser         72.32.157.230/32        md5
host    mydatabase      appuser         217.196.149.50/32       md5

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

[pgbouncer]
auth_type = hba
auth_hba_file = /etc/pgbouncer/pg_hba.conf
auth_file = /etc/pgbouncer/userlist.txt

You can of course also use auth_query in that case.