Audit Trail for Postgres

Introduction

This article is how to implement audit tables in PostgresSQL. It is different than some of the other databases.

Background

Cedric Baelemans wrote about using audit tables or the term he used was Shadow tables in his article Audit Trail Generator for Microsoft SQL. I decided to do the same for PostgresSQL to see how it was done in that database.

Using the Code

Most databases will have a trigger per database operation type. With that implementation, you will need a lot of triggers and code to do the work. This implementation using PostgresSQL you only need one function per table. Below I show the two table definitions for the primary and the audit table.

//
// The primary table 
//
CREATE TABLE MinUser (
	User_Id   			UUID NOT NULL PRIMARY KEY,
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50),
	UNIQUE (User_Name)
) WITH (OIDS=FALSE);
//
// The Audit table 
//
CREATE TABLE MinUser_Audit (
	AuditUser_Id   			Serial PRIMARY KEY,
        operation 		        char(1)   NOT NULL,
        stamp            		timestamp NOT NULL,
        userid            		text      NOT NULL,
	User_Id   			UUID NOT NULL,
	User_Name 			varchar (50) NOT NULL,
	User_Password			varchar (50) NOT NULL,
	User_Email			varchar (50),
	User_Role 			varchar (50)
) WITH (OIDS=FALSE);

With PostgresSQL, a Function is called when a trigger is fired. I set this to fire the same function for Update, Insert, and Delete on each table. In the function, I check to see what the operation is and then write to the audit table. This function is identical to the one in the documentation that comes with PostgresSQL except my tables have a serial key, which made the sample code fail.

CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
    BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), user, NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$usr_audit$ LANGUAGE plpgsql;

The original code in the documentation had a SELECT to get the old data into the audit record. The code is shown below to show the differences. Since I am using a serial key you must use the DEFAULT keyword to get the serial to work or move each column independently. This could have been done by doing OLD.User_Id and OLD.User_Name, etc for all of the data members.

INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;

There are a few special operators are used in the function. The meanings are listed below

• NEW      Data type RECORD; variable holding the new database row for INSERT/UPDATE operations.
• OLD       Data type RECORD; variable holding the old database row for UPDATE/DELETE operations.
• TG_OP   Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired.

For the complete set of keywords, please refer to the PostgresSQL documentation. The next thing to do is get it to fire. To do this you have to add a trigger. When you call the CREATE TRIGGER SQL you have to state if it is BEFORE or AFTER the change, and the type of operation you want to have this fire for. In this particular application, I want to fire it for the INSERT, UPDATE, and DELETE operations.

CREATE TRIGGER MinUser_auditt AFTER INSERT OR UPDATE OR DELETE ON MinUser 
    FOR EACH ROW EXECUTE PROCEDURE MinUser_audit();

Now you can certainly do this with an implementation of having a separate trigger for each operation and table but that will create a lot of functions and triggers. This implementation only requires one for each table that you want to have a audit trail of.

Points of Interest

There are a few articles in Code Project on triggers that you should read if you are interested in triggers. There are also different implementations. One implementation will copy the record as I have done, another will only copy the changed items. Each has their own merits. You also do not have to have an audit on every table, only the ones you want to see the changes.

History

Jan 20 2009 First article