# How configure the pgbouncer wth hba auth method

**URL:** <https://forums.percona.com/t/how-configure-the-pgbouncer-wth-hba-auth-method/34025>\
**Category:** PostgreSQL\
**Created:** [September 30, 2024, 6:21am UTC](https://forums.percona.com/t/how-configure-the-pgbouncer-wth-hba-auth-method/34025 "2024-09-30T06:21:21Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [September 30, 2024, 6:21am UTC](https://forums.percona.com/t/how-configure-the-pgbouncer-wth-hba-auth-method/34025/1 "2024-09-30T06:21:21Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [October 3, 2024, 5:34am UTC](https://forums.percona.com/t/how-configure-the-pgbouncer-wth-hba-auth-method/34025/2 "2024-10-03T05:34:22Z")

</div>

Can someone help me on this?

---

<div class="post-metadata">

**Author:** ![lalit.choudhary](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lalit.choudhary/32/51_2.png) [@lalit.choudhary](https://forums.percona.com/u/lalit.choudhary)\
**Post date:** [March 17, 2025, 12:23pm UTC](https://forums.percona.com/t/how-configure-the-pgbouncer-wth-hba-auth-method/34025/3 "2025-03-17T12:23:44Z")

</div>

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

```auto
[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

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

```

Add the `pgbounceauth` user entry in `pg_hba.conf`

#### /etc/pgbouncer/pg\_hba.conf

```auto
# 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.

```auto
[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)

```