Re: Converting from single user w/pool to multiple users

Jeff Amiel wrote:
> 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.

We use JBoss also. I understand that using database authentication
provides an additional layer of security and accountability, but
alternatives are available without the high overhead (both
administrative and runtime.) Do you really want to try to administer
1000s of database user accounts in addition to whereever you maintain
these same accounts for non-DB authentication. This assumes, of course,
that if you are interested in accountability at all that you
authenticate somewhere. And I certainly wouldn't want to sacrifice
memory that could be put to good use processing database requests to
holding several thousand idle database connections.

Instead, again assuming you authenticate users, you can propogate that
security context to JBoss. Then you can secure the EJB or MBean method
that obtains the database connection from the pool (and presumably does
something useful) to respect the security context. If you want to
audit, you have the security context information, so you can extract the
authentication credentials from there to write to an audit table (or
even include in every row you write to the database.)

--
Guy Rouillier