0

I need to add users to pgbouncer dynamically when users are created in postgres.

Is it advisable to use triggers to invoke shell script which add entry in userslist.txt of pgbouncer on pg_catalog.authid insertion event or call shell scrip directly from application.

I don't find any support in pgbouncer for dynamic addition of user & db details to pgbouncer configuration.

preethi
  • 59
  • 1
  • 2
  • 7

1 Answers1

0

That is no problem, and you don't need to add all users to userlist.txt.

You create a special “authentication user” to PostgreSQL and have only that in userlist.txt. Configure that user as auth_user in pgbouncer.ini and configure an auth_query that allows the user to retrieve another user's password.

Authentication then works like this:

  • when you try to connect to pgBouncer, it retrieves the user's password from PostgreSQL

  • pgBouncer authenticates you using that password

See here for an example.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Nice.This solved my problem. Any other way in pgbouncer to allow all database? – preethi Sep 17 '20 at 05:39
  • 1
    To quote the documentation: "“*” acts as a fallback database: if the exact name does not exist, its value is taken as connection string for requested database. Such automatically created database entries are cleaned up if they stay idle longer than the time specified by the `autodb_idle_timeout` parameter." – Laurenz Albe Sep 17 '20 at 05:57
  • Huh? Besides, that surely doesn't belong to this question. – Laurenz Albe Sep 22 '20 at 06:20