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

[Ken Winter]

Charlie, thanks for the encouraging response!  

What you suggest (below) is definitely the way I'd like to do this.
However, ZPsycopgDA and its communications with PostgreSQL are currently
black boxes for me, so I think I need some help taking the next steps.  

A few hours of poking around have led me to the following fragmentary ideas.

* I have found what may be a likely candidate for the code that could be
customized: the method DB.query(self, query_string, max_rows=None,
query_data=None) defined (on my Windows system) in C:\Program Files\Plone
2\Data\Products\ZPsycopgDA\db.py.

* One idea is to parse the query_string in DB.query and, if it contains an
INSERT or UPDATE command, splice a clause to record the current user's id
into it (e.g. put "last_updated_by = request.AUTHENTICATED_USER" into the
SET clause of an UPDATE).  Doing it this way should require nothing special
on the PostgreSQL side, since the user identifier would come through as part
of the INSERT or UPDATE statement.

* The idea of "extending the DA pass the current ZOPE user" seems cleaner.
However, I don't know how to get the DA to pass an additional parameter, and
I don't know how to get PostgreSQL to pick up the parameter so it can store
it as required.  In other words, I don't understand this API, and I don't
know where it is documented.

* I'm not sure if any of the above is on the right track at all - and if
not, what is the right track?

I'd appreciate any guidance anybody can offer.

~ Thanks
~ Ken


> -----Original Message-----
> From: Charlie Clark [mailto:charlie at egenix.com]
> Sent: Tuesday, November 06, 2007 3:26 PM
> To: Ken Winter; Zope-DB List
> Cc: Jim Myers; Michael Adams
> Subject: Re: [Zope-DB] Automatic capture of user ids for auditing
> 
> Am 06.11.2007, 20:12 Uhr, schrieb Ken Winter <ken at sunward.org>:
> 
...
> 
> > 2. Would it be possible to modify or add on to either the ZSQL methods 
> > and/or the database adapter (ZPsycopgDA, in
> > this case) to do what is needed?  If so, how?
> 
> Yes, you could quite easily extend the DA to pass the current Zope user
> and possibly extend any statements as required. 
> Have you an idea how you want to do this
> on the PostgreSQL side? Any chance of accepting an additional parameter?
>