Converting from single user w/pool to multiple users

Jeff Amiel <jamiel@istreamimaging.com> writes:

Anyone see any issues with having thousands of postgresql users
established? Are there any internal limits?

There's no hard limit. Offhand the only thing I can think of that might
be a bit slow is password lookup during connection --- I think that does
a linear scan through a list of usernames. This is only an issue if you
use passwords of course, but I suppose you probably would. (It would
likely not be hard to improve the search algorithm, if it did become
a bottleneck.)

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?

This is doubtless the worst problem...

regards, tom lane

Could we continue to use our existing connection pool (via our app
server) and every time the application 'gets' a connection (as a
superuser) , we then 'SET SESSION AUTHORIZATION' to the appropriate user
who is performing the action?

Jeff Amiel <jamiel@istreamimaging.com> writes:

Could we continue to use our existing connection pool (via our app
server) and every time the application 'gets' a connection (as a
superuser) , we then 'SET SESSION AUTHORIZATION' to the appropriate user
who is performing the action?

That would work to the extent that you filter SQL commands so a
nefarious user can't issue his own 'SET SESSION AUTHORIZATION'
to become someone else ...

regards, tom lane

I wrote:

There's no hard limit. Offhand the only thing I can think of that might
be a bit slow is password lookup during connection --- I think that does
a linear scan through a list of usernames. This is only an issue if you
use passwords of course, but I suppose you probably would. (It would
likely not be hard to improve the search algorithm, if it did become
a bottleneck.)

BTW, I take that back --- it already is a binary search, so there
shouldn't be any problem with thousands of users. Still, I like
your idea of continuing to pool the connections better. Backend
startup is a bit expensive.

regards, tom lane

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

Guy Rouillier wrote:

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

Actually, we dont think that the management of the database accounts
will be an issue. Our user administration system will create the users
in postgres at the same time it creates the appropriate 'profile' in our
application databases. Any changes (such as disabling the user) is also
propogated to both places via stored procedure(function) that modifies
both as appropriate.

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

The issue is really propogating the authenticaion credentials to the
database itself.....it's our ON INSERT/ON UPDATE/ON DELETE triggers that
are doing the auditing and they need the user ID to accurately log
changes. In lieu of any other per-connection persistant data option,
this seems like the best bet.

Jeff Amiel wrote:

The issue is really propogating the authenticaion credentials to the
database itself.....it's our ON INSERT/ON UPDATE/ON DELETE triggers
that are doing the auditing and they need the user ID to accurately
log changes. In lieu of any other per-connection persistant data
option, this seems like the best bet.

I still don't like the idea of thousands of connections, most of which
will probably most of the time be doing nothing except consuming lots of
memory. You might want to explore creating a small wrapper around the
JBoss connection pool that uses SET SESSION AUTHORIZATION after
obtaining a connection. That way you can still have a small number of
pooled connections but have the real user id associated with the
connection.

--
Guy Rouillier

We intended to do that very thing (read the earlier parts of the thread
between myself and Tom Lane)

Jeff

Guy Rouillier wrote:

Jeff Amiel wrote:

We intended to do that very thing (read the earlier parts of the
thread between myself and Tom Lane)

Sorry, you're correct. Somehow I missed that exchange (just read the
archives.)

--
Guy Rouillier