How configure the pgbouncer wth hba auth method

PostgreSQL
1

Hi All,

Can you please help me how to configure the pgboucner with hba auth method and without the /etc/pgbouncer/userlist.txt file ?

We don’t want to store passwords in the file, so without storing passwords, we would like to use hba auth method.

2

Can someone help me on this?

3

Hello @Naresh9999

Can you please help me how to configure the pgboucner with hba auth method and without the /etc/pgbouncer/userlist.txt file ?

We don’t want to store passwords in the file, so without storing passwords, we would like to use hba auth method.

For this, you have to use the auth_query method with at least one user (auth_user) entry in the userlist.txt file.

Here is an example:

pgbouncer.ini

[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

$ cat /etc/pgbouncer/userlist.txt 
pgbounceauth" "md5f9fc3711c004565c6f120ad0ef59738d"

Add the pgbounceauth user entry in pg_hba.conf

/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
host    all             pgbounceauth    127.0.0.1/32             md5

Login tests from pgbouncer for trust , md5, scram-sha-25 auth method users.

[postgres@node1 ~]$ psql -h 127.0.0.1 -U testusr1 -d db1 -p 6432
Password for user testusr1: 
psql (16.6 - Percona Distribution)
Type "help" for help.

db1=> exit
[postgres@node1 ~]$ psql -h 127.0.0.1 -U testusrmd5 -d db1 -p 6432
Password for user testusrmd5: 
psql (16.6 - Percona Distribution)
Type "help" for help.

db1=> 

pgbouncer logs:
2025-03-17 12:50:55.304 UTC [32434] LOG S-0x55d59b5a7d00: db1/pgbounceauth@127.0.0.1:5432 new connection to server (from 127.0.0.1:39014)
2025-03-17 12:50:55.312 UTC [32434] LOG C-0x55d59b59f180: db1/testusr1@127.0.0.1:40534 login attempt: db=db1 user=testusr1 tls=no replication=no
2025-03-17 12:50:57.985 UTC [32434] LOG C-0x55d59b59f180: db1/testusr1@127.0.0.1:40546 login attempt: db=db1 user=testusr1 tls=no replication=no
2025-03-17 12:50:57.990 UTC [32434] LOG S-0x55d59b5a7fc0: db1/testusr1@127.0.0.1:5432 new connection to server (from 127.0.0.1:39046)
2025-03-17 12:51:04.737 UTC [32434] LOG C-0x55d59b59f180: db1/testusr1@127.0.0.1:40546 closing because: client close request (age=6s)
2025-03-17 12:51:10.490 UTC [32434] LOG C-0x55d59b59f180: db1/testusrmd5@127.0.0.1:39124 login attempt: db=db1 user=testusrmd5 tls=no replication=no
2025-03-17 12:51:13.972 UTC [32434] LOG C-0x55d59b59f180: db1/testusrmd5@127.0.0.1:39140 login attempt: db=db1 user=testusrmd5 tls=no replication=no
2025-03-17 12:51:13.974 UTC [32434] LOG S-0x55d59b5a8280: db1/testusrmd5@127.0.0.1:5432 new connection to server (from 127.0.0.1:36502)