| Lists: | pgsql-general |
|---|
| From: | "Marcus Couto" <marcus(at)altapoint(dot)com> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | Triggers and Audit Trail |
| Date: | 2005-12-29 17:31:14 |
| Message-ID: | 003c01c60c9d$ab543860$bc00000a@programmer2 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Hi all. I'm new with PostgreSQL and this is my first post, so easy on me... :)
I'm thinking of using the native procedural language and triggers to keep an audit trail. For editing changes, we only keep a log of the modified fields and we create a record for each modified value. The audit table record holds information like user, date/time, table_name, field_name, old_value, new_value, type(delete, new, edit). I have a couple of questions:
Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records.
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 able to read it from the trigger event? Other suggestions?
Thanks
| From: | Jeff Amiel <jamiel(at)istreamimaging(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 17:44:26 |
| Message-ID: | 43B4207A.5030702@istreamimaging.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
>
> Using triggers, is there a way to loop through the fields of the OLD
> and NEW records? I haven't found a generic way to get the field name
> and value that triggered the update other than hard coding if
> statements to compare every field of the OLD and NEW records.
We (my company) never found a way. We ended up writing java code that
analyzed the catalog tables that generated the appropriate 'if'
statements in the trigger functions for us....
>
> 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 able to read it from the trigger event? Other
> suggestions?
Inside our application, when we grab a connection from our connection
pool, the user information is populated into a termporary table that the
audit triggers can then later read for any transactions on that
connection.
| From: | Jeff Amiel <jamiel(at)istreamimaging(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 17:46:48 |
| Message-ID: | 43B42108.9000904@istreamimaging.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
> We (my company) never found a way. We ended up writing java code that
> analyzed the catalog tables that generated the appropriate 'if'
> statements in the trigger functions for us....
Actually....we tinkered with hitting the catalog tables inside our
triggers, but for performance reasons, we generated the 'if' statements
instead....
| From: | Michael Fuhr <mike(at)fuhr(dot)org> |
|---|---|
| To: | Jeff Amiel <jamiel(at)istreamimaging(dot)com> |
| Cc: | Marcus Couto <marcus(at)altapoint(dot)com>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Triggers and Audit Trail |
| Date: | 2005-12-29 18:01:37 |
| Message-ID: | 20051229180137.GA53292@winnie.fuhr.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Thu, Dec 29, 2005 at 11:44:26AM -0600, Jeff Amiel wrote:
> >Using triggers, is there a way to loop through the fields of the OLD
> >and NEW records? I haven't found a generic way to get the field name
> >and value that triggered the update other than hard coding if
> >statements to compare every field of the OLD and NEW records.
>
> We (my company) never found a way. We ended up writing java code that
> analyzed the catalog tables that generated the appropriate 'if'
> statements in the trigger functions for us....
As far as I know you can't do this yet in PL/pgSQL, but you can in
other languages like PL/Perl and PL/Tcl.
--
Michael Fuhr
| 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 |
| 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> </div>
<div><font face="Arial" size="2">I'm thinking of using the native
procedural language and triggers to keep an audit trail. For editing
changes, we only keep a log of the modified fields and we create a
record for each modified value. The audit table </font><font
face="Arial" size="2">record holds information like user, 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 using it 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(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 other than hard coding if
statements to compare every field of the OLD and NEW records.</font> </div>
</blockquote>
I had this problem, and as Michael Fuhr mentioned you can't resolve it
in PL/PGSQL. I ended up using PL/TCL because it was stable under 7.4
and it does the field dereferencing you need. 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 able to read
it from the trigger event? Other suggestions?</font></div>
</blockquote>
I looked into that as well, and it's pretty hard. 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. 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.
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. <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).
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. 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 |