I am working with a system that uses JDBC and JNDI-based connection pooling. There are currently many organizations that use the system. Each of the organizations has multiple, individual system users.
Currently, each entity has its own database and a corresponding application-based datasource and connection pool.
Because of the nature of the data, it is of utmost importance that each organization's data is secure and inaccessible to other entities.
Maintaining individual datasources/connection pools for each entity is extremely cumbersome. It just doesn't seem like a good solution as the number of entities grows to many dozens.
And while handling connections on-demand would provide a dynamic alternative, it also feels like a bad option as I would hate to lose the performance and robustness of pooling connections.
Consider using a database side pool which can pool connections for each user and dbname pair. You might want to check pgpool and pgbouncer.
With one of them you can get benefits of pooling and yet let applicationmake connection on the go as request comes in
How about using SET/RESET ROLE so I could connect to the db under a group role and then changing roles per session to restrict access to entity-specific schema (assuming permissions are set correctly in the database)?
I am not sure if this is the reason or right use case for SET ROLE.
Using different users (either with same app server or different) is the best way if ensuring data is not accessible across organizations.
Are there other options?
Profuse appreciation for your thoughts and suggestions,
Mick
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533