supabase audit.sql

audit.sql

-- 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;
$$;