Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user

On Wed, Dec 29, 2010 at 3:25 AM, Achilleas Mantzios <
achill(at)matrix(dot)gatewaynet(dot)com> wrote:

> Στις Tuesday 28 December 2010 20:08:59 γράψατε:
> > On Tue, Dec 28, 2010 at 7:13 AM, Achilleas Mantzios <
> > achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> >
> > >
> > > You most probably are referring to a low-complexity system.
> > > Lets see this scenario. Imagine you want to indentify a cause for a
> variety
> > > of problems. Examples
> > > a) High load, you want to find what/who causes a very high load on the
> > > server
> > >
> >
> > Unfortunately the most likely culprit in your system is going to be the
> huge
> > number of connections that postgres is having to deal with. You should
> read
> > up on postgresql performance tuning and learn about how having large
> numbers
> > of connections to the server can impact postgresql performance.
> On the practical side, increasing the max_connections number to 2000 and
> having created 1000 connections
> on my test FreeBSD system did not produce any visible degradation in
> performance.
> Now on the theoretical side, It is true that connections eat up (some small
> mem) from shared mem.
> It is true that memory usage will increase by the use of work_mem when
> simultaneous users perform sorting, etc
> also it is true that after several 100s of connections, the task of opening
> a new connections becomes
> a rather expensive and resource intensive operation, but the end result is
> nowhere as bad as you seem to believe.
>

1000 idle connections may not be much of a problem, but 1000 connections all
performing work simultaneously can be very problematic as you compete for
both CPU and I/O resources. But if that's not the nature of your load, then
it wouldn't be a problem, certainly.

would you prefer "forgetting" to grant a user the right to perform a
> certain task
> or giving anyone the power to exploit any SQL holes in the application run
> by the one and only postgres super user? ;D
>

Well, I wouldn't generally endorse having an application connect as a
postgres super user, or even as a user with anything more than the bare
minimum required for the application to function. You can potentially
isolate one customer's data from another, depending upon your schema, but
there's no reason to think that a single user will have any more
authorization to do malicious things to the database than a user in your
multi-user scenario.

> Not only that, but i think that this is a userland solution. I prefer
> things to be done system-wise.
> As an Example, if i deploy the setup as i present it here, and if indeed it
> turns up to be a disaster
> it will cost me about 10 seconds to go into
> ../server/default/deploy/postgres-xa-ds.xml
> comment the <security-domain>somesecdomain</security-domain> tag, uncomment
> user and password
> and we are back to the same good old OnePool (JBossManagedConnectionPool)
>

Granted, though it wouldn't take any longer than that to disable my
mechanism by eliminating the aspect which causes the user to be set in the
db. If coded such that no stored user means no work in the trigger, then
the triggers become very low overhead, and it isn't much more difficult to
disable the triggers entirely. Yes, it is a userland solution, but it also
gives you visibility into userland state that is difficult to get in any
other way (we track remote address of the user as well as user identity, for
example, and we also get a full audit log of every change made to important
tables including the ability to roll back an individual customer's state to
an earlier point in time without playing complex games with backups). But
it is certainly not a solution for everyone. I was just throwing it out
there as an option that might solve the problem you are trying to solve.