How to audit data with triggers in PostgreSQL

Introduction

Auditing data changes can be key for maintaining data integrity and history in database systems. This article walks through the use of triggers in PostgreSQL to create an effective auditing system, providing an extra layer of security and traceability for your data transactions.

What is a Trigger?

In PostgreSQL, a trigger is a function invoked automatically whenever a specified event occurs. Events can be an INSERT, UPDATE, DELETE, or even TRUNCATE action performed on a table. Triggers are critical tools for enforcing complex business rules, maintaining referential data integrity, logging historical changes of data, or auditing user actions.

Setting up the Environment

Before diving into the implementation, we need to set up a sample database and table in PostgreSQL. We’ll be using the psql command-line tool for these operations. Open your terminal and follow these instructions:

-- Connect to your PostgreSQL server
psql -U username -d databasename

-- Create a new table for demonstration
CREATE TABLE employee (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    salary DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ
);

Basic Trigger Creation

Our first example involves creating a simple trigger to log every insert operation performed on the employee table. We need to create an AUDIT table to store audit data and a function that the trigger will execute.

-- Create an audit table
CREATE TABLE audit (
    change_id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    operation VARCHAR(10) NOT NULL,
    changed_by TEXT NOT NULL,
    changed_on TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Create an audit function
CREATE OR REPLACE FUNCTION audit_insert()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO audit(table_name, operation, changed_by, changed_on)
    VALUES (TG_TABLE_NAME, 'INSERT', current_user, now());
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

-- Create a trigger for the employee table
CREATE TRIGGER employee_insert_audit
AFTER INSERT ON employee
FOR EACH ROW EXECUTE FUNCTION audit_insert();

Advanced Trigger for Detailed Auditing

To create a more detailed audit trail, we will log the old and new values for each update operation. We modify our audit table and create a new function:

-- Modify the audit table to store row changes
ALTER TABLE audit ADD COLUMN old_values TEXT;
ALTER TABLE audit ADD COLUMN new_values TEXT;

-- Create a more complex audit function
CREATE OR REPLACE FUNCTION audit_update()
RETURNS TRIGGER AS $
DECLARE
    operation_type TEXT;
    user_name TEXT := current_user;
BEGIN
    IF TG_OP = 'UPDATE' THEN
        operation_type := 'UPDATE';
        INSERT INTO audit(table_name, operation, old_values, new_values, changed_by, changed_on)
        VALUES (TG_TABLE_NAME, operation_type, row_to_json(OLD), row_to_json(NEW), user_name, now());
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        operation_type := 'INSERT';
        INSERT INTO audit(table_name, operation, new_values, changed_by, changed_on)
        VALUES (TG_TABLE_NAME, operation_type, row_to_json(NEW), user_name, now());
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        operation_type := 'DELETE';
        INSERT INTO audit(table_name, operation, old_values, changed_by, changed_on)
        VALUES (TG_TABLE_NAME, operation_type, row_to_json(OLD), user_name, now());
        RETURN OLD;
    END IF;
END;
$ LANGUAGE plpgsql;

-- Create a trigger for the employee table updates
CREATE TRIGGER employee_update_audit
AFTER UPDATE ON employee
FOR EACH ROW EXECUTE FUNCTION audit_update();

Handling Complex Trigger Logic

When the logic gets more complex, it’s beneficial to refine the triggers accordingly. We might want to check for specific conditions before logging or performing more complex operations based on the change:

-- Create a trigger that only logs updates where the salary is changed
CREATE OR REPLACE FUNCTION audit_salary_update()
RETURNS TRIGGER AS $
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO audit(table_name, operation, old_values, new_values, changed_by, changed_on)
        VALUES (TG_TABLE_NAME, 'UPDATE', hstore(OLD), hstore(NEW), current_user, now());
    END IF;
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER employee_salary_update_audit
AFTER UPDATE OF salary ON employee
FOR EACH ROW WHEN (OLD.salary <> NEW.salary)
EXECUTE FUNCTION audit_salary_update();

Conclusion

In conclusion, auditing data with triggers in PostgreSQL is a robust method for tracking changes to your database tables. While triggers offer powerful capabilities for auditing, bear in mind that they can add overhead to database operations and should be carefully designed to minimize performance impact. The strategies discussed in this article should equip you with the knowledge to implement an effective audit system in your PostgreSQL database.