0

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?

2 Answers 2

0

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.

5
  • That's really some light to me about the use of authentication at Pgbouncer. Now it make sense. I forgot the fact that it's using already established database connections. Thanks very much. Nov 29, 2023 at 0:55
  • The bug you reported, even with md5 still facing the problem. Dec 6, 2023 at 7:09
  • When submitting, I verified that the bug only affected double-SCRAM situations, that md5 did work. Presumably you are doing something else, but I don't have enough info about what it is to investigate.
    – jjanes
    Dec 6, 2023 at 13:53
  • When I use authentication type as md5 in Postgres as well as Pgbouncer with different password , authentication at Pgbouncer layer succeeds but Postgres fails. Dec 7, 2023 at 6:16
  • @BasilTitus Without seeing a specific reproduction case, similar to what I provided in the bug report, I don't know what to tell you, other than "works for me".
    – jjanes
    Dec 7, 2023 at 17:23
0

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.

2
  • Thanks for the article above. In our case the pgbouncer is on a separate machine. Is there any benefit in having this extra layer of authentication with same credentials which will later authenticated at database again? is it best practice to use trust between pgbouncer and database and leave authentication to pgbouncer ? Nov 28, 2023 at 7:46
  • The benefit is that you don't have to trust another machine. If you need that or not depends on your security requirements. Note that you don't need to use the same authentication method - you could for example use certificate authentication for pgBouncer. Nov 28, 2023 at 10:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.