CODEWITHSUNDEEP
postgresqlconnection-poolingpoolpgbouncer
Bernie Lenz
Bernie Lenz

Reputation: 2146

pgbouncer: user,password per pool

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/[email protected]:33204 login attempt: db=db1 user=test_user tls=no
2019-03-03 16:04:02.719 1 LOG S-0x2026fd0: db1/[email protected]: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.

Upvotes: 1

Views: 2499

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

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.

Upvotes: 2

Related Questions