Pgbouncer worked fine for this. Thanks. On Wed, Jun 8, 2016 at 1:12 PM, Sameer Kumar <sameer.ku...@ashnik.com> wrote:
> > > On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, <m.mcinne...@gmail.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 >
