Question 296643 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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.

Tags:

2

Answers


  1. 0

    Use a different database user for the monitoring system, and create a special one-session pool for it. If your monitoring system is smart enough to keep the database connection open, do without pgBouncer and let the monitoring system connect to the database directly.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search