Web based application that utilizes PostgreSQL (7.45 currently).
A debate is raging in the office regarding the idea of switching from
using a connection pool (that utilizes a single god-like database user)
to a model where each web user would have a mirror postgresql user. All
connections to the database (from the web/app server would be
established with that user id).
Some questions:
Anyone see any issues with having thousands of postgresql users
established? Are there any internal limits?
Previously, the connection pool (provided by jboss) would 'wait' for an
available question until a timeout period was reached before returning
an error. Under the new scheme, we are limited by max_connections
(postgresql.conf)...and would return an error immediately when no
connections were available. Is there any way to mitigate this?
Does anyone else do this? Is it standard/recommended/taboo? Our
primary reason for this is database auditing. Our audit triggers would
now be able to pick up the user id directly instead relying on the
application programmer to provide it (or some other potentially
unreliable method) Secondarily is the obvious benefit of security. We
could divide our users into group and lock down table access as
appropriate.