Re: Triggers and Audit Trail

[Eric E <whalesuit@xxxxxxxxx>]

Hi Marcus,

Marcus Couto wrote:

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:

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.

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. 

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.

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?

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).

Some other tips:
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. 
some key lines from that TCL function:

switch $TG_op {
# do different things for different SQL commands
DELETE {}
INSERT {}
UPDATE {}
SELECT {}
default {}

# get the name of the table
spi_exec "select relname as trg_tablename from pg_class where oid=$TG_relid;"

# loop over all the fields in the relation new getting field names and values
foreach {fieldname fieldval} [array get NEW] {
# 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)
}

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.

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.

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.

Hope that helps,

Eric