53 lines
2.8 KiB
MySQL
53 lines
2.8 KiB
MySQL
|
-- 0005_investigator_write_policies.sql
|
||
|
|
--
|
||
|
|
-- Fix-up on top of 0004: the investigation tables had RLS enabled but ONLY
|
||
|
|
-- a SELECT policy. With RLS active, INSERT/UPDATE/DELETE are blocked unless
|
||
|
|
-- a policy permits them, regardless of GRANT. So the `investigator` role
|
||
|
|
-- could connect and read but its `UPDATE … RETURNING` claim query silently
|
||
|
|
-- returned zero rows — jobs sat in `queued` forever.
|
||
|
|
--
|
||
|
|
-- This migration adds INSERT/UPDATE policies tied to the `investigator` role.
|
||
|
|
-- Public anon/authenticated remain READ-ONLY.
|
||
|
|
--
|
||
|
|
-- Idempotent. Apply as supabase_admin.
|
||
|
|
|
||
|
|
BEGIN;
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────
|
||
|
|
-- investigation_jobs — writes from the runtime + INSERTS from the web admin
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────
|
||
|
|
DROP POLICY IF EXISTS investigation_jobs_insert ON public.investigation_jobs;
|
||
|
|
DROP POLICY IF EXISTS investigation_jobs_update ON public.investigation_jobs;
|
||
|
|
|
||
|
|
-- The web admin creates jobs through the service_role (gated by middleware
|
||
|
|
-- /api/admin/*); the investigator role updates them as it claims and runs.
|
||
|
|
CREATE POLICY investigation_jobs_insert ON public.investigation_jobs
|
||
|
|
FOR INSERT TO investigator, service_role, postgres
|
||
|
|
WITH CHECK (TRUE);
|
||
|
|
CREATE POLICY investigation_jobs_update ON public.investigation_jobs
|
||
|
|
FOR UPDATE TO investigator, service_role, postgres
|
||
|
|
USING (TRUE) WITH CHECK (TRUE);
|
||
|
|
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────
|
||
|
|
-- All 6 investigation tables: writes only by the investigator + service_role.
|
||
|
|
-- ─────────────────────────────────────────────────────────────────────────
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
t TEXT;
|
||
|
|
BEGIN
|
||
|
|
FOREACH t IN ARRAY ARRAY['evidence','hypotheses','contradictions','witnesses','gaps','residual_uncertainties']
|
||
|
|
LOOP
|
||
|
|
EXECUTE format('DROP POLICY IF EXISTS %I_insert ON public.%I', t, t);
|
||
|
|
EXECUTE format('DROP POLICY IF EXISTS %I_update ON public.%I', t, t);
|
||
|
|
EXECUTE format(
|
||
|
|
'CREATE POLICY %I_insert ON public.%I FOR INSERT TO investigator, service_role, postgres WITH CHECK (TRUE)',
|
||
|
|
t, t);
|
||
|
|
EXECUTE format(
|
||
|
|
'CREATE POLICY %I_update ON public.%I FOR UPDATE TO investigator, service_role, postgres USING (TRUE) WITH CHECK (TRUE)',
|
||
|
|
t, t);
|
||
|
|
END LOOP;
|
||
|
|
END
|
||
|
|
$$;
|
||
|
|
|
||
|
|
COMMIT;
|