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?
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
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
If it is not on your
PATH, you’ll have to use the absolute path (something like
- write access to the pgbouncer configuration file, which may require that you run this as
rootuser 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
[pgbouncer] auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt/userlist.txt
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.
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
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.
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
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 22.214.171.124/32 md5 host mydatabase appuser 126.96.36.199/32 md5
If the file is called
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.