supabase-brand-studio-pulls-service-writes

-- DRAFT ONLY. Requires separate approval; not part of the additive chunk migration.
-- Target project: opqdmuynzuikfglyevjc. Scope: public.brand_studio_pulls only.
-- No data updates, ledger changes, new RPCs, worker edits or changes to other tables.
-- Lead: first run the rollback-test copy; do not execute this COMMIT draft yet.
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE public.brand_studio_pulls ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON TABLE public.brand_studio_pulls FROM PUBLIC, anon, authenticated;
DO $column_grants$
DECLARE col record;
BEGIN
  FOR col IN SELECT attname FROM pg_attribute
    WHERE attrelid='public.brand_studio_pulls'::regclass AND attnum>0 AND NOT attisdropped
  LOOP
    EXECUTE format('REVOKE ALL (%I) ON TABLE public.brand_studio_pulls FROM PUBLIC, anon, authenticated',col.attname);
  END LOOP;
END;
$column_grants$;
-- Restrictive policies also deny row DML if another inherited role grants it.
DROP POLICY IF EXISTS release_deny_client_insert ON public.brand_studio_pulls;
CREATE POLICY release_deny_client_insert ON public.brand_studio_pulls AS RESTRICTIVE
  FOR INSERT TO anon, authenticated WITH CHECK (false);
DROP POLICY IF EXISTS release_deny_client_update ON public.brand_studio_pulls;
CREATE POLICY release_deny_client_update ON public.brand_studio_pulls AS RESTRICTIVE
  FOR UPDATE TO anon, authenticated USING (false) WITH CHECK (false);
DROP POLICY IF EXISTS release_deny_client_delete ON public.brand_studio_pulls;
CREATE POLICY release_deny_client_delete ON public.brand_studio_pulls AS RESTRICTIVE
  FOR DELETE TO anon, authenticated USING (false);
-- Existing brand_studio_pulls_select_own policy retains owner-bound reads.
GRANT SELECT ON TABLE public.brand_studio_pulls TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.brand_studio_pulls TO service_role;

-- Fail closed on effective/inherited grants that a direct REVOKE did not remove.
-- TRUNCATE is not protected by RLS. An inherited write privilege needs review,
-- never a broader unreviewed role revoke in this table-only change.
DO $effective_grants$
DECLARE client_role text;
BEGIN
  FOREACH client_role IN ARRAY ARRAY['anon','authenticated'] LOOP
    IF has_table_privilege(client_role,'public.brand_studio_pulls','INSERT')
      OR has_table_privilege(client_role,'public.brand_studio_pulls','UPDATE')
      OR has_table_privilege(client_role,'public.brand_studio_pulls','DELETE')
      OR has_table_privilege(client_role,'public.brand_studio_pulls','TRUNCATE')
      OR has_table_privilege(client_role,'public.brand_studio_pulls','TRIGGER')
      OR has_any_column_privilege(client_role,'public.brand_studio_pulls','INSERT')
      OR has_any_column_privilege(client_role,'public.brand_studio_pulls','UPDATE') THEN
      RAISE EXCEPTION 'STOP: effective client write grants remain for %; entire draft rolled back',client_role;
    END IF;
  END LOOP;
  IF NOT has_table_privilege('authenticated','public.brand_studio_pulls','SELECT')
    OR NOT has_table_privilege('service_role','public.brand_studio_pulls','SELECT')
    OR NOT has_table_privilege('service_role','public.brand_studio_pulls','INSERT')
    OR NOT has_table_privilege('service_role','public.brand_studio_pulls','UPDATE')
    OR NOT has_table_privilege('service_role','public.brand_studio_pulls','DELETE') THEN
    RAISE EXCEPTION 'STOP: required read/service grants missing';
  END IF;
END;
$effective_grants$;
COMMIT;