Обсуждение: 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" ('');