Re: audit trail

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" ('');