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

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.