Re: Triggers and Audit Trail

From: Eric E <whalesuit(at)gmail(dot)com>
To: Marcus Couto <marcus(at)altapoint(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Triggers and Audit Trail
Date: 2005-12-29 21:23:38
Message-ID: 43B453DA.1050006@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Marcus,<br>
<br>
Marcus Couto wrote:
<blockquote cite="mid003c01c60c9d$ab543860$bc00000a(at)programmer2"
type="cite">
<meta http-equiv="Content-Type" content="text/html; ">
<meta content="MSHTML 6.00.2900.2802" name="GENERATOR">
<style></style>
<div><font face="Arial" size="2">Hi all. I'm new with PostgreSQL and
this is my first post, so easy on me... :)</font></div>
<div>&nbsp;</div>
<div><font face="Arial" size="2">I'm thinking&nbsp;of using the native
procedural language and triggers to keep an audit trail. For&nbsp;editing
changes, we only keep&nbsp;a log of the modified fields and we create a
record for each modified value. The audit table&nbsp;</font><font
face="Arial" size="2">record holds information&nbsp;like user, date/time,
table_name, field_name, old_value, new_value, type(delete, new, edit).&nbsp;</font><font
face="Arial" size="2">I have a couple of questions:</font> <br>
</div>
</blockquote>
I wrote such an audit system and am using it production.&nbsp; It works
reasonably well.&nbsp; It was quite a bit of work to develop, and still has
some rough edges.<br>
<blockquote cite="mid003c01c60c9d$ab543860$bc00000a(at)programmer2"
type="cite">
<div><font face="Arial" size="2">Using triggers, is there a way to
loop through the fields of the OLD and NEW records? </font><font
face="Arial" size="2">I haven't found a generic way to get the field
name and value that triggered the update&nbsp;other than hard coding if
statements to compare every field of the OLD and NEW records.</font>&nbsp;</div>
</blockquote>
I had this problem, and as Michael Fuhr mentioned you can't resolve it
in PL/PGSQL.&nbsp; I ended up using PL/TCL because it was stable under 7.4
and it does the field dereferencing you need.&nbsp; As of 8.0 and later
PL/PERL is also stable and I believe it does field dereferencing as
well. <br>
<br>
<blockquote cite="mid003c01c60c9d$ab543860$bc00000a(at)programmer2"
type="cite">
<div><font face="Arial" size="2">Another issue is how to keep track
of the audit user since we share the same postgres user and our
application keeps track of the actual current user locally. Is there
some kind of way we can set the current user so that we're&nbsp;able to read
it from the trigger event? Other suggestions?</font></div>
</blockquote>
I looked into that as well, and it's pretty hard.&nbsp; Most applications
that use only one database user but have multiple application-level
users are three-tier, and the apps tend to do logging themselves, often
using a separate loggin mechanism like log4j and friends.&nbsp; 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 your
application log the user after writing the row history table.&nbsp;
Otherwise you're at the mercy of when and how your database connection
is opened (i.e., how long a session lasts).<br>
<br>
Some other tips:<br>
I use a PL/TCL trigger function to enumerate the table and fields, and
then call two functions that actually write the log of the action and
the row history table.&nbsp; <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><font color="#009900"><br>
# get the name of the table<br>
spi_exec "select relname as trg_tablename from pg_class where
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 your row history
table (or pass it to a row-history-writer function as I do)<br>
}<br>
</font><br>
The functions that actually write the log run setuid (i.e. "Security of
definer" checkbox in pgAdmin or SECURITY DEFINER in PGSQL parlance).&nbsp;
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 in the 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.&nbsp; I think a
comprehensive audit package for Postgres would be a great addition, but
sadly I lack the resources to contribute it.<br>
<br>
Hope that helps,<br>
<br>
Eric<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 4.8 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ted Byers 2005-12-29 21:32:04 another problem with stored procedures
Previous Message Michael Fuhr 2005-12-29 21:14:09 Re: alter column datatype with cast