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 the moment, due to a limited amount of information that is currently available to a PL/pgSQL function. We store the tag, the event, the timestamp when this trigger is executed, and the user who executed the command.
  2. We then create a function that is executed by the trigger whenever it is fired. The function is simple enough for now. It must return the type EVENT_TRIGGER. It logs the DDL information in the audit trail table. The function created is SECURITY DEFINER. The reason why this is done is because other users in the database don't have any privileges on the audit trail table and we don't actually want them to know it is there. This function is executed as the definer (which is a superuser), and the use of session_user ensures that we log the user who logged in to the database, and not the one whose privileges are used to execute the function.
  3. We then create an event trigger that only executes when certain commands such as CREATE, DROP, or ALTER TABLE are executed by a user.

We then create an example table and note that the command is indeed logged in the audit trail table.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.22.249.220