Re: Auditing with shared username

Hi Ian,
    Thanks for the quick reply.  What I'm confused about is how I let
the trigger function etc. know which homegrown user it was that touched
the record.  Any advice?

Thanks,

Eric

Ian Harding wrote:

>I have a homegrown userid/password system in a database table, and on
>tables I audit, I keep the id of the last person to touch that record,
>and have a trigger write the changed values out to an audit table.  It
>works fine, but of course there is some overhead involved.
>
>You can't involve postgres connections as representing a user since any
>connection pooling system will make that useless.  PG doesn't have
>connection pooling, that is a higher level application function.
>
>
>
>
>
>>>>Eric E <whalesuit@bonbon.net> 12/06/04 8:58 AM >>>
>>>>
>>>>
>Hi all,
>    Like many folks who use three-tier design, I would like to create an
>
>audit trail in my Postgres database, and I would like to do so without
>having to create a database user for each audit.
>
>As I see it, there are two ways to do this, and I can't see a clear way
>to do either of them.  If anyone has better suggestions, I'd of course
>love to hear them.
>
>Here's what I'd thought up:
>
>1) Connect my homebrew login system which runs out of a couple database
>tables to postgres connection/sessionID (i.e., keep track of which
>sessionID represents my current user) so that any audit function can use
>
>the session ID to look up the current user.
>
>2) Maintain a "current homebrew user" session variable that is distinct
>from Postgres' current_user, which I believe stores the current database
>
>user.   I found a couple threads on session variables, but mostly they
>were discouraging people from using such variables.
>
>Does anyone have any good ideas or advice?
>
>Also, both of these methods require that a user maintain his/her own
>session.  I don't know how PG's connection pooling works, but is it
>actually possible to specify a particular session for a particular
>user?  Is there some place I can find documentation on how Postgres
>deals with logins and sessions?
>
>Many thanks,
>
>Eric
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if
>your
>      joining column's datatypes do not match
>
>
>
>