[Zope-DB] Automatic capture of user ids for auditing

[Ken Winter]

I have a Zope site with a PostgreSQL (8.1) back end.  Only authenticated
users have access to the site and the database.  Each database table has
created_by and last_updated_by columns for auditing purposes.  

The question here is:  How to get these audit columns *automatically*
updated with the user id of the user who initiates each INSERT or UPDATE
action?  "Automatically" means that the application doesn't have to do
anything to get these updates to happen.
 
The problem is that I have multiple application-level, Zope-authenticated
end users, but only one user at the database level.  So when end user 'fred'
initiates an UPDATE action (implemented as a ZSQL method), PostgreSQL
doesn't know which end user initiated it.  So the problem is: how to tell
PostgreSQL that 'fred' was the user who initiated this transaction?  Only
with this piece of info can database-side machinery (triggers or whatever)
put 'fred' into the last_updated_by column of each record that was updated
by that UPDATE action.

The idea of simply requiring every ZSQL method to update the audit column
explicitly (e.g. to have every UPDATE command look something like "UPDATE
... SET last_updated_by = request.AUTHENTICATED_USER, ...") fails the
"automatically" test.  It's not a good idea to leave system auditing to the
whims and foibles of each application developer.

It seems like the components that might be able to pass this info to the
database are the ZSQL methods and/or the database adapter (PsycopgDA, in
this case).  So, questions:

1. Is there any way to get the info across with either of these components
as they currently exist?  If so, how?

2. Would it be possible to modify or add on to either of the to do what is
needed?  If so, how?

3. Is having one Dhatabase user per Zope-level end user an alternative worth
considering?

4. Any other ideas about how to solve this problem?
 
~ TIA
~ Ken

P.S. I have found two previous discussions of (approximately) this issue:

http://www.gossamer-threads.com/lists/zope/db/48026 

http://archives.postgresql.org/pgsql-general/2005-12/msg01323.php 

Neither of these seemed to produce an answer (that I could understand).