Re: Auditing with shared username - Mailing list pgsql-general

Well, upon further reflection, I came to this conclusion:
In order to do trigger-based auditing that logs a homegrown user, you
need to hand the database some token or identifier for the user that it
can use to record the user into the audit log.  That part is pretty
straightforward: if you're not using the db's login system, the db can't
know which user this is unless you tell it.

The only relatively secure and general way I can see to do this is to
have each table exposed by a view that adds an updating-username field
to the basic fields, and then uses a rule to hand that username off to
the auditing function.

Any thoughts?  Did anyone do this differently?  If so, how?

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