Re: connection pooling, many users, many datasources

Pgbouncer worked fine for this. Thanks.

On Wed, Jun 8, 2016 at 1:12 PM, Sameer Kumar <sameer(dot)kumar(at)ashnik(dot)com>
wrote:

>
>
> 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
>