76 lines
2.7 KiB
SQL
76 lines
2.7 KiB
SQL
-- ============================================
|
|
-- Buddy Access Logic Migration
|
|
-- Run AFTER rls_policy_performance_fixes.sql
|
|
-- ============================================
|
|
-- Adds read-only access for buddies to see sessions/tastings they participate in
|
|
-- ============================================
|
|
|
|
-- ============================================
|
|
-- Fix: tasting_sessions - Add consolidated buddy read access
|
|
-- ============================================
|
|
|
|
-- Drop all previous policies for this table
|
|
DROP POLICY IF EXISTS "tasting_sessions_policy" ON tasting_sessions;
|
|
DROP POLICY IF EXISTS "tasting_sessions_owner_policy" ON tasting_sessions;
|
|
DROP POLICY IF EXISTS "tasting_sessions_buddy_select_policy" ON tasting_sessions;
|
|
|
|
-- Consolidated SELECT: owner OR participant
|
|
CREATE POLICY "tasting_sessions_select_policy" ON tasting_sessions
|
|
FOR SELECT USING (
|
|
(SELECT auth.uid()) = user_id OR
|
|
id IN (
|
|
SELECT sp.session_id
|
|
FROM session_participants sp
|
|
JOIN buddies b ON b.id = sp.buddy_id
|
|
WHERE b.buddy_profile_id = (SELECT auth.uid())
|
|
)
|
|
);
|
|
|
|
-- Owner-only for other actions
|
|
CREATE POLICY "tasting_sessions_insert_policy" ON tasting_sessions
|
|
FOR INSERT WITH CHECK ((SELECT auth.uid()) = user_id);
|
|
|
|
CREATE POLICY "tasting_sessions_update_policy" ON tasting_sessions
|
|
FOR UPDATE USING ((SELECT auth.uid()) = user_id);
|
|
|
|
CREATE POLICY "tasting_sessions_delete_policy" ON tasting_sessions
|
|
FOR DELETE USING ((SELECT auth.uid()) = user_id);
|
|
|
|
|
|
-- ============================================
|
|
-- Fix: tastings - Add consolidated buddy read access
|
|
-- ============================================
|
|
|
|
-- Drop all previous policies for this table
|
|
DROP POLICY IF EXISTS "tastings_policy" ON tastings;
|
|
DROP POLICY IF EXISTS "tastings_owner_policy" ON tastings;
|
|
DROP POLICY IF EXISTS "tastings_buddy_select_policy" ON tastings;
|
|
|
|
-- Consolidated SELECT: owner OR tagged buddy
|
|
CREATE POLICY "tastings_select_policy" ON tastings
|
|
FOR SELECT USING (
|
|
(SELECT auth.uid()) = user_id OR
|
|
id IN (
|
|
SELECT tb.tasting_id
|
|
FROM tasting_buddies tb
|
|
JOIN buddies b ON b.id = tb.buddy_id
|
|
WHERE b.buddy_profile_id = (SELECT auth.uid())
|
|
)
|
|
);
|
|
|
|
-- Owner-only for other actions
|
|
CREATE POLICY "tastings_insert_policy" ON tastings
|
|
FOR INSERT WITH CHECK ((SELECT auth.uid()) = user_id);
|
|
|
|
CREATE POLICY "tastings_update_policy" ON tastings
|
|
FOR UPDATE USING ((SELECT auth.uid()) = user_id);
|
|
|
|
CREATE POLICY "tastings_delete_policy" ON tastings
|
|
FOR DELETE USING ((SELECT auth.uid()) = user_id);
|
|
|
|
-- ============================================
|
|
-- Note: bottles stays owner-only for now
|
|
-- The original logic was complex and could cause RLS recursion
|
|
-- If you need buddies to see bottles, we can add it separately
|
|
-- ============================================
|