CYBERTEC PostgreSQL Logo

pgbouncer authentication made easy

01.2019 / Category: / Tags: |
pgBouncer authentication can be like a bouncer at a discotheque who has to ask for the password himself
© Laurenz Albe 2023

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


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

6 responses to “pgbouncer authentication made easy”

  1. 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.conf file in pgbouncer if you want to allow or reject connections based on their client IP address.

      A simple pg_hba.conf can 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.

  2. 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 = hba should not require trust authentication on the PostgreSQL server.

      Specify an auth_user, auth_file and auth_query as you would with auth_type = md5.

      The pg_hba.conf file 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 ?

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram