Creating an audit trail

Let's now take a look at the complete example of an event trigger that creates an audit trail of some DDL commands in the database:

CREATE TABLE track_ddl
(
  event text, 
  command text, 
  ddl_time timestamptz, 
  usr text
);

CREATE OR REPLACE FUNCTION track_ddl_function()
RETURNS event_trigger
AS
$$
BEGIN
  INSERT INTO track_ddl values(tg_tag, tg_event, now(), session_user);
  RAISE NOTICE 'DDL logged';
END
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE EVENT TRIGGER track_ddl_event ON ddl_command_start
WHEN TAG IN ('CREATE TABLE', 'DROP TABLE', 'ALTER TABLE')
EXECUTE PROCEDURE track_ddl_function();

CREATE TABLE event_check(i int);

SELECT * FROM track_ddl;

-[ RECORD 1 ]------------------------
event    | CREATE TABLE
command  | ddl_command_start
ddl_time | 2014-04-13 16:58:40.331385
usr      | testusr

The example is actually quite simple. Here's what we have done in the example:

1. First, we created a table where we store the audit log. The table is quite simple at...