Re: connection pooling, many users, many datasources

On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, <m(dot)mcinness1(at)gmail(dot)com>
wrote:

> 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

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com