Skip to content

Instantly share code, notes, and snippets.

@joshxyzhimself
Created September 18, 2022 11:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save joshxyzhimself/22a3cc49becacb9b09ca92d812bc025b to your computer and use it in GitHub Desktop.
Save joshxyzhimself/22a3cc49becacb9b09ca92d812bc025b to your computer and use it in GitHub Desktop.
supabase 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;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment