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

Στις Tuesday 28 December 2010 15:26:11 ο/η Lew έγραψε:
> Achilleas Mantzios wrote:
> > Yes that's the logic. One connection pool per user.
> > I find it more clever, fine grained and just better from
> > many perspectives (security/auditing/diagnosis/debugging being the main ones).
>
> Too clever by half. What do you do with the pool when a user is logged off
> for a few days, then comes back?

Personally, i do not do anything ;) Its the Jboss JCA pool that does the magic.
To answer your question, simply defining min-pool-size=0 will mean that when the user
goes home, after a defined timeout all his connections will be closed.
When he comes back tomorrow, his connection(s) will be automatically created.

> How many pools will you have when a thousand
> users log off and another thousand log on? How many connections will you keep
> in each pool? How will the DB engine keep up with all that?

Each pool will have the minimum connections, in order for the app to work ok.
e.g. if in my code the max simulteanous connections opened are 4, then i will define that number.
The max number of users is known.
>
> I predict that you will get no increase in security or auditing capability
> that couldn't be handled with the conventional "one user per application"
> approach, and that diagnosis and debugging will be complicated in some other
> way, and that you'll have performance and resource issues.
>

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
b) Peculiar errors in the pgsql log files, with no streight forward source or explanation
(imagine loads of points of access for a db)

What do you do?
You identify the sql either by looking in pg_stat_activity or by looking at the error in the pgsql log.
Then you do a grep in the application to find possible programs which build and call such SQL.
(Unfortunately in the case of dynamic SQL generation our task becomes an unpleasant procedure of guessing).
Then you look in the jboss server logs and in the jboss access logs (hint : actual http GET/POST with users are *ONLY* recorded in the access logs)
to see which user might be calling that program/page. Imagine that the system gets many hits/second.
At this point, you are simply lost, in the best case you might have some clue about who run the program, but you would not be
able to prove anything for sure.
Now compare this, with a nice line from the pgsql log file which looks like:
dynacom amantzio 92607 4d19fd48.169bf 2010-12-28 17:08:01.379 EET ERROR: permission denied for relation vessels
(where amantzio is the user)

>
> --
> Lew
> Ceci n'est pas une pipe.
>

--
Achilleas Mantzios