Thread: Auditing with shared username
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
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
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
>
>
>
>
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 > > > >