connection pooling, many users, many datasources

From: Michael McInness <m(dot)mcinness1(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: connection pooling, many users, many datasources
Date: 2016-06-08 16:49:59
Message-ID: CAAnXuS0czc9ODVi5FWiHGAWBUhGrqRGG=giRnK89NuUNcj5jrw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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,

Mick

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sameer Kumar 2016-06-08 17:12:54 Re: connection pooling, many users, many datasources
Previous Message Stephen Frost 2016-06-08 14:08:05 Re: WAL's listing in pg_xlog by some sql query