Обсуждение: audit trail
Running PostgreSQL 7.2.1 on RedHat Linux 7.2.
I'm interested in turning on an audit trail
to note what tables have been updated, created,
selected from, who did it, what machine / connection
they're using, etc.
I saw something in the n.g. where something
similar has been done by a trigger and a
table with this info is kept.
I'm also sure this is yet another perfect time
to learn a new facet about PostgreSQL. So,
how do I create / manage an audit trail?
Thanks!
-X
On Fri, 23 Aug 2002, Johnson, Shaunn wrote: > Running PostgreSQL 7.2.1 on RedHat Linux 7.2. > > I'm interested in turning on an audit trail > to note what tables have been updated, created, > selected from, who did it, what machine / connection > they're using, etc. Shaunn, Here are a couple of references on setting up audit trails in Postgres. The first is a link to an older mailing list post which provides some examples: http://archives.postgresql.org/pgsql-novice/2002-06/msg00001.php Also, here's some documentation I wrote up myself that I'll eventually draft into a slightly more formal document if I get around to it. This is an example of a setting up an audit table for a single table. The TG_OP variable I use and several other special variables are documented here: http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html -mark http://mark.stosberg.com/ ######################## dcumentation for Setting up Audit Tables in Postgres 7.1.2 Last updated on 2/12/02 by Mark Stosberg ---------------------------------------- 1. make PL/pgSQL available (as super-user) (We can add this to the template1 database if we want it available to all new databases) CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'c'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; 2. Create Audit Table with timestamp column DROP TABLE t_audit; CREATE TABLE t_audit ( c1 int, transaction_dml varchar(10), -- tells us "UPDATE" or "DELETE" transaction_time timestamp DEFAULT CURRENT_TIMESTAMP ); 3. Create logging Functions -- ------------------------- -- *** Warning *** -- If you modify the function you MUST drop and recreate the event trigger. -- If not, you get a cache error. -- The update function needs to return the NEW row DROP FUNCTION t_audit_update(); CREATE FUNCTION t_audit_update() RETURNS OPAQUE AS 'begin insert into t_audit (c1,transaction_dml) values (OLD.c1,TG_OP); return NEW; end; ' LANGUAGE 'plpgsql'; -- the delete function needs to return the OLD row DROP FUNCTION t_audit_delete(); CREATE FUNCTION t_audit_delete() RETURNS OPAQUE AS 'begin insert into t_audit (c1,transaction_dml) values (OLD.c1,TG_OP); return OLD; end; ' LANGUAGE 'plpgsql'; -- 4. Setup Event triggers -------------------------------------------------- DROP TRIGGER t_audit_update_trigger ON t; CREATE TRIGGER "t_audit_update_trigger" BEFORE UPDATE ON "t" FOR EACH ROW EXECUTE PROCEDURE "t_audit_update" (''); DROP TRIGGER t_audit_delete_trigger ON t; CREATE TRIGGER "t_audit_delete_trigger" BEFORE DELETE ON "t" FOR EACH ROW EXECUTE PROCEDURE "t_audit_delete" ('');