mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
238 lines
10 KiB
PL/PgSQL
238 lines
10 KiB
PL/PgSQL
|
|
-- ============================================================
|
|
-- 1. STRIPE & FORTE: Remove homeowner direct SELECT, add RPC
|
|
-- ============================================================
|
|
|
|
DROP POLICY IF EXISTS "Homeowners can read own association stripe mappings" ON public.stripe_account_mappings;
|
|
|
|
DROP POLICY IF EXISTS "Users can view own association forte mappings" ON public.forte_account_mappings;
|
|
|
|
-- RPC that returns only non-sensitive gateway fields
|
|
CREATE OR REPLACE FUNCTION public.get_association_payment_gateways(p_association_id uuid)
|
|
RETURNS jsonb
|
|
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
|
|
AS $$
|
|
SELECT jsonb_build_object(
|
|
'stripe', (
|
|
SELECT jsonb_build_object(
|
|
'id', id, 'association_id', association_id,
|
|
'stripe_account_id', stripe_account_id,
|
|
'stripe_public_key', stripe_public_key,
|
|
'is_active', is_active, 'label', label
|
|
) FROM public.stripe_account_mappings
|
|
WHERE association_id = p_association_id AND is_active = true LIMIT 1
|
|
),
|
|
'forte', (
|
|
SELECT jsonb_build_object(
|
|
'id', id, 'association_id', association_id,
|
|
'organization_id', organization_id, 'location_id', location_id,
|
|
'environment', environment, 'is_active', is_active, 'label', label,
|
|
'pass_processing_fee', pass_processing_fee,
|
|
'processing_fee_percent', processing_fee_percent,
|
|
'processing_fee_fixed_cents', processing_fee_fixed_cents
|
|
) FROM public.forte_account_mappings
|
|
WHERE association_id = p_association_id AND is_active = true LIMIT 1
|
|
)
|
|
);
|
|
$$;
|
|
|
|
-- ============================================================
|
|
-- 2. PROFILES: Scope to own profile + same association members
|
|
-- ============================================================
|
|
|
|
DROP POLICY IF EXISTS "Users can view all profiles" ON public.profiles;
|
|
|
|
CREATE POLICY "Users can view own and association profiles"
|
|
ON public.profiles FOR SELECT TO authenticated
|
|
USING (
|
|
user_id = auth.uid()
|
|
OR has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
OR user_id IN (
|
|
SELECT o.user_id FROM public.owners o
|
|
WHERE o.association_id IN (SELECT get_user_association_ids())
|
|
AND o.user_id IS NOT NULL
|
|
)
|
|
OR user_id IN (
|
|
SELECT bm.user_id FROM public.board_members bm
|
|
WHERE bm.association_id IN (SELECT get_user_association_ids())
|
|
AND bm.user_id IS NOT NULL
|
|
)
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 3. BANK ACCOUNTS: Add association scoping for staff
|
|
-- ============================================================
|
|
|
|
DROP POLICY IF EXISTS "Staff full access on bank_accounts" ON public.bank_accounts;
|
|
|
|
CREATE POLICY "Staff full access on own association bank_accounts"
|
|
ON public.bank_accounts FOR ALL TO authenticated
|
|
USING (
|
|
(has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'manager'::app_role))
|
|
AND association_id IN (SELECT get_user_association_ids())
|
|
)
|
|
WITH CHECK (
|
|
(has_role(auth.uid(), 'admin'::app_role) OR has_role(auth.uid(), 'manager'::app_role))
|
|
AND association_id IN (SELECT get_user_association_ids())
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 4. BLANKET USING(true) POLICIES → Association-scoped
|
|
-- ============================================================
|
|
|
|
-- Tables with direct association_id
|
|
DROP POLICY IF EXISTS "Authenticated users can view annual meetings" ON public.annual_meetings;
|
|
CREATE POLICY "Users can view own association annual meetings"
|
|
ON public.annual_meetings FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view billable_expenses" ON public.billable_expenses;
|
|
CREATE POLICY "Users can view own association billable expenses"
|
|
ON public.billable_expenses FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view journal_entries" ON public.journal_entries;
|
|
CREATE POLICY "Users can view own association journal entries"
|
|
ON public.journal_entries FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view parking records" ON public.parking_records;
|
|
CREATE POLICY "Users can view own association parking records"
|
|
ON public.parking_records FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view projects" ON public.projects;
|
|
CREATE POLICY "Users can view own association projects"
|
|
ON public.projects FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view timeline events" ON public.unit_timeline_events;
|
|
CREATE POLICY "Users can view own association timeline events"
|
|
ON public.unit_timeline_events FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Anyone can verify proofs" ON public.document_validation_proofs;
|
|
CREATE POLICY "Users can view own association proofs"
|
|
ON public.document_validation_proofs FOR SELECT TO authenticated
|
|
USING (association_id IN (SELECT get_user_association_ids()));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view active board resources" ON public.board_resources;
|
|
CREATE POLICY "Users can view own association board resources"
|
|
ON public.board_resources FOR SELECT TO authenticated
|
|
USING (is_active = true AND association_id IN (SELECT get_user_association_ids()));
|
|
|
|
-- Tables scoped via parent join (application_id, bill_id, etc.)
|
|
DROP POLICY IF EXISTS "Authenticated users can view arc_application_comments" ON public.arc_application_comments;
|
|
CREATE POLICY "Users can view own association arc comments"
|
|
ON public.arc_application_comments FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.arc_applications a
|
|
WHERE a.id = arc_application_comments.application_id
|
|
AND a.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view arc_application_votes" ON public.arc_application_votes;
|
|
CREATE POLICY "Users can view own association arc votes"
|
|
ON public.arc_application_votes FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.arc_applications a
|
|
WHERE a.id = arc_application_votes.application_id
|
|
AND a.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can read bill comments" ON public.bill_comments;
|
|
CREATE POLICY "Users can view own association bill comments"
|
|
ON public.bill_comments FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.bills b
|
|
WHERE b.id = bill_comments.bill_id
|
|
AND b.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view vote responses" ON public.board_vote_responses;
|
|
CREATE POLICY "Users can view own association vote responses"
|
|
ON public.board_vote_responses FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.board_votes bv
|
|
WHERE bv.id = board_vote_responses.board_vote_id
|
|
AND bv.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can read project comments" ON public.project_comments;
|
|
CREATE POLICY "Users can view own association project comments"
|
|
ON public.project_comments FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.projects p
|
|
WHERE p.id = project_comments.project_id
|
|
AND p.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can read project files" ON public.project_files;
|
|
CREATE POLICY "Users can view own association project files"
|
|
ON public.project_files FOR SELECT TO authenticated
|
|
USING (EXISTS (
|
|
SELECT 1 FROM public.projects p
|
|
WHERE p.id = project_files.project_id
|
|
AND p.association_id IN (SELECT get_user_association_ids())
|
|
));
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view status_update_comments" ON public.status_update_comments;
|
|
CREATE POLICY "Staff can view status update comments"
|
|
ON public.status_update_comments FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view status_update_votes" ON public.status_update_votes;
|
|
CREATE POLICY "Staff can view status update votes"
|
|
ON public.status_update_votes FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view entity_comments" ON public.entity_comments;
|
|
CREATE POLICY "Staff can view entity comments"
|
|
ON public.entity_comments FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
DROP POLICY IF EXISTS "Authenticated users can view entity_votes" ON public.entity_votes;
|
|
CREATE POLICY "Staff can view entity votes"
|
|
ON public.entity_votes FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
-- Company settings & role permissions: restrict to staff
|
|
DROP POLICY IF EXISTS "Authenticated users can view company_settings" ON public.company_settings;
|
|
CREATE POLICY "Staff can view company settings"
|
|
ON public.company_settings FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
DROP POLICY IF EXISTS "Authenticated can read role_permissions" ON public.role_permissions;
|
|
CREATE POLICY "Staff can read role permissions"
|
|
ON public.role_permissions FOR SELECT TO authenticated
|
|
USING (
|
|
has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|
|
|
|
-- Shared links: restrict to creator or staff
|
|
DROP POLICY IF EXISTS "Authenticated can read shared links" ON public.shared_links;
|
|
CREATE POLICY "Users can read own or staff shared links"
|
|
ON public.shared_links FOR SELECT TO authenticated
|
|
USING (
|
|
created_by = auth.uid()
|
|
OR has_role(auth.uid(), 'admin'::app_role)
|
|
OR has_role(auth.uid(), 'manager'::app_role)
|
|
);
|