Click here to Skip to main content
15,867,686 members
Articles / .NET
Article

Audit Trail for Postgres

Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
20 Jan 2009CPOL3 min read 39.6K   16   8
Discussion of audit trail implementation for the Postrgres database

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.

SQL
//
// 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.

SQL
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.

SQL
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.

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
I am a Director of Engineering, have an MBA and work in C# forms, Asp.Net and vb.net. I have been writing Windows program since windows 3.0. I am currently working in the Healthcare industry.

I enjoy reading, music (most types), and learning new technology. I am involved in opensource projects at codeplex.

My linkedin link is
http://www.linkedin.com/in/donsweitzer

Comments and Discussions

 
QuestionPHP script to generate the audit trail tables based on this article Pin
Douglas Pasqua28-Feb-13 2:57
Douglas Pasqua28-Feb-13 2:57 
QuestionExtend Pin
hamedmirzaei23-Oct-12 3:59
hamedmirzaei23-Oct-12 3:59 
AnswerRe: Extend Pin
Donsw24-Oct-12 0:25
Donsw24-Oct-12 0:25 
GeneralRe: Extend Pin
hamedmirzaei24-Oct-12 1:16
hamedmirzaei24-Oct-12 1:16 
GeneralNice work! Pin
wtwhite28-Jan-09 16:07
wtwhite28-Jan-09 16:07 
GeneralRe: Nice work! Pin
Donsw29-Jan-09 1:35
Donsw29-Jan-09 1:35 
although I would love to take full credit I can not. It is a variant of a procedure I found in a postgreSQL manual. I too thought it was very clean and nice. thanks.
Questionreferential integrity? Pin
M Towler27-Jan-09 22:06
M Towler27-Jan-09 22:06 
AnswerRe: referential integrity? Pin
Donsw28-Jan-09 5:24
Donsw28-Jan-09 5:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.