Postgres SQL Auditing Trigger

audit-trigger.sql

-- Create all objects and audit table

create TABLE t_history (
id serial,
tstamp Timestamp DEFAULT now(),
schemaname text,
tabname text,
operation text,
who text DEFAULT current_user,
new_val jsonb,
old_val jsonb
);

-- Create trigger function

CREATE OR REPLACE FUNCTION change_trigger() RETURNS TRIGGER AS $change$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO public.t_history (tabname, schemaname, operation, new_val) VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO public.t_history (tabname, schemaname, operation, new_val, old_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
row_to_json(NEW), row_to_json(OLD));
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO public.t_history (tabname, schemaname, operation, old_val)
VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
RETURN OLD;
END IF;
END;
$change$ LANGUAGE plpgsql;


-- Replace TABLENAME with the application Postgres table name we want to audit
CREATE TRIGGER TABLENAME
AFTER INSERT OR UPDATE OR DELETE ON oauth2_state
        FOR EACH ROW EXECUTE PROCEDURE change_trigger();

-- Use this statement to copy and audit multiple additional tables

--CREATE TRIGGER TABLENAME-ADDITIONAL 
--AFTER INSERT OR UPDATE OR DELETE ON login_state
--        FOR EACH ROW EXECUTE PROCEDURE change_trigger();

       
-- Query audit logs
       
select * from t_history;



--Save those results before running remaining steps to drop the added audit functions

drop function change_trigger CASCADE;

drop table public.t_history;