A simple audit system

Let’s suppose we want to audit all insert, update and delete committed on a database.

We create a table to store relevant data :

drop table if exists audit_history;
create table audit_history (
  id_audit_history       serial
 ,table_name             text
 ,operation              text
 ,audit_id               bigint
 ,user_name              text
 ,insertion_date         timestamp       default now()
);

drop sequence if exists audit_history_seq;
create sequence audit_history_seq;

We create two test tables with a pk constraints and one having a fk constraint to the other. Note that this fk constraint has the clause on delete cascade.

drop table if exists app_table_ref;
drop table if exists app_table;

create table app_table (
  pk             integer         primary key
 ,info           varchar
 ,audit_id       bigserial
);

create table app_table_ref (
  pk             integer         primary key
 ,info           varchar
 ,fk             integer         
          references app_table(pk)        
          on delete cascade
 ,audit_id       bigserial
);

Note the presence of the column audit_id, a bigserial that is not a pk.

We create now the trigger function, a generic fonction for the two triggers : after insert or update and before delete.

create or replace function populate_audit_history() 
   returns trigger as $$
begin

  if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then

      insert into audit_history
        (table_name, operation, audit_id, user_name)
      values
        (TG_TABLE_NAME, TG_OP, new.audit_id, current_user);

      return new;

  else
  
     insert into audit_history
        (table_name, operation, audit_id, user_name)
      values
        (TG_TABLE_NAME, TG_OP, old.audit_id, current_user);

     return old;

  end if;
end;
$$ language 'plpgsql';


create or replace function create_audit_triggers() 
   returns void as $$
declare
   r       record;
   stmt    varchar;
begin
  for r in select table_name from information_schema.tables 
              where table_name like 'app_table%' loop

     stmt :=    'create trigger '
             || quote_ident(r.table_name || '_audit_after_iu')
             || ' after insert or update on '
             || quote_ident(r.table_name)
             || ' for each row 
                    execute procedure populate_audit_history();';

     execute stmt;
   
     stmt :=    'create trigger '
             || quote_ident(r.table_name || '_audit_before_d')
             || ' before delete on '
             || quote_ident(r.table_name)
             || ' for each row 
                    execute procedure populate_audit_history();';

     execute stmt;

   end loop;
end;
$$ language 'plpgsql';

select create_audit_triggers();

Note the execute instruction that allows us to execute dynamic SQL statements.

We do now some transactions on those two tables :

insert into app_table values(23,'record 23');
insert into app_table values(56,'record 56');
insert into app_table values(71,'record 71');
insert into app_table values(82,'record 82');
insert into app_table values(85,'record 85');
insert into app_table values(91,'record 91');
insert into app_table values(94,'record 94');
insert into app_table values(97,'record 97');
insert into app_table values(99,'record 99');
update app_table set info = 'modified' where pk = 23;
update app_table set info = 'modified' where pk = 56;
update app_table set info = 'modified' where pk = 97;
delete from app_table where pk = 71;
insert into app_table values(101,'record 101');
insert into app_table values(121,'record 121');
insert into app_table values(167,'record 167');
update app_table set info = 'modified' where pk = 101;
delete from app_table where pk = 121;
insert into app_table_ref values(1, 'ref1', 23);
insert into app_table_ref values(2, 'ref2', 23);
insert into app_table_ref values(3, 'ref3', 82);
delete from app_table where pk = 23;

Here is the table audit_history :

1  | app_table     | INSERT |  1 | mchl | 2017-02-08 15:01:10.28164
2  | app_table     | INSERT |  2 | mchl | 2017-02-08 15:01:10.292839
3  | app_table     | INSERT |  3 | mchl | 2017-02-08 15:01:10.304088
4  | app_table     | INSERT |  4 | mchl | 2017-02-08 15:01:10.315051
5  | app_table     | INSERT |  5 | mchl | 2017-02-08 15:01:10.32618
6  | app_table     | INSERT |  6 | mchl | 2017-02-08 15:01:10.337284
7  | app_table     | INSERT |  7 | mchl | 2017-02-08 15:01:10.348366
8  | app_table     | INSERT |  8 | mchl | 2017-02-08 15:01:10.35954
9  | app_table     | INSERT |  9 | mchl | 2017-02-08 15:01:10.370595
10 | app_table     | UPDATE |  1 | mchl | 2017-02-08 15:01:10.381727
11 | app_table     | UPDATE |  2 | mchl | 2017-02-08 15:01:10.392824
12 | app_table     | UPDATE |  8 | mchl | 2017-02-08 15:01:10.403822
13 | app_table     | DELETE |  3 | mchl | 2017-02-08 15:01:10.414956
14 | app_table     | INSERT | 10 | mchl | 2017-02-08 15:01:10.4261
15 | app_table     | INSERT | 11 | mchl | 2017-02-08 15:01:10.437168
16 | app_table     | INSERT | 12 | mchl | 2017-02-08 15:01:10.448195
17 | app_table     | UPDATE | 10 | mchl | 2017-02-08 15:01:10.459344
18 | app_table     | DELETE | 11 | mchl | 2017-02-08 15:01:10.470463
19 | app_table_ref | INSERT |  1 | mchl | 2017-02-08 15:01:10.481536
20 | app_table_ref | INSERT |  2 | mchl | 2017-02-08 15:01:10.49259
21 | app_table_ref | INSERT |  3 | mchl | 2017-02-08 15:01:10.503579
22 | app_table     | DELETE |  1 | mchl | 2017-02-08 15:01:10.514913
23 | app_table_ref | DELETE |  1 | mchl | 2017-02-08 15:01:10.514913
24 | app_table_ref | DELETE |  2 | mchl | 2017-02-08 15:01:10.514913

And here the table app_table :

  56 | modified   |        2
  82 | record 82  |        4
  85 | record 85  |        5
  91 | record 91  |        6
  94 | record 94  |        7
  97 | modified   |        8
  99 | record 99  |        9
 101 | modified   |       10
 167 | record 167 |       12

and the table app_table_ref :

  3 | ref3 | 82 |        3

We note that the delete in app_table of the record having pk = 23 has deleted also the two records of app_table_ref having fk = 23.

This design is very handy because you can add auditing on an existing database by adding a to each table : 1/  a column serial (that is not a pk) and 2/  the two triggers (after the creation of the generic function).

The applications are not touched and the referential integrity of the database is preserved.

Having for each modifications, the name of the table and the value of the audit_id, we can find, for insert and update, the rows that have changed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s