Could pgsql jdbc support pool reauthentication?

Hello,

I was at pgconfeu and attended a talk by Joe Conway about STIG and the implementation of set_user. It took me back when I was trying to find better ways to do connection pooling with jboss/widlfly.

Basically in jboss/wildfly you can have a single app user for all connections, which takes from you all the benefits of having individual postgresql users, or specify a security-domain where you say
jboss to use another login module such as CallerIdentity which makes the jboss pooled connection use the same user/credential as the one of the logged in jboss user, which is great, as we can apply
all the security mechanisms of postgersql, have correct logging/stats per user which is very useful, row-level security, apply advanced multitenancy schemes, etc... but suffers that every user has his
own pool. So if say we need 5 connections max for the most complex app to work, and we have 200 users, then at peak time, the total number of connections would have to be raised to 1000.

So, one solution would be to keep one common pool with connections originally authenticated with a dedicated app user which has the privilege to run set_user, and then when getConnection() is called,
to run set_user with the calling user, and have the app/db behave as per the user's authorization, then when the connection is closed, to call reset_user so that the effective user of the inactive
connection to be again the dedicated app user.

This way we could have one single pool, and have all the benefits of the postgresql's security system.

Any thoughts on this?

Thanks

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt