denshooter
7e910ce23d
- update posts_select RLS to allow approved/ok posts regardless of anonymity - sync baseline and perf-fix migrations with live policy Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
59 lines
2.0 KiB
PL/PgSQL
59 lines
2.0 KiB
PL/PgSQL
-- Performance follow-up for DB linter findings:
|
|
-- 1) Wrap auth.uid() in RLS policies with SELECT to avoid per-row re-evaluation.
|
|
-- 2) Ensure every FK in follows/reports/resonances/whispers has a covering index.
|
|
-- 3) Merge duplicate permissive UPDATE policies on posts.
|
|
|
|
BEGIN;
|
|
|
|
ALTER POLICY profiles_update ON public.profiles
|
|
USING ((select auth.uid()) = id);
|
|
|
|
ALTER POLICY posts_select ON public.posts
|
|
USING (
|
|
deleted_at IS NULL
|
|
AND review_status IN ('ok', 'approved')
|
|
);
|
|
|
|
ALTER POLICY posts_insert ON public.posts
|
|
WITH CHECK ((select auth.uid()) = user_id);
|
|
|
|
DROP POLICY IF EXISTS posts_softdelete ON public.posts;
|
|
ALTER POLICY posts_update_own ON public.posts
|
|
USING ((select auth.uid()) = user_id)
|
|
WITH CHECK ((select auth.uid()) = user_id);
|
|
|
|
ALTER POLICY resonances_insert ON public.resonances
|
|
WITH CHECK ((select auth.uid()) = user_id);
|
|
|
|
ALTER POLICY resonances_delete ON public.resonances
|
|
USING ((select auth.uid()) = user_id);
|
|
|
|
ALTER POLICY whispers_select ON public.whispers
|
|
USING (
|
|
(select auth.uid()) = from_user_id
|
|
OR (select auth.uid()) = to_user_id
|
|
);
|
|
|
|
ALTER POLICY whispers_insert ON public.whispers
|
|
WITH CHECK ((select auth.uid()) = from_user_id);
|
|
|
|
ALTER POLICY follows_insert ON public.follows
|
|
WITH CHECK ((select auth.uid()) = follower_id);
|
|
|
|
ALTER POLICY follows_delete ON public.follows
|
|
USING ((select auth.uid()) = follower_id);
|
|
|
|
ALTER POLICY reports_insert ON public.reports
|
|
WITH CHECK ((select auth.uid()) = reporter_id);
|
|
|
|
ALTER POLICY reports_select_own ON public.reports
|
|
USING ((select auth.uid()) = reporter_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_follows_following_id ON public.follows (following_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reports_reporter_id ON public.reports (reporter_id);
|
|
CREATE INDEX IF NOT EXISTS idx_resonances_user_id ON public.resonances (user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_whispers_from_user_id ON public.whispers (from_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_whispers_post_id ON public.whispers (post_id);
|
|
|
|
COMMIT;
|