PgBouncer is a lightweight external connection pooler that can be introduced between an application and a PostgreSQL database. It manages its own user authentication and has its own database for users, and uses auth_type options to authenticate users.
This blog post explains configuring PgBouncer auth_type trust and hba use cases with configuration examples and known issues.
Process:
auth_type = trust
With auth_type = trust, authentication is not done. The username must still exist in the auth_file.
Using auth_type = trust for the user via pgbouncer.
pgbouncer.ini:
|
1 2 3 4 5 6 7 |
[databases] db1 = host=127.0.0.1 port=5432 dbname=db1 auth_user=testusr [pgbouncer] auth_type = trust auth_file = /etc/pgbouncer/userlist.txt |
Add an empty password in userlist.txt; otherwise, the pgbouncer gives this error: ERROR broken auth file:
|
1 2 |
$ cat /etc/pgbouncer/userlist.txt "testusr" "" |
PostgreSQL pg_hba.conf:
|
1 |
host all testusr 127.0.0.1/32 trust |
|
1 2 3 4 5 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr -p 6432 psql (16.6 - Percona Distribution) Type "help" for help. db1=> |
As we can see, testusr with auth_type trust can log in via pgbpuncer without a password.
pgbouncer log:
|
1 2 3 4 5 6 7 |
2025-03-03 12:30:45.327 UTC [38708] LOG listening on 0.0.0.0:6432 2025-03-03 12:30:45.327 UTC [38708] LOG listening on [::]:6432 2025-03-03 12:30:45.327 UTC [38708] LOG listening on unix:/tmp/.s.PGSQL.6432 2025-03-03 12:30:45.327 UTC [38708] LOG process up: PgBouncer 1.24.0, libevent 2.1.8-stable (epoll), adns: evdns2, tls: OpenSSL 1.1.1k FIPS 25 Mar 2021 2025-03-03 12:30:51.061 UTC [38708] LOG C-0x55f3a5deff00: db1/testusr@127.0.0.1:47866 login attempt: db=db1 user=testusr tls=no replication=no 2025-03-03 12:30:51.061 UTC [38708] LOG S-0x55f3a5df8960: db1/testusr@127.0.0.1:5432 new connection to server (from 127.0.0.1:38880) 2025-03-03 12:31:25.635 UTC [38708] LOG C-0x55f3a5deff00: db1/testusr@127.0.0.1:47866 closing because: client close request (age=34s) |
auth_type=hba
This loads the actual authentication type from the auth_hba_file. This allows different authentication methods for different access paths, such as trust, md5, scram-sha-25, etc.
Example:
pgbouncer.ini:
|
1 2 3 4 5 6 7 |
[databases] * = host=127.0.0.1 port=5432 [pgbouncer] auth_type = hba auth_file = /etc/pgbouncer/userlist.txt auth_hba_file = /etc/pgbouncer/pg_hba.conf |
userlist.txt:
|
1 2 3 4 |
[postgres@node1 ~]$ cat /etc/pgbouncer/userlist.txt "testusrmd5" "md558a40daa68ecb57fcd68add9a89edd31" "testusr1" "SCRAM-SHA-256$4096:BE6bepNUKDkX3sKlcN2KJQ==$QDYCNTGWzWcn1HVH04TE/ng8fFxKPaqqILO+WwLmDHI=:pbOEpFpqhdlOlrNCTZdlv2kP4jUxBz6JmYyUNtPBgwM=" "testusr" " " |
pg_hba.conf
|
1 2 3 4 5 6 |
[postgres@node1 ~]$ cat /etc/pgbouncer/pg_hba.conf: # TYPE DATABASE USER ADDRESS METHOD host all testusr 127.0.0.1/32 trust host all testusrmd5 127.0.0.1/32 md5 host all testusr1 127.0.0.1/32 scram-sha-25 |
Login tests from pgbouncer for trust, md5, scram-sha-25 auth method users:
trust:
|
1 2 3 4 5 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr -p 6432 psql (16.6 - Percona Distribution) Type "help" for help. db1=> q |
pgbouncer log:
|
1 2 3 4 |
2025-03-04 11:03:03.504 UTC [40937] LOG C-0x5556f801cd50: db1/testusr@127.0.0.1:37724 login attempt: db=db1 user=testusr tls=no replication=no 2025-03-04 11:03:05.181 UTC [40937] LOG C-0x5556f801cd50: db1/testusr@127.0.0.1:37724 closing because: client close request (age=1s) 2025-03-04 11:03:35.902 UTC [40937] LOG C-0x5556f801cd50: db1/testusr@127.0.0.1:48486 login attempt: db=db1 user=testusr tls=no replication=no 2025-03-04 11:03:38.794 UTC [40937] LOG C-0x5556f801cd50: db1/testusr@127.0.0.1:48486 closing because: client close request (age=2s) |
md5:
|
1 2 3 4 5 6 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusrmd5 -p 6432 Password for user testusrmd5: psql (16.6 - Percona Distribution) Type "help" for help. db1=> q |
|
1 2 3 |
2025-03-04 11:04:32.839 UTC [40937] LOG C-0x5556f801cd50: db1/testusrmd5@127.0.0.1:43076 login attempt: db=db1 user=testusrmd5 tls=no replication=no 2025-03-04 11:04:35.183 UTC [40937] LOG C-0x5556f801cd50: db1/testusrmd5@127.0.0.1:43086 login attempt: db=db1 user=testusrmd5 tls=no replication=no 2025-03-04 11:04:38.218 UTC [40937] LOG C-0x5556f801cd50: db1/testusrmd5@127.0.0.1:43086 closing because: client close request (age=3s) |
scram-sha-25:
|
1 2 3 4 5 6 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr1 -p 6432 Password for user testusr1: psql (16.6 - Percona Distribution) Type "help" for help. db1=> q |
|
1 2 3 |
2025-03-04 11:05:03.367 UTC [40937] LOG C-0x5556f801cd50: db1/testusr1@127.0.0.1:56962 login attempt: db=db1 user=testusr1 tls=no replication=no 2025-03-04 11:05:05.758 UTC [40937] LOG C-0x5556f801cd50: db1/testusr1@127.0.0.1:56972 login attempt: db=db1 user=testusr1 tls=no replication=no 2025-03-04 11:05:11.890 UTC [40937] LOG C-0x5556f801cd50: db1/testusr1@127.0.0.1:56972 closing because: client close request (age=6s) |
auth_query method
If you do not want to mention all users in the auth_file, you can configure pgbouncer with auth_query.
We suggest using the auth_query method. This method minimizes the need to store password secrets outside the database, allowing you to use pgbouncer as a connection pooler while ensuring the maximum possible security offered by the password authentication scheme.
Let us consider another example.
pgbouncer.ini:
|
1 2 3 4 5 6 7 8 9 10 |
[databases] * = host=127.0.0.1 port=5432 auth_user=pgbounceauth [pgbouncer] auth_type = hba auth_file = /etc/pgbouncer/userlist.txt auth_hba_file = /etc/pgbouncer/pg_hba.conf auth_user = pgbounceauth auth_query = SELECT pgbounce_user, pgbounce_password FROM bounce.lookup($1) |
userlist.txt:
|
1 2 |
[postgres@node1 ~]$ cat /etc/pgbouncer/userlist.txt "pgbounceauth" "md5f9fc3711c004565c6f120ad0ef59738d" |
Add the pgbounceauth user entry in pg_hba.conf:
|
1 2 3 4 |
host all testusr 127.0.0.1/32 trust host all testusrmd5 127.0.0.1/32 md5 host all testusr1 127.0.0.1/32 scram-sha-25 host all pgbounceauth 127.0.0.1/32 md5 |
For auth_type=hba when using PAM authentication method in pg_hba.conf for a user, it has some issues as reported here: https://github.com/pgbouncer/pgbouncer/issues/1253, https://github.com/pgbouncer/pgbouncer/pull/1291.
Further reading
About the Author
Lalit ChoudharyLalit works as a Database Engineer in Percona. His main professional interests are problem-solving, working on database issues and testing.
Hi,
and thank you for your contributed article.
My concerns is about end-to-end SCRAM auth.
I manage to connect to a postgresql backend with scram, but only if my frontend user in pgbouncer .ini and userlist uses md5 password.
I tried the auth_type=hba configuration, granting access with a SCRAM auth, but with no success, receiving both :FATAL: server login failed: wrong password type
FATAL: SSL required
Also, please note that my pg_bouncer is on a host different of both the client and the database.
Any idea on how best to setup end-to-end SCRAM, or shall I resort to either md5 clear password on pg bouncer, or using auth query?
Best regards,
Mathieu