Re: Totally weird behaviour in org.postgresql.Driver

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Peter <peter(at)greatnowhere(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Totally weird behaviour in org.postgresql.Driver
Date: 2009-03-17 11:32:32
Message-ID: 49BF8A50.6080502@opencloud.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Peter wrote:
>>>>> It's fairly unusual to have a tomcat application of any size login to
>>>>> the db as the user. Could you share the reason why ?
>>>>>
>>>>>
>>>> The app is actually middleware for Adobe Flex frontend and PG backend,
>>>> not a regular web app. The architecture requires PG to know which user
>>>> has connected (lots of heavy lifting takes place in PG), and we so far
>>>> havent found any other way how to let PG know which user has connected.
>>>> The only alternative was to supply user ID in every PG function call but
>>>> that is messy and introduces it's own limitations as well. If you have
>>>> any suggestions I'm all ears! ;)
>>>>
>>> Set a user variable after you've obtained a connection from the pool, and
>>> use that to log user-specific values. That way, you maintain the
>>> benefits
>>> of connection pools, but can still identify individual users.
>>>
>> It would seem to me that if you need to scale this app then you are going
>> to
>> have to set the user in the application somewhere. Having all of the users
>> connect as themselves doesn't lend itself to being scalable.
>
> I guess you're right, but even so I should be able to scale it to hundreds
> of simultaneous users - the only limiting factor is number of connections on
> PG server.
>
> So is there a way to associate user variable with Postgres connection that
> can be picked up by SQL code running in that connection? Right now I can
> only think of PlPerl function that caches user id in a global variable, but
> am not sure about potential pitfalls of such setup...

Perhaps you could have your pool connect as a fixed superuser, and issue
a SET SESSION AUTHORIZATION each time you get a connection before you do
anything else.

Or you could do something similar with a non-superuser that had many
roles, and use SET ROLE.

-O

In response to

Browse pgsql-jdbc by date

  From Date Subject
Next Message Віталій Тимчишин 2009-03-17 11:41:50 Re: Totally weird behaviour in org.postgresql.Driver
Previous Message Peter 2009-03-17 08:25:35 Re: Totally weird behaviour in org.postgresql.Driver