redz1029
3cb980b629
Security — enforce write authorization server-side (was UI/RPC-only): - it_service_requests RLS: block cross-office read/edit + self-approve (QA-015) - pass_slips RLS: owner can complete but not self-approve (QA-046) - swap_requests RLS: scope select/update to participants + admin (QA-047) - storage: tighten it_service_attachments + task_attachments write/delete (QA-027) - admin_user_management edge function: allow programmers to manage users (QA-016) Fixes: - workforce generator "uncovered shifts" false alarms (QA-043/044) - network-map VLAN + New-location dialog validation, disabled-until-valid (QA-048) - de-flake time-of-day-dependent dashboard metrics test (QA-045) Toolchain: - upgrade to Flutter 3.47.5 / Dart 3.13.4; font_awesome_flutter 11.0.0, flutter_quill 11.6.0, pdfrx 2.6.5; clear resulting deprecations (QA-002) analyze clean; 139 tests pass; web build succeeds. Report + evidence in docs/qa/. Note: also carries the in-progress Brick model cleanup already present in the working tree. QA-001 (AI keys public in the build) is deferred by owner decision. Co-Authored-By: claude-flow <ruv@ruv.net>
68 lines
3.0 KiB
SQL
68 lines
3.0 KiB
SQL
-- QA-047: swap_requests authorization bypass.
|
|
--
|
|
-- Live REST probes with a non-privileged it_staff token showed:
|
|
-- * SELECT is not scoped: any authenticated user reads EVERY swap (60/60),
|
|
-- including 48 they are neither requester nor recipient of.
|
|
-- * UPDATE is not scoped: the requester could PATCH their OWN swap straight
|
|
-- to status='accepted' (forging the recipient's acceptance), and a total
|
|
-- non-participant could PATCH ANY swap's status/recipient. This bypasses the
|
|
-- identity checks in respond_shift_swap() entirely (same class as QA-015).
|
|
--
|
|
-- The base swap_requests policies live in a migration that predates this repo
|
|
-- ("already exists in many deployments"), so their names aren't known here;
|
|
-- the DO block drops whatever SELECT/INSERT/UPDATE/DELETE policies exist and
|
|
-- this migration then defines the complete, hardened set. Safe because:
|
|
-- * respond_shift_swap() (accept/reject/escalate) is SECURITY DEFINER
|
|
-- (20260322170000+), so it mutates rows regardless of these policies.
|
|
-- * request_shift_swap() inserts with requester_id = auth.uid().
|
|
-- * The only direct table UPDATE the app makes is reassignSwap(), an
|
|
-- admin/dispatcher action (workforce_provider.dart:reassignSwap).
|
|
|
|
ALTER TABLE public.swap_requests ENABLE ROW LEVEL SECURITY;
|
|
|
|
DO $$
|
|
DECLARE
|
|
pol record;
|
|
BEGIN
|
|
FOR pol IN
|
|
SELECT policyname FROM pg_policies
|
|
WHERE schemaname = 'public' AND tablename = 'swap_requests'
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS %I ON public.swap_requests', pol.policyname);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- SELECT: only the two participants and admins/dispatchers. The provider still
|
|
-- filters client-side, so tightening here only removes rows a user shouldn't
|
|
-- have seen in the first place.
|
|
CREATE POLICY "swap_requests: select" ON public.swap_requests
|
|
FOR SELECT TO authenticated USING (
|
|
requester_id = auth.uid()
|
|
OR recipient_id = auth.uid()
|
|
OR EXISTS (SELECT 1 FROM public.profiles p
|
|
WHERE p.id = auth.uid() AND p.role IN ('admin', 'dispatcher'))
|
|
);
|
|
|
|
-- INSERT: a user may only open a swap as themselves (request_shift_swap pins
|
|
-- requester_id to auth.uid()).
|
|
CREATE POLICY "swap_requests: insert" ON public.swap_requests
|
|
FOR INSERT TO authenticated WITH CHECK (
|
|
requester_id = auth.uid()
|
|
);
|
|
|
|
-- UPDATE: only admins/dispatchers may write the row directly (reassignSwap).
|
|
-- Participants accept/reject/escalate through respond_shift_swap(), which is
|
|
-- SECURITY DEFINER and therefore not bound by this policy. This is what closes
|
|
-- the bypass: a requester/recipient can no longer forge status directly.
|
|
CREATE POLICY "swap_requests: update" ON public.swap_requests
|
|
FOR UPDATE TO authenticated USING (
|
|
EXISTS (SELECT 1 FROM public.profiles p
|
|
WHERE p.id = auth.uid() AND p.role IN ('admin', 'dispatcher'))
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.profiles p
|
|
WHERE p.id = auth.uid() AND p.role IN ('admin', 'dispatcher'))
|
|
);
|
|
|
|
-- No DELETE policy: direct deletes stay blocked (unchanged behaviour).
|