mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
91 lines
3.1 KiB
SQL
91 lines
3.1 KiB
SQL
|
|
-- ============================================================
|
|
-- 1. FIX: owners table - restrict homeowner SELECT to own record
|
|
-- ============================================================
|
|
DROP POLICY IF EXISTS "Members can view own association owners" ON public.owners;
|
|
|
|
CREATE POLICY "Members can view own owner record"
|
|
ON public.owners FOR SELECT TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
-- ============================================================
|
|
-- 2. FIX: violations table - restrict homeowner SELECT to own violations
|
|
-- ============================================================
|
|
DROP POLICY IF EXISTS "Members can view own association violations" ON public.violations;
|
|
|
|
CREATE POLICY "Members can view own violations"
|
|
ON public.violations FOR SELECT TO authenticated
|
|
USING (
|
|
owner_id IN (
|
|
SELECT id FROM public.owners WHERE user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 3. FIX: files storage bucket - remove anon access, make private
|
|
-- ============================================================
|
|
|
|
-- Make the bucket private
|
|
UPDATE storage.buckets SET public = false WHERE id = 'files';
|
|
|
|
-- Remove anonymous read policy
|
|
DROP POLICY IF EXISTS "Public can read files bucket" ON storage.objects;
|
|
|
|
-- Remove overly broad authenticated policies and replace with role-scoped ones
|
|
DROP POLICY IF EXISTS "Authenticated users can read files bucket" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Authenticated users can upload to files bucket" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Authenticated users can update files bucket" ON storage.objects;
|
|
DROP POLICY IF EXISTS "Authenticated users can delete from files bucket" ON storage.objects;
|
|
|
|
CREATE POLICY "Staff can read files bucket"
|
|
ON storage.objects FOR SELECT TO authenticated
|
|
USING (
|
|
bucket_id = 'files'
|
|
AND (
|
|
has_role(auth.uid(), 'admin'::public.app_role)
|
|
OR has_role(auth.uid(), 'manager'::public.app_role)
|
|
OR has_role(auth.uid(), 'employee'::public.app_role)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Staff can upload to files bucket"
|
|
ON storage.objects FOR INSERT TO authenticated
|
|
WITH CHECK (
|
|
bucket_id = 'files'
|
|
AND (
|
|
has_role(auth.uid(), 'admin'::public.app_role)
|
|
OR has_role(auth.uid(), 'manager'::public.app_role)
|
|
OR has_role(auth.uid(), 'employee'::public.app_role)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Staff can update files bucket"
|
|
ON storage.objects FOR UPDATE TO authenticated
|
|
USING (
|
|
bucket_id = 'files'
|
|
AND (
|
|
has_role(auth.uid(), 'admin'::public.app_role)
|
|
OR has_role(auth.uid(), 'manager'::public.app_role)
|
|
OR has_role(auth.uid(), 'employee'::public.app_role)
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
bucket_id = 'files'
|
|
AND (
|
|
has_role(auth.uid(), 'admin'::public.app_role)
|
|
OR has_role(auth.uid(), 'manager'::public.app_role)
|
|
OR has_role(auth.uid(), 'employee'::public.app_role)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Staff can delete from files bucket"
|
|
ON storage.objects FOR DELETE TO authenticated
|
|
USING (
|
|
bucket_id = 'files'
|
|
AND (
|
|
has_role(auth.uid(), 'admin'::public.app_role)
|
|
OR has_role(auth.uid(), 'manager'::public.app_role)
|
|
OR has_role(auth.uid(), 'employee'::public.app_role)
|
|
)
|
|
);
|