Search code examples
postgresqlpgbouncer

PgBouncer peer & host authentication for the same database

I have a PgBouncer installed on the same host with postgres server and I have several scenarios for it:

  1. Monitoring(pgwatch2) should connect with peer authentication to the postgres(for example) database via PgBouncer
  2. External client connection should also be able connect to the database postgres via PgBouncer(with host authentication method)

I can't understand how to properly configure PgBouncer to follow the rules:

  1. If the connection to PgBouncer is done via socket, it should proceed with peer authentication to the postgres server
  2. If the connection uses the host authentication method, PgBouncer should also connect to postgres server with the corresponding host method

My current configuration is as follows, but it doesn't choose socket or localhost properly(sometimes it fails):

[databases]
* = host=localhost port=5432
* = host=/tmp port=5432

I added two different rules to fix my issues, but it doesn't seem to work as expected.

Solution

  • I think that you can't distinguish pools based on the incoming connection type. You could create a fake database entry, and then direct it to the real database in the config:

    [databases]
monitoring_database = host=/tmp dbname=postgres port=5432
* =                   host=localhost port=5432

    But in this case the peer authentication would be coming from the owner of the pgbouncer process, not from the owner of the monitoring client.