mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
41 lines
1.0 KiB
PL/PgSQL
41 lines
1.0 KiB
PL/PgSQL
|
|
-- Remove the overly permissive anon SELECT policy
|
|
DROP POLICY IF EXISTS "Anyone can read violations for response page" ON public.violations;
|
|
|
|
-- Create a secure RPC that returns only non-PII fields for a single violation
|
|
CREATE OR REPLACE FUNCTION public.lookup_violation_for_response(p_violation_id uuid)
|
|
RETURNS TABLE(
|
|
id uuid,
|
|
association_id uuid,
|
|
unit_id uuid,
|
|
owner_id uuid,
|
|
title text,
|
|
description text,
|
|
category text,
|
|
status text,
|
|
priority text,
|
|
due_date date,
|
|
violation_type text,
|
|
violation_date date,
|
|
address text,
|
|
stage text,
|
|
photo_url text,
|
|
photo_urls jsonb,
|
|
notice_level text,
|
|
created_at timestamptz
|
|
)
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
SELECT
|
|
v.id, v.association_id, v.unit_id, v.owner_id,
|
|
v.title, v.description, v.category, v.status, v.priority,
|
|
v.due_date, v.violation_type, v.violation_date, v.address,
|
|
v.stage, v.photo_url, v.photo_urls, v.notice_level, v.created_at
|
|
FROM public.violations v
|
|
WHERE v.id = p_violation_id
|
|
LIMIT 1;
|
|
$$;
|