Audit trigger

From PostgreSQL wiki

Jump to: navigation, search

Library Snippets

Generic audit trigger function

Works with PostgreSQL

8.4+

Written in

PL/pgSQL

Depends on

Nothing


Here is an example of a generic trigger function used for auditing changes to tables, and optionally updating a "row_last_updated" column with the UTC timestamp of the change.

The "last updated" timestamp uses "now()", as it is current at the *start* of the transaction, so all actions that occur with each call of the "audit.if_modified_func" will have the same timestamp. 

-- create a schema named "audit"
CREATE schema audit;
 
-- UTC is used as the audit time because if the calling applications are in other time zones, it simplifies things if the source data is in a common time zone.
 
CREATE TABLE audit.logged_actions (
schema_name text NOT NULL,
table_name text NOT NULL,
user_name text,
action_tstamp_utc TIMESTAMP NOT NULL DEFAULT (now() AT TIME ZONE 'UTC'),
action TEXT NOT NULL CHECK (action IN ('I','D','U')),
original_data text,
new_data text,
query text
) WITH (fillfactor=100);
CREATE INDEX logged_actions_schema_table_idx ON audit.logged_actions(((schema_name||'.'||table_name)::TEXT));
CREATE INDEX logged_actions_action_tstamp_utc_idx ON audit.logged_actions(action_tstamp_utc);
CREATE INDEX logged_actions_action_idx ON audit.logged_actions(action);
 
 
-- generic function for all tables
--drop function if exists audit.if_modified_func() cascade;
CREATE OR REPLACE FUNCTION audit.if_modified_func() RETURNS TRIGGER AS $body$
DECLARE
    v_old_data TEXT;
    v_new_data TEXT;
BEGIN
    /*  If this actually for real auditing (where you need to log EVERY action),
        then you would need to use something like dblink or plperl that could log outside the transaction,
        regardless of whether the transaction committed or rolled back.
    */
 
    /* This dance with casting the NEW and OLD values to a ROW is not necessary in pg 9.0+ */
 
    IF (TG_OP = 'UPDATE') THEN
        v_old_data := ROW(OLD.*);
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,new_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data,v_new_data, current_query());
        -- Alternative version, just log the query, not the contents of the columns
        --insert into audit.logged_actions (schema_name,table_name,user_name,action,query) values (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),current_query());
 
        -- to update the row_last_updated column if implemented
        NEW.row_last_updated = timezone('UTC'::text, now());
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := ROW(OLD.*);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,original_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_old_data, current_query());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := ROW(NEW.*);
        INSERT INTO audit.logged_actions (schema_name,table_name,user_name,action,new_data,query) VALUES (TG_TABLE_SCHEMA::TEXT,TG_TABLE_NAME::TEXT,session_user::TEXT,substring(TG_OP,1,1),v_new_data, current_query());
        -- no need to set the "row_last_updated" timestamp, as that is handled using the "DEFAULT" option on in the table DDL
        RETURN NEW;
    ELSE
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - Other action occurred: %, at %',TG_OP,now();
        RETURN NULL;
    END IF;
 
EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE WARNING '[AUDIT.IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
END;
$body$ language plpgsql security definer;


-- Tested with a table named "t" 

drop table if exists t;
create table t (x int not null primary key, y text);
alter table t add row_last_updated timestamp not null default (now() at time zone 'UTC');

-- needs to be applied to all tables that we want to monitor

-- this is a test trigger to show how we can audit all changes to the relevant tables, including inserts 

create trigger t_if_modified_trg before insert or update or delete on t for each row execute procedure audit.if_modified_func();


-- Some sample updates, deletes, and inserts to illustrate the points 

select * from t; select * from audit.logged_actions;

insert into t (x,y) values (1,'asdf'),(2,'werwer'),(3,null);
select * from t; select * from audit.logged_actions;

update t set y='eeeeee' where x=2;
select * from t; select * from audit.logged_actions;

update t set y='yuyuyuy' where x=3;
select * from t; select * from audit.logged_actions;

delete from t where x=1;
select * from t; select * from audit.logged_actions;

-- should be a pk violation 

update t set x=4 where x=2;
select * from t; select * from audit.logged_actions;


Original code by bricklen. Any errors are mine

Retrieved from "http://wiki.postgresql.org/wiki/Audit_trigger"
Personal tools