Auditing with shared username

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

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