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

Στις Tuesday 28 December 2010 12:15:22 ο/η John R Pierce έγραψε:
> On 12/28/10 2:00 AM, Achilleas Mantzios wrote:
> > Hello, and merry xmas,
> > Traditionally we have been using jboss JCA pools with postgresql by having every connection authenticate
> > against the static postgres user, or 3 other predifined static users.
> > This was working ok, but made debugging hard, there was no way to confidently link log from the jboss log
> > with log from postgresql, or just find out which user causes a faulty situation. The pgsql user was also one of those 4.
> > Lately i have been thinking of authenticating the connections in jboss JCA pools against the active web user,
> > i configured it, and it actually works in some tests i made. I find this more attractive, since it will give much better statistics,
> > ( i would trust pgsql stats over jboss stats).
> > What i'd like to ask, is if anyone of you has done this and had a good experience. The resaons why i ask, is that
> > documentation in jboss is minimal regarding this issue, and the commonly usual practice is to use the same
> > user when creating connections in the pool. I should ask the jboss ppl as well, but my experiences on their camp
> > were not very encouraging (i never got any serious feedback from there).
> >
> > Well, i'd just like to hear your thoughts on that.
>
> I can't address jboss specific issues, but in general in postgres, all
> the connections in a given pool have to be authenticated as the same
> postgres user, otherwise you're reconnecting to postgres to authenticate
> different users each time which defeats the whole purpose of a
> connection pool.... so, are you thinking of creating a pool for each
> web user?!? that could get messy fast.
>

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

We have LDAP based authentication in Jboss. And i use the very same LDAP server for postgresql authentication as well.
So i have one point of authentication for both Jboss, PostgreSQL, the very same LDAP server.
User logs in, and his credentials are delegated to PostgreSQL with *no* involvement of any application code.
So from a one-user monolithic declarative model we can go now on fully multi sql user, with *no changes* in the app,
or the .EAR configuration!!!
As we speak i am testing it with max 4 connections/user.
Both systems (postgresql, jboss) support this as is, with just recompilation (postgresql) / configuration (jboss).
The main question is twofold: If any one has any theoretical objections to this model,
and 2nd if anyone has encountered any problems by using this or smth similar.
>
>
>
>
>
>

--
Achilleas Mantzios