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
