PostgreSQL Audit with Trigger - "Value-based Auditing (VA)"

1/ Creating a table for Trigger Data:

-- SEQUENCE: audit.audit_log_id_seq

-- DROP SEQUENCE IF EXISTS audit.audit_log_id_seq;

CREATE SEQUENCE IF NOT EXISTS audit.audit_log_id_seq

    INCREMENT 1

    START 1

    MINVALUE 1

    MAXVALUE 9223372036854775807

    CACHE 1;

ALTER SEQUENCE audit.audit_log_id_seq OWNER TO postgres;

GRANT ALL ON SEQUENCE audit.audit_log_id_seq TO postgres;

/

-- Table: audit.audit_log

-- DROP TABLE IF EXISTS audit.audit_log;

CREATE TABLE IF NOT EXISTS audit.audit_log

(

    id bigint NOT NULL

DEFAULT nextval('audit.audit_log_id_seq'::regclass),

    event_time text COLLATE pg_catalog."default",

    client_addr inet,

    application_name text COLLATE pg_catalog."default",

    username text COLLATE pg_catalog."default",

    table_name text COLLATE pg_catalog."default",

    operation text COLLATE pg_catalog."default",

    before_value json,

    after_value json,

    CONSTRAINT audit_log_pkey PRIMARY KEY (id)

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS audit.audit_log OWNER to postgres;

GRANT ALL ON TABLE audit.audit_log TO postgres;

------------------------------ 2/ Creating a Audit Trigger:

-- FUNCTION: audit.audit_trigger()

-- DROP FUNCTION IF EXISTS audit.audit_trigger();

CREATE OR REPLACE FUNCTION audit.audit_trigger()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

DECLARE

    old_row json := NULL;

    new_row json := NULL;

BEGIN

    IF TG_OP IN ('UPDATE','DELETE') THEN

        old_row = row_to_json(OLD);

    END IF;

    IF TG_OP IN ('INSERT','UPDATE') THEN

        new_row = row_to_json(NEW);

    END IF;

    INSERT INTO audit.audit_log(

        client_addr,

        application_name,

        username,

        event_time,

        table_name,

        operation,

        before_value,

        after_value

    ) VALUES (

        inet_client_addr(),

        current_setting('application_name'),

        session_user,

        current_timestamp,

        TG_TABLE_SCHEMA ||  '.' || TG_TABLE_NAME,

        TG_OP,

        old_row,

        new_row

    );

    RETURN NEW;

END;

$BODY$;

ALTER FUNCTION audit.audit_trigger() OWNER TO postgres;

GRANT EXECUTE ON FUNCTION audit.audit_trigger() TO postgres;

------------------ 3/ Insert Trigger to Table "public.salons":

-- Trigger: audit_log__public_salons

-- DROP TRIGGER IF EXISTS audit_log__public_salons ON public.salons;

CREATE TRIGGER audit_log__public_salons

    AFTER INSERT OR DELETE OR UPDATE

    ON public.salons

    FOR EACH ROW

    EXECUTE PROCEDURE audit.audit_trigger();

------------------

4/ Result: SELECT * FROM audit.audit_log ORDER BY id ASC LIMIT 100