163 lines
6.0 KiB
PL/PgSQL
163 lines
6.0 KiB
PL/PgSQL
-- 1. Fonksiyonu DROP et ki return type değişebilsin
|
||
DROP FUNCTION IF EXISTS cleanup_old_anonymous_trips();
|
||
|
||
-- 2. Tüm eski ve güvensiz politikaları temizle (trips)
|
||
DROP POLICY IF EXISTS "Herkes seyahat oluşturabilir" ON trips;
|
||
DROP POLICY IF EXISTS "Herkes public seyahatleri görebilir" ON trips;
|
||
DROP POLICY IF EXISTS "Kullanıcılar kendi seyahatlerini güncelleyebilir" ON trips;
|
||
DROP POLICY IF EXISTS "Kullanıcılar kendi seyahatlerini silebilir" ON trips;
|
||
DROP POLICY IF EXISTS "Allow anonymous trip viewing" ON trips;
|
||
DROP POLICY IF EXISTS "Allow anonymous trip update" ON trips;
|
||
DROP POLICY IF EXISTS "Allow anonymous trip delete" ON trips;
|
||
DROP POLICY IF EXISTS "Allow anonymous trip insert" ON trips;
|
||
DROP POLICY IF EXISTS "Secure trip viewing" ON trips;
|
||
DROP POLICY IF EXISTS "Secure trip update" ON trips;
|
||
DROP POLICY IF EXISTS "Secure trip delete" ON trips;
|
||
DROP POLICY IF EXISTS "Secure trip creation" ON trips;
|
||
DROP POLICY IF EXISTS "Güvenli seyahat görüntüleme" ON trips;
|
||
DROP POLICY IF EXISTS "Güvenli seyahat güncelleme" ON trips;
|
||
DROP POLICY IF EXISTS "Güvenli seyahat silme" ON trips;
|
||
DROP POLICY IF EXISTS "Seyahatleri görüntüleme" ON trips;
|
||
DROP POLICY IF EXISTS "Seyahat oluşturma" ON trips;
|
||
DROP POLICY IF EXISTS "Seyahat güncelleme" ON trips;
|
||
DROP POLICY IF EXISTS "Seyahat silme" ON trips;
|
||
DROP POLICY IF EXISTS "trips_select_policy" ON trips;
|
||
DROP POLICY IF EXISTS "trips_insert_policy" ON trips;
|
||
DROP POLICY IF EXISTS "trips_update_policy" ON trips;
|
||
DROP POLICY IF EXISTS "trips_delete_policy" ON trips;
|
||
|
||
-- 3. Tüm eski ve güvensiz politikaları temizle (trip_days)
|
||
DROP POLICY IF EXISTS "Herkes seyahat günlerini yönetebilir" ON trip_days;
|
||
DROP POLICY IF EXISTS "Güvenli seyahat günleri yönetimi" ON trip_days;
|
||
DROP POLICY IF EXISTS "Seyahat günleri yönetimi" ON trip_days;
|
||
DROP POLICY IF EXISTS "trip_days_all_policy" ON trip_days;
|
||
|
||
-- 4. Tüm eski ve güvensiz politikaları temizle (trip_places)
|
||
DROP POLICY IF EXISTS "Herkes seyahat yerlerini yönetebilir" ON trip_places;
|
||
DROP POLICY IF EXISTS "Güvenli seyahat yerleri yönetimi" ON trip_places;
|
||
DROP POLICY IF EXISTS "Seyahat yerleri yönetimi" ON trip_places;
|
||
DROP POLICY IF EXISTS "trip_places_all_policy" ON trip_places;
|
||
|
||
-- 5. TRIPS GÜVENLİ POLİTİKALARI
|
||
-- SELECT: Sahibi, Public olanlar, veya doğru token'ı olanlar
|
||
CREATE POLICY "trips_select_policy" ON trips
|
||
FOR SELECT
|
||
USING (
|
||
is_public = true
|
||
OR user_id = auth.uid()
|
||
OR (
|
||
user_id IS NULL
|
||
AND anonymous_token IS NOT NULL
|
||
AND (
|
||
anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-trip-token', 'none')
|
||
OR anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-anonymous-token', 'none')
|
||
)
|
||
)
|
||
);
|
||
|
||
-- INSERT: Giriş yapmış kullanıcılar veya anonim (user_id IS NULL)
|
||
CREATE POLICY "trips_insert_policy" ON trips
|
||
FOR INSERT
|
||
WITH CHECK (
|
||
user_id = auth.uid() OR user_id IS NULL
|
||
);
|
||
|
||
-- UPDATE: Sadece sahibi veya doğru token'ı olanlar
|
||
CREATE POLICY "trips_update_policy" ON trips
|
||
FOR UPDATE
|
||
USING (
|
||
user_id = auth.uid()
|
||
OR (
|
||
user_id IS NULL
|
||
AND anonymous_token IS NOT NULL
|
||
AND (
|
||
anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-trip-token', 'none')
|
||
OR anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-anonymous-token', 'none')
|
||
)
|
||
)
|
||
)
|
||
WITH CHECK (
|
||
user_id = auth.uid() OR user_id IS NULL
|
||
);
|
||
|
||
-- DELETE: Sadece sahibi veya doğru token'ı olanlar
|
||
CREATE POLICY "trips_delete_policy" ON trips
|
||
FOR DELETE
|
||
USING (
|
||
user_id = auth.uid()
|
||
OR (
|
||
user_id IS NULL
|
||
AND anonymous_token IS NOT NULL
|
||
AND (
|
||
anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-trip-token', 'none')
|
||
OR anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-anonymous-token', 'none')
|
||
)
|
||
)
|
||
);
|
||
|
||
-- 6. TRIP_DAYS GÜVENLİ POLİTİKALARI
|
||
CREATE POLICY "trip_days_all_policy" ON trip_days
|
||
FOR ALL
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM trips
|
||
WHERE trips.id = trip_days.trip_id
|
||
AND (
|
||
trips.is_public = true
|
||
OR trips.user_id = auth.uid()
|
||
OR (
|
||
trips.user_id IS NULL
|
||
AND trips.anonymous_token IS NOT NULL
|
||
AND (
|
||
trips.anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-trip-token', 'none')
|
||
OR trips.anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-anonymous-token', 'none')
|
||
)
|
||
)
|
||
)
|
||
)
|
||
);
|
||
|
||
-- 7. TRIP_PLACES GÜVENLİ POLİTİKALARI
|
||
CREATE POLICY "trip_places_all_policy" ON trip_places
|
||
FOR ALL
|
||
USING (
|
||
EXISTS (
|
||
SELECT 1 FROM trip_days
|
||
JOIN trips ON trips.id = trip_days.trip_id
|
||
WHERE trip_days.id = trip_places.trip_day_id
|
||
AND (
|
||
trips.is_public = true
|
||
OR trips.user_id = auth.uid()
|
||
OR (
|
||
trips.user_id IS NULL
|
||
AND trips.anonymous_token IS NOT NULL
|
||
AND (
|
||
trips.anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-trip-token', 'none')
|
||
OR trips.anonymous_token = COALESCE(current_setting('request.headers', true)::json->>'x-anonymous-token', 'none')
|
||
)
|
||
)
|
||
)
|
||
)
|
||
);
|
||
|
||
-- 8. TEMİZLİK OTOMASYONU
|
||
CREATE OR REPLACE FUNCTION cleanup_old_anonymous_trips()
|
||
RETURNS VOID AS $$
|
||
BEGIN
|
||
-- 7 günden eski, user_id NULL olan gezileri sil
|
||
DELETE FROM trips
|
||
WHERE user_id IS NULL
|
||
AND created_at < NOW() - INTERVAL '7 days';
|
||
END;
|
||
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
||
|
||
-- pg_cron desteği varsa zamanla
|
||
DO $$
|
||
BEGIN
|
||
IF EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_cron') THEN
|
||
PERFORM cron.schedule('0 0 * * *', 'SELECT cleanup_old_anonymous_trips()');
|
||
END IF;
|
||
EXCEPTION WHEN OTHERS THEN
|
||
NULL;
|
||
END $$;
|