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
Iwas 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
havingindividual 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/credentialas 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
whichhas 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
connectionis 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
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc