PostgreSQL: Pgbouncer authentication

Question

We are using authentication file mode in pgbouncer. The authentication part of Pgbouncer is so confusing. Would be helpful if anybody can throw some light on my below queries.

• Are Pgbouncer connections authenticated twice at bouncer side and DB layer?

• What's the use of authentication at Pgbouncer if it is using the same credentials as database?

• Is it possible to use a different set of user credentials for the Pgbouncer authentication other than DB credentials itself?

Answer 1

The point of pgbouncer is that not every connection to pgbouncer creates a new connection to the database. Or at least, that is a major point, maybe it is not the only point.

An incoming connection to pgbouncer might lead to an authentication to the database itself, or it might just use an existing connection to the database which was already authenticated. So the connection authenticates 1 + 1/N times, once to pgbouncer and some fraction of the time through to the database (if it was the first connection in that pool, or it caused the pool to grow, or needed to replace an expired or skunked connection). I guess you a pgbouncer developer could arrange it so that it authenticates to the pooler (N-1)/N of the time, and channels through to the database 1/N of the time, but since it would still need to ping-pong through the pooler, it is hard to see what the point of that would be.

What's the use of authentication at Pgbouncer if it is using the same credentials as database?

What would the alternative look like? pgbouncer authenticates through to the database the first time, and then just shares that connection out to anyone who shows up, with no checking? Or connections are never re-used, in which case your pooler does no pooling? Or, something else?

Is it possible to use a different set of user credentials for the Pgbouncer authentication other than DB credentials itself?

Yes, that is possible (but I don't think it is very common). But due to an apparent bug in pgbouncer, it is not possible if SCRAM is trying to be used for both sets of credentials.

Answer 2

It is confusing. Since pgBouncer acts as a proxy between client and database, authentication happens at two stages:

• the client has to authenticate with pgBouncer

• pgBouncer has to authenticate with the database

These two steps are theoretically independent, but often you would like to use the database credentials to authenticate with pgBouncer. That's what auth_query is for. On the other hand, you typically install pgBouncer on the database machine, and then you can use trust authentication between pgBouncer and the database unless your security requirements are quite advanced.

Perhaps my introductory article on the topic can be useful for you.