Skip to content

Instantly share code, notes, and snippets.

@joshxyzhimself
Created September 18, 2022 11:29

Revisions

  1. joshxyzhimself revised this gist Sep 18, 2022. No changes.
  2. joshxyzhimself created this gist Sep 18, 2022.
    82 changes: 82 additions & 0 deletions audit.sql
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,82 @@
    -- References
    --
    -- https://www.pgaudit.org/
    -- https://github.com/pgaudit/pgaudit
    --
    -- https://supabase.com/blog/audit
    -- https://github.com/supabase/supa_audit
    -- https://news.ycombinator.com/item?id=30615470
    --
    -- https://github.com/2ndQuadrant/audit-trigger
    -- https://github.com/cmabastar/audit-trigger
    --
    -- https://wiki.postgresql.org/wiki/Audit_trigger_91plus
    --
    -- https://www.postgresql.org/docs/current/plpgsql-trigger.html
    --
    -- DROP TRIGGER IF EXISTS on_insert_update_delete ON public.user_roles CASCADE;
    --
    -- CREATE TRIGGER on_insert_update_delete
    -- AFTER INSERT OR UPDATE OR DELETE ON public.user_roles
    -- FOR EACH ROW EXECUTE PROCEDURE insert_log();
    --

    DROP TYPE IF EXISTS "operation" CASCADE;

    DROP TABLE IF EXISTS "logs" CASCADE;

    DROP FUNCTION IF EXISTS insert_log CASCADE;

    CREATE TYPE "operation" AS ENUM ('INSERT', 'UPDATE', 'DELETE');

    CREATE TABLE "logs" (
    "id" uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    "table_oid" oid NOT NULL,
    "table_schema" text NOT NULL,
    "table_name" text NOT NULL,
    "table_operation" operation NOT NULL,
    "row_id" uuid NOT NULL,
    "row_data" jsonb NOT NULL,
    "timestamp" timestamptz DEFAULT now() NOT NULL
    );

    ALTER TABLE "logs" ENABLE ROW LEVEL SECURITY;
    CREATE POLICY "logs-select" ON "logs" AS PERMISSIVE
    FOR SELECT TO authenticated USING (
    is_authorized(auth.uid(), 'logs', 'read') = true
    );

    CREATE INDEX "logs_oid" ON "logs" USING BTREE("table_oid");
    CREATE INDEX "logs_ts" ON "logs" USING BRIN("timestamp");

    CREATE FUNCTION insert_log ()
    RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER
    AS $$
    declare
    row_data jsonb = to_jsonb(COALESCE(new, old));
    begin
    INSERT INTO "logs" ("table_oid", "table_schema", "table_name", "table_operation", "row_id", "row_data")
    SELECT TG_RELID, TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP::operation, COALESCE(new.id, old.id), row_data;
    return COALESCE(new, old);
    end;
    $$;

    DO LANGUAGE plpgsql $$
    declare
    t record;
    begin
    FOR t IN
    SELECT * FROM information_schema.tables
    WHERE "table_schema" = 'public' AND "table_type" = 'BASE TABLE' AND "table_name" != 'logs'
    loop
    EXECUTE format('
    DROP TRIGGER IF EXISTS on_insert_update_delete ON %I.%I CASCADE;
    ', t.table_schema, t.table_name);
    EXECUTE format('
    CREATE TRIGGER on_insert_update_delete
    AFTER INSERT OR UPDATE OR DELETE ON %I.%I
    FOR EACH ROW EXECUTE PROCEDURE insert_log();
    ', t.table_schema, t.table_name);
    end loop;
    end;
    $$;