Thread: Converting from single user w/pool to multiple users
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.
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? >>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 > >
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: > > >>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. > > >
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