Quick guide to Auditing a (postgreSQL) Database: putting it all together

Posted on by

On my previous post, I suggested how to create a schema, a table and a trigger function, in order to audit a PostgreSQL database.
To audit a table, you would have to create a trigger for that table, calling the code from the generic trigger.
In my case, I want to audit every table in the database, and I think most people will likely want to audit every table, or at least most tables in the database.
To escape the tedious task of writing code to implement that n-times, I put together a script that will generate an audit trigger for each table in the database.If you want to apply it to a restricted number of tables instead, you could easily change it to read the table names from a list.

CREATE OR REPLACE FUNCTION create_audit_triggers()
  RETURNS void AS
$BODY$  
 DECLARE 
 r RECORD; 
 _string varchar ( 1000 );	
  BEGIN


FOR r IN SELECT distinct tablename FROM pg_catalog.pg_tables where schemaname='public'  LOOP

	IF NOT EXISTS(SELECT *
			     FROM information_schema.triggers
			     WHERE event_object_table = r.tablename
			     AND trigger_name = r.tablename || '_audit'
			     )
			    THEN

				--raise info '%' , r.tablename;
				_string :=' CREATE TRIGGER ' || r.tablename || '_audit ' ||
				' AFTER INSERT OR UPDATE OR DELETE ON ' || r.tablename ||
				' FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();';
				raise info '%', _string; 
				EXECUTE ( _string ) ; 	

	END IF ; 

end loop;

  END;
  $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_info_tables2()
  OWNER TO postgres;

This will check if the trigger already exists (for which an error would be raised!), and generate the triggers during the blink of an eye (depending on the size of your database!). Thus you could use it for updating the triggers, after you added a couple of tables in the database.

Leave a comment