Created
September 18, 2022 11:29
Revisions
-
joshxyzhimself revised this gist
Sep 18, 2022 . No changes.There are no files selected for viewing
-
joshxyzhimself created this gist
Sep 18, 2022 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal 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; $$;