1

I'm trying to setup a pgbouncer pool that connects to the database using a pool definition that includes a user and password.

Subsequently each client application should connect using a different user and password. The idea is that one pool is shared by multiple client users.

Is that possible?

Here's my setup: pgbouncer.ini

[databases]
testpool = host=testpool.mycompany.com dbname=db1 port=544 user=company_dbo password=company123 max_db_connections=20

[pgbouncer]
auth_type = plain
auth_file = /etc/pgbouncer/users.txt

users.txt

"test_user" "test123"

When I try to connect I get below error

$ export PGPASSWORD='test123';psql -h localhost -U test_user -d db1 -p 5442
psql: ERROR:  password authentication failed for user "test_user"

And the logs say

2019-03-03 16:04:02.668 1 LOG C-0x2022000: db1/test_user@172.17.0.1:33204 login attempt: db=db1 user=test_user tls=no
2019-03-03 16:04:02.719 1 LOG S-0x2026fd0: db1/test_user@10.1.0.118:5442 new connection to server (from 172.17.0.2:58920)
2019-03-03 16:04:02.762 1 WARNING server login failed: FATAL password authentication failed for user "test_user"

It appears that pgbouncer tries to login to the database using the client user and password, rather than the user and password specified in the [databases] section.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Bernie Lenz
  • 1,967
  • 23
  • 45

1 Answers1

2

auth_file is used to authenticate the user with pgBouncer, not to provide a different password for use with the database. pgBouncer will use the password it received from the client to log into PostgreSQL.

I don't know what problem you are trying to solve this way, but perhaps you can use trust authentication between pgBouncer and PostgreSQL and leave the burden of authentication entirely with pgBouncer.

There is no way to “change identity” in pgBouncer.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks Laurenz. Will try "trust" out. The original problem is that we have pgbouncer running behind an AWS NLB which doesn't pass the source IP along to pgbouncer. Because of that all requests appear in the logs with the NLB IPs instead of the IPs of the "source" applications which makes it hard to trouble shoot. To make it easier to identify the source I was trying the following: Application --(AppUser/AppPwd)--> pgbouncer --(PgbUser/PgbPwd)--> postgresdb – Bernie Lenz Mar 04 '19 at 16:12
  • I understand. Yes, `trust` may make this possible. – Laurenz Albe Mar 04 '19 at 16:20