Is it advisable to call script after user creation in relational database?

Question

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.

Answer 1

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.