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.
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)?
Are there other options?
Profuse appreciation for your thoughts and suggestions,
