mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
82 lines
2.9 KiB
PL/PgSQL
82 lines
2.9 KiB
PL/PgSQL
|
|
-- 1. Allow association participants (which includes ARC members who are owners/board members) to see votes
|
|
CREATE POLICY "Participants can view entity votes"
|
|
ON public.entity_votes FOR SELECT
|
|
USING (public.can_comment_on_entity(auth.uid(), entity_type, entity_id));
|
|
|
|
-- 2. Helper to determine if an ARC application is locked
|
|
CREATE OR REPLACE FUNCTION public.is_arc_application_locked(_application_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM public.arc_applications
|
|
WHERE id = _application_id
|
|
AND lower(coalesce(status,'')) IN ('approved','denied')
|
|
);
|
|
$$;
|
|
|
|
-- 3. Block writes to entity_votes/entity_comments tied to a locked ARC application
|
|
CREATE OR REPLACE FUNCTION public.prevent_writes_on_locked_arc()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
v_entity_type text;
|
|
v_entity_id uuid;
|
|
BEGIN
|
|
v_entity_type := COALESCE(NEW.entity_type, OLD.entity_type);
|
|
v_entity_id := COALESCE(NEW.entity_id, OLD.entity_id);
|
|
|
|
IF v_entity_type = 'arc_application' AND public.is_arc_application_locked(v_entity_id) THEN
|
|
RAISE EXCEPTION 'This ARC application has been finalized (approved or denied) and is locked from further changes.'
|
|
USING ERRCODE = 'check_violation';
|
|
END IF;
|
|
|
|
RETURN COALESCE(NEW, OLD);
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_lock_entity_votes_arc ON public.entity_votes;
|
|
CREATE TRIGGER trg_lock_entity_votes_arc
|
|
BEFORE INSERT OR UPDATE OR DELETE ON public.entity_votes
|
|
FOR EACH ROW EXECUTE FUNCTION public.prevent_writes_on_locked_arc();
|
|
|
|
DROP TRIGGER IF EXISTS trg_lock_entity_comments_arc ON public.entity_comments;
|
|
CREATE TRIGGER trg_lock_entity_comments_arc
|
|
BEFORE INSERT OR UPDATE OR DELETE ON public.entity_comments
|
|
FOR EACH ROW EXECUTE FUNCTION public.prevent_writes_on_locked_arc();
|
|
|
|
-- 4. Block updates to a finalized ARC application (except a deliberate status reset by an admin)
|
|
CREATE OR REPLACE FUNCTION public.prevent_updates_on_finalized_arc()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
BEGIN
|
|
IF lower(COALESCE(OLD.status,'')) IN ('approved','denied') THEN
|
|
-- Allow admins to reopen by changing status away from approved/denied; otherwise block.
|
|
IF NEW.status IS DISTINCT FROM OLD.status
|
|
AND lower(COALESCE(NEW.status,'')) NOT IN ('approved','denied')
|
|
AND public.has_role(auth.uid(), 'admin'::public.app_role) THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
RAISE EXCEPTION 'This ARC application has been finalized (approved or denied) and is locked from further changes.'
|
|
USING ERRCODE = 'check_violation';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_lock_arc_application_updates ON public.arc_applications;
|
|
CREATE TRIGGER trg_lock_arc_application_updates
|
|
BEFORE UPDATE ON public.arc_applications
|
|
FOR EACH ROW EXECUTE FUNCTION public.prevent_updates_on_finalized_arc();
|