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.
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 |
[databases]<br><br>db1 = host=127.0.0.1 port=5432 dbname=db1 auth_user=testusr<br><br>[pgbouncer]<br>auth_type = trust<br>auth_file = /etc/pgbouncer/userlist.txt |
Add an empty password in userlist.txt; otherwise, the pgbouncer gives this error: ERROR broken auth file:
|
1 |
$ cat /etc/pgbouncer/userlist.txt<br>"testusr" "" |
PostgreSQL pg_hba.conf:
|
1 |
host all testusr 127.0.0.1/32 trust |
|
1 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr -p 6432<br>psql (16.6 - Percona Distribution)<br>Type "help" for help.<br><br>db1=> |
As we can see, testusr with auth_type trust can log in via pgbpuncer without a password.
pgbouncer log:
|
1 |
2025-03-03 12:30:45.327 UTC [38708] LOG listening on 0.0.0.0:6432<br>2025-03-03 12:30:45.327 UTC [38708] LOG listening on [::]:6432<br>2025-03-03 12:30:45.327 UTC [38708] LOG listening on unix:/tmp/.s.PGSQL.6432<br>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<br>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<br>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)<br>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) |
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 |
[databases]<br>* = host=127.0.0.1 port=5432<br><br>[pgbouncer]<br>auth_type = hba<br>auth_file = /etc/pgbouncer/userlist.txt<br>auth_hba_file = /etc/pgbouncer/pg_hba.conf |
userlist.txt:
|
1 |
[postgres@node1 ~]$ cat /etc/pgbouncer/userlist.txt<br>"testusrmd5" "md558a40daa68ecb57fcd68add9a89edd31"<br>"testusr1" "SCRAM-SHA-256$4096:BE6bepNUKDkX3sKlcN2KJQ==$QDYCNTGWzWcn1HVH04TE/ng8fFxKPaqqILO+WwLmDHI=:pbOEpFpqhdlOlrNCTZdlv2kP4jUxBz6JmYyUNtPBgwM="<br>"testusr" " " |
pg_hba.conf
|
1 |
[postgres@node1 ~]$ cat /etc/pgbouncer/pg_hba.conf:<br># TYPE DATABASE USER ADDRESS METHOD<br><br>host all testusr 127.0.0.1/32 trust<br>host all testusrmd5 127.0.0.1/32 md5<br>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 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr -p 6432<br>psql (16.6 - Percona Distribution)<br>Type "help" for help.<br><br>db1=> q |
pgbouncer log:
|
1 |
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<br>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)<br>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<br>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 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusrmd5 -p 6432<br>Password for user testusrmd5:<br>psql (16.6 - Percona Distribution)<br>Type "help" for help.<br><br>db1=> q |
|
1 |
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<br>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<br>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 |
[postgres@node1 ~]$ psql -h 127.0.0.1 -d db1 -U testusr1 -p 6432<br>Password for user testusr1:<br>psql (16.6 - Percona Distribution)<br>Type "help" for help.<br><br>db1=> q |
|
1 |
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<br>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<br>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) |
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 |
[databases]<br>* = host=127.0.0.1 port=5432 auth_user=pgbounceauth<br><br>[pgbouncer]<br>auth_type = hba<br>auth_file = /etc/pgbouncer/userlist.txt<br>auth_hba_file = /etc/pgbouncer/pg_hba.conf<br><br>auth_user = pgbounceauth<br>auth_query = SELECT pgbounce_user, pgbounce_password FROM bounce.lookup($1) |
userlist.txt:
|
1 |
[postgres@node1 ~]$ cat /etc/pgbouncer/userlist.txt<br>"pgbounceauth" "md5f9fc3711c004565c6f120ad0ef59738d" |
Add the pgbounceauth user entry in pg_hba.conf:
|
1 |
host all testusr 127.0.0.1/32 trust<br>host all testusrmd5 127.0.0.1/32 md5<br>host all testusr1 127.0.0.1/32 scram-sha-25<br>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.
Resources
RELATED POSTS
