an audit log for data changes - postgresql

add_trigger_to_table.sql

# 'schema' aka 'public' and 'table_name' aka the table name you wish to add audit logging to
DROP TRIGGER schema_table_name_audit_logging ON schema.table_name;

CREATE TRIGGER table_name_audit_logging
    AFTER INSERT OR UPDATE OR DELETE
    ON schema.table_name
    FOR EACH ROW
EXECUTE PROCEDURE logging.log_data_changes();

table_audit_log.sql

# drop schema if exists logging cascade;
create schema logging;
revoke create on schema logging from public;

create table logging.table_audit_log
(
    schema_name      text                     not null,
    table_name       text                     NOT NULL,
    change_by        text,
    action           TEXT                     NOT NULL CHECK (action IN ('INSERT', 'DELETE', 'UPDATE')),
    action_timestamp TIMESTAMP WITH TIME zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    original_data    jsonb,
    new_data         jsonb,
    query            text
) with (fillfactor = 100);

REVOKE ALL ON logging.table_audit_log FROM public;

GRANT SELECT ON logging.table_audit_log TO public;

CREATE INDEX table_audit_log_schema_table_idx
    ON logging.table_audit_log (((schema_name || '.' || table_name)::text));

CREATE INDEX table_audit_log_action_timestamp_idx
    ON logging.table_audit_log (action_timestamp);

CREATE INDEX table_audit_log_action_idx
    ON logging.table_audit_log (action);


CREATE OR REPLACE FUNCTION logging.log_data_changes() RETURNS TRIGGER AS
$body$
DECLARE
    v_old_data  JSON;
    v_new_data  JSON;
    v_change_by text := current_setting('myvars.auth_user_email', true);
BEGIN
    if (v_change_by is null) then
        v_change_by := CURRENT_USER;
    end if;

    IF (TG_OP = 'UPDATE') THEN
        v_old_data := row_to_json(OLD.*);
        v_new_data := row_to_json(NEW.*);
        INSERT INTO logging.table_audit_log (schema_name, table_name, change_by, action, original_data, new_data, query)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, v_change_by::TEXT, TG_OP, v_old_data,
                v_new_data, current_query());
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := row_to_json(OLD.*);
        INSERT INTO logging.table_audit_log (schema_name, table_name, change_by, action, original_data, query)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, v_change_by::TEXT, TG_OP, v_old_data,
                current_query());
        RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := row_to_json(NEW.*);
        INSERT INTO logging.table_audit_log (schema_name, table_name, change_by, action, new_data, query)
        VALUES (TG_TABLE_SCHEMA::TEXT, TG_TABLE_NAME::TEXT, v_change_by::TEXT, TG_OP, v_new_data,
                current_query());
        RETURN NEW;
    ELSE
        RAISE WARNING '[logging.log_data_changes] - Other action occurred: %, at %',TG_OP,now();
        RETURN NULL;
    END IF;

EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[logging.log_data_changes] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[logging.log_data_changes] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN OTHERS THEN
        RAISE WARNING '[logging.log_data_changes] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
END ;
$body$
    LANGUAGE plpgsql
    SECURITY DEFINER
    SET search_path = pg_catalog, logging;