Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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:
- ``` sql
- 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:
- ``` sql
- 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!:
- ``` sql
- 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
- ``` sql
- 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
- ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement