Table of Contents
UPDATED August 2023
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.
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.
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:
|
1 2 |
'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
psqlPATH, you'll have to use the absolute path (something like /usr/pgsql-11/bin/psql or "C:Program FilesPostgreSQL11binpsql").root user or administratorThen you can simply create the file like this:
|
1 |
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:
|
1 2 3 |
[pgbouncer] auth_type = md5 auth_file = /etc/pgbouncer/userlist.txt/userlist.txt |
Done!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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:
|
1 2 3 4 5 |
[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.
pg_hba.confYou 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:
|
1 2 3 |
# 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:
|
1 2 3 4 |
[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.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
Hi,,
I have pgbouncer and pg in seperate servers, when I config pgbouncer in advanced authentication mode (auth_type = hba)
I have to configure pg server pg_hba.conf authentication method as "trust" to allow pgbouncer side connections ( host all all pgbouncer_server_IP trust ), otherwise I get auth error.
Am I have to do it in pg pg_hba.conf ?
Is there another way to configure both pg_hba as md5 auth method ?
Choosing
auth_type = hbashould not requiretrustauthentication on the PostgreSQL server.Specify an
auth_user,auth_fileandauth_queryas you would withauth_type = md5.The
pg_hba.conffile only determines how clients have to authenticate to pgBouncer.Thanks for reply,
but in auth_type = hba mode and auth_user=pgbouncer, pgbouncer require "username" "hash" all users in userlist.txt, I don't get it why ?
Is it likely a bug ?
your query 'psql -Atq -U postgres -d postgres -c ‘SELECT concat($$”$$, usename, $$” “$$, passwd, $$”$$) FROM pg_shadow’ > /etc/pgbouncer/userlist.txt' looks like it ended up pasted by accident in the hba.conf section.
Also, you'll need to create the pgbouncer function in *every* database.
Do you suggest moving lines from the PG pg_hba.conf to the bouncer pg_hba.conf? Im trying to understand why you'd bother w/ a bouncer hba and not just rely on the PG hba.
Thanks for the review; I have removed the accidental paste and added your remark.
To your question:
Since the connections to PostgreSQL now all come from pgbouncer, you need to have a
pg_hba.conffile in pgbouncer if you want to allow or reject connections based on their client IP address.A simple
pg_hba.confcan just be copied from PostgreSQL and used with pgbouncer, but pgbouncer does not support all the authentication methods that PostgreSQL knows, so you might need to adapt the file.