mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
91 lines
3.7 KiB
SQL
91 lines
3.7 KiB
SQL
|
|
-- 1. amenity_bookings: anon INSERT scoped to valid amenity+association
|
|
DROP POLICY IF EXISTS "Anon can insert bookings" ON public.amenity_bookings;
|
|
CREATE POLICY "Anon can insert bookings" ON public.amenity_bookings
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM public.amenities a
|
|
WHERE a.id = amenity_id AND a.association_id = amenity_bookings.association_id AND a.is_active = true
|
|
)
|
|
);
|
|
|
|
-- 2. amenity_form_submissions: anon INSERT scoped to valid amenity+association
|
|
DROP POLICY IF EXISTS "Anon can insert form submissions" ON public.amenity_form_submissions;
|
|
CREATE POLICY "Anon can insert form submissions" ON public.amenity_form_submissions
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM public.amenities a
|
|
WHERE a.id = amenity_id AND a.association_id = amenity_form_submissions.association_id AND a.is_active = true
|
|
)
|
|
);
|
|
|
|
-- 3. bill_comments: authenticated INSERT must match auth.uid()
|
|
DROP POLICY IF EXISTS "Authenticated users can insert bill comments" ON public.bill_comments;
|
|
CREATE POLICY "Authenticated users can insert bill comments" ON public.bill_comments
|
|
FOR INSERT TO authenticated
|
|
WITH CHECK (auth.uid() = user_id);
|
|
|
|
-- 4. election_audit_log: restrict anon INSERT to valid election_id
|
|
DROP POLICY IF EXISTS "Anyone can insert audit log" ON public.election_audit_log;
|
|
CREATE POLICY "Anon can insert audit log for valid election" ON public.election_audit_log
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.elections e WHERE e.id = election_id)
|
|
);
|
|
|
|
-- 5. election_ballots: anon INSERT scoped to valid vote_token via voter lookup
|
|
DROP POLICY IF EXISTS "Anon can insert ballots" ON public.election_ballots;
|
|
CREATE POLICY "Anon can insert ballots" ON public.election_ballots
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM public.election_eligible_voters ev
|
|
WHERE ev.vote_token = election_ballots.vote_token
|
|
AND ev.election_id = election_ballots.election_id
|
|
AND ev.has_voted = false
|
|
)
|
|
);
|
|
|
|
-- 6. election_ballots: anon DELETE scoped to valid vote_token
|
|
DROP POLICY IF EXISTS "Anon can delete ballots by token" ON public.election_ballots;
|
|
CREATE POLICY "Anon can delete ballots by token" ON public.election_ballots
|
|
FOR DELETE TO anon
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM public.election_eligible_voters ev
|
|
WHERE ev.vote_token = election_ballots.vote_token
|
|
AND ev.election_id = election_ballots.election_id
|
|
)
|
|
);
|
|
|
|
-- 7. form_inbox: restrict INSERT to authenticated staff only
|
|
DROP POLICY IF EXISTS "Anyone can insert into form inbox" ON public.form_inbox;
|
|
CREATE POLICY "Staff can insert into form inbox" ON public.form_inbox
|
|
FOR INSERT TO authenticated
|
|
WITH CHECK (
|
|
public.has_role(auth.uid(), 'admin') OR
|
|
public.has_role(auth.uid(), 'manager') OR
|
|
public.has_role(auth.uid(), 'employee')
|
|
);
|
|
|
|
-- 8. public_form_submissions: anon INSERT scoped to valid template+association
|
|
DROP POLICY IF EXISTS "Anyone can submit forms" ON public.public_form_submissions;
|
|
CREATE POLICY "Anon can submit forms for valid template" ON public.public_form_submissions
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM public.public_form_templates t
|
|
WHERE t.id = template_id AND t.association_id = public_form_submissions.association_id AND t.is_published = true
|
|
)
|
|
);
|
|
|
|
-- 9. violation_responses: anon INSERT scoped to valid violation_id
|
|
DROP POLICY IF EXISTS "Anyone can submit violation responses" ON public.violation_responses;
|
|
CREATE POLICY "Anon can submit violation responses for valid violation" ON public.violation_responses
|
|
FOR INSERT TO anon
|
|
WITH CHECK (
|
|
EXISTS (SELECT 1 FROM public.violations v WHERE v.id = violation_id)
|
|
);
|