Re: PostgreSQL Authentication and Pooling in JBoss : static 'postgres' user VS dynamic jboss user - Mailing list pgsql-jdbc

Στις Tuesday 28 December 2010 20:08:59 γράψατε:
> On Tue, Dec 28, 2010 at 7:13 AM, Achilleas Mantzios <
> achill@matrix.gatewaynet.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.
Anyways i am not endorsing anything of all this.
All i am saying is that if someone has an accurate aproximation of the distribution of connections
and execution of statements in the system, then he can deploy a setup like the one described,
without killing his performance.
Our system accepts about 100 distinct users per day average, with max value observed during the last 10 months = 123
distinctusers/day. 
(they are ordinary office employees)
So simply setting max_connections=400 would solve the problem, (current value is 100) even in the xtreme case
when all 100 users run the most complex program in the app (requiring 4 connections), in the same time span (as defined
byidle-timeout-minutes) 
(lets remind you here once again that we are talking about jboss here)
Of course i will not have to do that. Post probably i will leave the current value of max_connections,
but i will have to play with  idle-timeout-minutes, (decrease it, to make room faster for new connections)
>
> If you are looking for systemic problems that are related to authorization,
> you are only going to create more of those kinds of problems by having such
> fine-grained authorization configuration within your database - forget to
> grant an authority to user y on table x when some app state changes and you
> suddenly have authorization errors.
This is not a problem, for every new table the initial auth script runs again and it will be all right.
(since GRANT ALL ON ALL TABLES IN SCHEMA <schema> to <user> does not
have any impact for future tables, otherwise not even that would be need).
BTW, while we are at it, 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
>
> If you are looking for data issues like 'who was responsible for setting
[snip]
> If you
> have high load and are write-mostly, then it could easily be an unacceptable
> increase (doubling) in load.
>

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)

Now what would be ultra cool, is if we had the means to reauthenticate inside the same connection.
As far as i have tested (but with a limited scope) with psql client, i didnt manage to do that.
If we could reauthenticate in the same connection, then we would have one standard traditional pool
with many shared connections and the user would be authenticated within this existing connection.

I do not know if this notion is supported in the postgersql protocol, jdbc, or jboss pool
in order to be a viable alternative.

If smth like this existed it would bring an long lost (from the mainframe era) functionality to the
J2EE world!!
--
Achilleas Mantzios