Re: Triggers and Audit Trail - Mailing list pgsql-general

Hi Marcus,<br /><br /> Marcus Couto wrote: <blockquote cite="mid003c01c60c9d$ab543860$bc00000a@programmer2"
type="cite"><style></style><div><fontface="Arial" size="2">Hi all. I'm new with PostgreSQL and this is my first post,
soeasy on me... :)</font></div><div> </div><div><font face="Arial" size="2">I'm thinking of using the native procedural
languageand triggers to keep an audit trail. For editing changes, we only keep a log of the modified fields and we
createa record for each modified value. The audit table </font><font face="Arial" size="2">record holds
information likeuser, date/time, table_name, field_name, old_value, new_value, type(delete, new, edit). </font><font
face="Arial"size="2">I have a couple of questions:</font><br /></div></blockquote> I wrote such an audit system and am
usingit production.  It works reasonably well.  It was quite a bit of work to develop, and still has some rough
edges.<br/><blockquote cite="mid003c01c60c9d$ab543860$bc00000a@programmer2" type="cite"><div><font face="Arial"
size="2">Usingtriggers, is there a way to loop through the fields of the OLD and NEW records? </font><font face="Arial"
size="2">Ihaven't found a generic way to get the field name and value that triggered the update other than hard coding
ifstatements to compare every field of the OLD and NEW records.</font> </div></blockquote> I had this problem, and as
MichaelFuhr mentioned you can't resolve it in PL/PGSQL.  I ended up using PL/TCL because it was stable under 7.4 and it
doesthe field dereferencing you need.  As of 8.0 and later PL/PERL is also stable and I believe it does field
dereferencingas well. <br /><br /><blockquote cite="mid003c01c60c9d$ab543860$bc00000a@programmer2"
type="cite"><div><fontface="Arial" size="2">Another issue is how to keep track of the audit user since we share the
samepostgres user and our application keeps track of the actual current user locally. Is there some kind of way we can
setthe current user so that we're able to read it from the trigger event? Other suggestions?</font></div></blockquote>
Ilooked into that as well, and it's pretty hard.  Most applications that use only one database user but have multiple
application-levelusers are three-tier, and the apps tend to do logging themselves, often using a separate loggin
mechanismlike log4j and friends.  So for that part I'd either have your app write the user action into the appropriate
table,or look into retrieving the PK of your audit/history table row, passing it back to your application and having
yourapplication log the user after writing the row history table.  Otherwise you're at the mercy of when and how your
databaseconnection is opened (i.e., how long a session lasts).<br /><br /> Some other tips:<br /> I use a PL/TCL
triggerfunction to enumerate the table and fields, and then call two functions that actually write the log of the
actionand the row history table.  <br /> some key lines from that TCL function:<br /><br /><font color="#009900">switch
$TG_op{<br /> # do different things for different SQL commands<br /> DELETE {}<br /></font><font color="#009900">INSERT
{}<br/></font><font color="#009900">UPDATE {}<br /></font><font color="#009900">SELECT {}<br /> default {}<br
/></font><fontcolor="#009900"><br /> # get the name of the table<br /> spi_exec "select relname as trg_tablename from
pg_classwhere oid=$TG_relid;"<br /><br /> # loop over all the fields in the relation new getting field names and
values<br/> foreach {fieldname fieldval} [array get NEW] {<br /> # you can use this to assemble your SQL to insert into
yourrow history table (or pass it to a row-history-writer function as I do)<br /> }<br /></font><br /> The functions
thatactually write the log run setuid (i.e. "Security of definer" checkbox in pgAdmin or SECURITY DEFINER in PGSQL
parlance). This means that the audit (actions) table and row history tables can be stored in schemas not readable by
users.<br/><br /> Also bear in mind when implementing an audit trail in this way that you'll have to apply any changes
inthe tables you are auditing to the tables that store your audit trail, and this can get complex as the tables
evolve.<br/><br /> There was also some audit code for Postgres written in C, but I couldn't find much documentation for
it,so I abandonded it.  I think a comprehensive audit package for Postgres would be a great addition, but sadly I lack
theresources to contribute it.<br /><br /> Hope that helps,<br /><br /> Eric<br />