Enable basic CUD Auditing for Postgresql on public tables

postgresqlaudit.md

Assumptions: The tables you want audited will have an integer column called id. This will then be later accessible in the audit table by the column row_id.

A table to store audit actions in:

create table logged_action (
    id              serial,
    table_name      text not null,
    user_name       text not null,
    action_tstamp   timestamp with time zone
        not null default current_timestamp,
    action          text not null check(action in ('I', 'D', 'U')),
    original_data   text,
    new_data        text,
    schema          text not null,
    row_id          integer not null
);

A function to execute when CUD happens:

CREATE OR REPLACE FUNCTION if_modified_func() RETURNS TRIGGER AS $body$
DECLARE
    v_old_data TEXT;
    v_new_data TEXT;
    v_schema_query TEXT;
    v_schema TEXT;
    msg 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.
    */

    v_schema_query := format(
        'with x as ' || 
            '(select column_name from information_schema.columns ' ||
            E'where table_name = \'%I\' ' ||
            'order by ordinal_position) ' ||
        'select array_agg(column_name::text) from x', TG_TABLE_NAME);
    execute v_schema_query into v_schema;

    /*  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 logged_action (
            table_name,
            user_name,
            action,
            original_data,
            new_data,
            schema,
            row_id)
        VALUES (
            TG_TABLE_NAME::TEXT,
            user::TEXT,
            substring(TG_OP,1,1),
            v_old_data,
            v_new_data,
            v_schema,
            old.id);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := ROW(OLD.*);
        INSERT INTO logged_action (
            table_name,
            user_name,
            action,
            original_data,
            schema,
            row_id)
        VALUES (
            TG_TABLE_NAME::TEXT,
            user::TEXT,
            substring(TG_OP,1,1),
            v_old_data,
            v_schema,
            old.id);
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := ROW(NEW.*);
        INSERT INTO logged_action (
            table_name,
            user_name,
            action,
            new_data,
            schema,
            row_id)
        VALUES (
            TG_TABLE_NAME::TEXT,
            user::TEXT,
            substring(TG_OP,1,1),
            v_new_data,
            v_schema,
            new.id);
        RETURN NEW;
    ELSE
        raise warning '[IF_MODIFIED_FUNC] - Other action occurred: %, at %', TG_OP, now();
        RETURN NULL;
    END IF;

EXCEPTION
    WHEN data_exception THEN
        raise warning '[IF_MODIFIED_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        raise warning '[IF_MODIFIED_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;
    WHEN OTHERS THEN
        raise warning '[IF_MODIFIED_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;
END;
$body$
LANGUAGE plpgsql;

A view to show which tables have an audit trigger installed. Don't forget to make an exception to not look at the _audit table itself!:

drop view table_audit_trigger;
create or replace view table_audit_trigger as
    select table_name, tgname
    from
        (select table_name
            from information_schema.tables
            where table_schema = 'public'
                and table_type = 'BASE TABLE') y
        left join
        (select relname, tgname
            from pg_trigger
            join pg_class on tgrelid=pg_class.oid
            join pg_proc on tgfoid=pg_proc.oid
            where tgname like '%_audit') x
            on relname = table_name;

Some dynamic sql to drop and reapply all the triggers on the tables

do
$$ 
begin 
    execute(select 
        string_agg(
            'drop trigger if exists _audit on ' || 
                quote_ident(t.table_name) || ';' ||
            'create trigger _audit ' ||
                ' after insert or update or delete on ' || 
                    quote_ident(t.table_name) ||
                ' for each row execute procedure if_modified_func();', E'\n') 
        from (select table_name from table_audit_trigger) t); 
end 
$$;

And here's what you get:

db=# select * from logged_action;
 table_name  | user_name |         action_tstamp         | action |            original_data             |               new_data               |                  schema                   | row_id | id 
-------------+-----------+-------------------------------+--------+--------------------------------------+--------------------------------------+-------------------------------------------+--------+----
 patient_lab | nobody    | 2015-06-03 16:24:12.729897+00 | U      | (182,26,1,"2015-06-03 00:00:00",11)  | (182,26,1,"2015-06-03 00:00:00",10)  | {id,patient_id,lab_id,lab_time,lab_value} |    182 | 43
 patient_lab | nobody    | 2015-06-03 16:25:28.695848+00 | I      |                                      | (183,26,30,"2015-06-04 10:25:00",12) | {id,patient_id,lab_id,lab_time,lab_value} |    183 | 44
 patient_lab | nobody    | 2015-06-03 16:25:41.056173+00 | D      | (183,26,30,"2015-06-04 10:25:00",12) |                                      | {id,patient_id,lab_id,lab_time,lab_value} |    183 | 45