Building an Audit Log in PostgreSQL

In any application, tracking changes in the database is often crucial for accountability and transparency. Whether it's for debugging, security, or compliance, knowing what changes were made, by whom, and when can be essential. In this blog, we will explore how to create an Audit Log in PostgreSQL that tracks every INSERT, UPDATE, and DELETE operation on tables using triggers and a dedicated audit log table.

We'll cover:

1. Designing the Audit Table

2. Creating the Audit Function

3. Implementing Triggers for Auditing

4. Demonstration with Sample Tables

5. Conclusion

1. Designing the Audit Table

The first step in building an audit log is to design the table where we'll store all the change data. Here's how our audit table looks:

CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,               -- Auto-incrementing ID for the audit log entry
    entity_name VARCHAR(50) NOT NULL,       -- Name of the table being audited
    entity_id BIGINT NOT NULL,              -- ID of the entity being modified
    version INT NOT NULL,                   -- Version of the data (to track changes over time)
    old_data JSONB,                         -- Snapshot of the old data (before update or delete)
    new_data JSONB,                         -- Snapshot of the new data (after insert or update)
    action VARCHAR(20) NOT NULL,            -- Type of action (INSERT, UPDATE, DELETE)
    updated_on BIGINT NOT NULL              -- Timestamp of when the action took place
);

Here, we store critical information for auditing:

• entity_name: The name of the table on which the action was performed.

• version: Helps keep track of data versions after multiple updates.

• old_data & new_data: JSONB fields for storing the old and new records.

• action: Specifies if it was an INSERT, UPDATE, or DELETE operation.

• updated_on: An epoch timestamp to record when the change occurred.

2. Creating the Audit Function

Next, we need a function that writes to this audit_logs table whenever a change occurs in one of our target tables. This function, written in PL/pgSQL, dynamically handles all three types of operations: INSERT, UPDATE, and DELETE.

CREATE OR REPLACE FUNCTION audit_log_fxn()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
        VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), NULL, 'DELETE', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), OLD.version);
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
        VALUES (TG_TABLE_NAME, OLD.id, row_to_json(OLD), row_to_json(NEW), 'UPDATE', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), NEW.version);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO audit_logs(entity_name, entity_id, old_data, new_data, action, updated_on, version)
        VALUES (TG_TABLE_NAME, NEW.id, NULL, row_to_json(NEW), 'INSERT', EXTRACT(EPOCH FROM CURRENT_TIMESTAMP), COALESCE(NEW.version, 1));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

In this function:

• For INSERT, it records only the new_data.

• For UPDATE, both old_data and new_data are logged.

• For DELETE, only old_data is logged.

The trigger automatically populates the entity_name with the name of the table using TG_TABLE_NAME, and we use EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) to store the timestamp in UNIX format.

3. Implementing Triggers for Auditing

We now create triggers that will invoke the audit_log_fxn function whenever an INSERT, UPDATE, or DELETE happens on specific tables. Let's assume we have three tables: student, faculty, and courses.

Create Tables

CREATE TABLE student (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    version INT DEFAULT 1
);

CREATE TABLE faculty (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    version INT DEFAULT 1
);

CREATE TABLE courses (
    id BIGSERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    credits INT,
    version INT DEFAULT 1
);

CREATE OR REPLACE TRIGGER student_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON student
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();

Trigger for the faculty Table

CREATE OR REPLACE TRIGGER faculty_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON faculty
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();

Trigger for the courses Table

CREATE OR REPLACE TRIGGER courses_audit_trigger
AFTER UPDATE OR DELETE OR INSERT ON courses
FOR EACH ROW
EXECUTE FUNCTION audit_log_fxn();

These triggers ensure that whenever a row in the student, faculty, or courses table is modified, the audit function is called, and the changes are logged in the audit_logs table.

4. Demonstration with Sample Tables

Let's create the student, faculty, and courses tables, perform some operations, and see how the audit log captures them.

Insert Operation

-- Insert into student table
INSERT INTO student (name, age) VALUES ('Alice', 21);

-- Update record in student table
UPDATE student SET age = 22 WHERE name = 'Alice';

-- Delete record from student table
DELETE FROM student where name = 'Alice';

-- Insert into faculty table
INSERT INTO faculty (name, department) VALUES ('Dr. Smith', 'Mathematics');

-- Update record in faculty table
UPDATE faculty SET department = 'Physics' WHERE name = 'Dr. Smith';

-- Delete record from faculty table
DELETE FROM faculty WHERE name = 'Dr. Smith';

-- Insert into courses table
INSERT INTO courses (course_name, credits) VALUES ('Algebra', 4);

-- Update record in courses table
UPDATE courses SET credits = 5 WHERE course_name  = 'Algebra';

-- Delete record from courses table
DELETE FROM courses WHERE course_name  = 'Algebra';

Audit log will capture this as:

5. Conclusion

By setting up an audit log with PostgreSQL triggers, we can ensure that every data modification—whether it's an INSERT, UPDATE, or DELETE—is recorded systematically. This not only provides transparency but also allows for efficient debugging and tracking of application behaviour. The use of JSONB fields for old_data and new_data gives us flexibility to capture all the changes in a structured format.

With this approach, you now have a robust auditing mechanism for your PostgreSQL database, easily adaptable to any use case where data integrity and traceability are essential.