Server-Side PL/pgSQL & Audit Triggers: Tamper-Evident Database Compliance

Created: 2026-11-08  ·  Last updated: 2026-11-08  ·  Nejat Hakan

Executive Summary: Application-level audit logging frequently fails compliance standards: rogue administrative sessions, direct psql connections, background migration scripts, and application crashes routinely bypass application loggers. Enforcing business invariants and compliance logging directly inside the database engine guarantees that every data mutation is evaluated within the identical ACID transaction boundary. This guide explores the PL/pgSQL procedural runtime, execution security models (SECURITY DEFINER), and how to construct a generic, append-only tamper-evident audit trail using JSONB diffs.

• PL/pgSQL Architecture

  ---

  Execute procedural logic in-memory right next to storage pages; eliminate network latency and enforce strict transaction safety.

• SECURITY DEFINER Hardening

  ---

  Eliminate privilege escalation exploits: pin SET search_path = pg_catalog, pg_temp when executing privileged database functions.

• BEFORE vs. AFTER Triggers

  ---

  Sanitize and validate incoming values with BEFORE triggers; record immutable historical events with AFTER triggers.

• Generic JSONB Audit Trail

  ---

  Record precise attribute deltas across all tables automatically: log to_jsonb(OLD) vs. to_jsonb(NEW) with session metadata.

---

1. Procedural Execution Model in PostgreSQL

While standard SQL is purely declarative, complex workflows (such as multi-step financial validations, status state-machines, and compliance auditing) require procedural control structures (loops, conditionals, and exception handling).

%%{init: {'flowchart': {'wrappingWidth': 600}}}%%
flowchart TB
    DML["Client DML Operation<br/>(INSERT, UPDATE, DELETE)"]
    TRG_B["1. BEFORE Trigger<br/>Validates & Modifies NEW Record"]
    HEAP["2. Physical Heap Write<br/>Applies Row Mutation into 8 KiB Page"]
    TRG_A["3. AFTER Trigger<br/>Fires Audit Logging Function"]
    AUDIT["4. Append-Only Audit Table<br/>Records JSONB Diffs Atomically"]
    COMMIT["5. Transaction Commit<br/>Both Mutation & Audit Log Persisted"]

    DML --> TRG_B
    TRG_B --> HEAP
    HEAP --> TRG_A
    TRG_A --> AUDIT
    AUDIT --> COMMIT

Functions vs. Stored Procedures

In modern PostgreSQL, functions and procedures serve distinct architectural roles:

Architectural Metric	User-Defined Function (FUNCTION)	Stored Procedure (PROCEDURE)
Invocation Syntax	SELECT my_func(arg);	CALL my_proc(arg);
Return Value	Must return a value, table, or VOID	Does not return values directly (uses INOUT)
Transaction Control	Cannot commit or rollback (runs inside outer transaction)	Can commit or rollback (COMMIT, ROLLBACK in body)
Use Case	Data transformations, trigger handlers, calculated columns	Long-running batch migrations, ETL partition swaps

---

2. Hardening SECURITY DEFINER Functions

By default, functions execute with the privileges of the calling user (SECURITY INVOKER). A function declared with SECURITY DEFINER executes with the privileges of the role that created the function (analogous to the setuid binary bit in Linux).

The search_path Privilege Escalation Vulnerability

If an attacker creates a malicious table or operator in a temporary schema and calls a SECURITY DEFINER function, the function may execute the attacker's object with superuser privileges. Always explicitly set search_path on every SECURITY DEFINER function!

-- Production Best Practice: Secure Function Definition
CREATE OR REPLACE FUNCTION fn_secure_operation()
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
-- Pin search_path strictly to prevent untrusted schema overrides
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
    -- Privileged operations execute safely here
END;
$$;

---

3. Trigger Mechanics: Lifecycle & Special Variables

A trigger executes an associated PL/pgSQL function automatically whenever a specified event occurs on a table: - BEFORE Triggers: Fire before the row mutation is committed to the physical table heap. Can inspect and modify the NEW record or raise an exception to abort the transaction. - AFTER Triggers: Fire after the row mutation is written. Ideal for inserting audit records or updating denormalized counters. - INSTEAD OF Triggers: Fire on views that cannot be updated automatically, translating writes into operations on underlying tables.

Key Trigger Execution Variables

Inside a trigger function, PostgreSQL provides contextual variables:

Variable	Type	Description
NEW	RECORD	The proposed row version for INSERT or UPDATE operations. (NULL on DELETE).
OLD	RECORD	The previous row version for UPDATE or DELETE operations. (NULL on INSERT).
TG_OP	TEXT	The SQL operation that fired the trigger: 'INSERT', 'UPDATE', or 'DELETE'.
TG_TABLE_NAME	TEXT	The name of the table on which the trigger fired.
TG_WHEN	TEXT	Timing: 'BEFORE', 'AFTER', or 'INSTEAD OF'.

---

4. Hands-on Lab: Generic Tamper-Evident JSONB Audit Trail

Build a generic, enterprise-grade audit logging system that records row-level diffs across any table without writing repetitive code:

-- File: /opt/db-ops/tamper_evident_audit_lab.sql
BEGIN;

-- 1. Create the centralized, append-only security audit table
CREATE TABLE security_audit_log (
    audit_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name      TEXT NOT NULL,
    operation       VARCHAR(8) NOT NULL,
    db_user         TEXT NOT NULL DEFAULT session_user,
    client_addr     INET DEFAULT inet_client_addr(),
    changed_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    old_data        JSONB,
    new_data        JSONB
);

-- 2. Restrict permissions: Applications may NEVER modify or delete historical logs!
REVOKE UPDATE, DELETE, TRUNCATE ON security_audit_log FROM PUBLIC;

-- 3. Define the generic PL/pgSQL trigger function
CREATE OR REPLACE FUNCTION fn_generic_audit_trail()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO public.security_audit_log (
            table_name, operation, old_data, new_data
        ) VALUES (
            TG_TABLE_NAME, TG_OP, to_jsonb(OLD), NULL
        );
        RETURN OLD;

    ELSIF (TG_OP = 'UPDATE') THEN
        -- Only record an audit entry if the payload actually changed
        IF to_jsonb(OLD) IS DISTINCT FROM to_jsonb(NEW) THEN
            INSERT INTO public.security_audit_log (
                table_name, operation, old_data, new_data
            ) VALUES (
                TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW)
            );
        END IF;
        RETURN NEW;

    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO public.security_audit_log (
            table_name, operation, old_data, new_data
        ) VALUES (
            TG_TABLE_NAME, TG_OP, NULL, to_jsonb(NEW)
        );
        RETURN NEW;
    END IF;

    RETURN NULL;
END;
$$;

-- 4. Attach the trigger to the products table
CREATE TRIGGER trg_audit_products
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION fn_generic_audit_trail();

COMMIT;

Testing Audit Immutability in psql

Execute an update and inspect the resulting JSONB audit record:

-- Update product stock and price
UPDATE products 
SET price_cents = 19900, stock_qty = 35 
WHERE sku = 'SRV-NVME-2TB';

-- Query the audit trail:
SELECT 
    audit_id, 
    table_name, 
    operation, 
    db_user, 
    changed_at,
    old_data->>'price_cents' AS old_price,
    new_data->>'price_cents' AS new_price
FROM security_audit_log
ORDER BY audit_id DESC
LIMIT 1;