133 lines
4.2 KiB
SQL
133 lines
4.2 KiB
SQL
-- ================================================================================
|
||
-- CORRECTION RLS : contribution_notifications
|
||
-- ================================================================================
|
||
-- Date : 16 octobre 2025
|
||
-- Objectif : Activer RLS et créer politiques pour table contribution_notifications
|
||
-- Criticité : 🟠 MODÉRÉE (table de logs, mais contient des métadonnées sensibles)
|
||
-- ================================================================================
|
||
|
||
-- 1️⃣ ACTIVER ROW LEVEL SECURITY
|
||
ALTER TABLE contribution_notifications ENABLE ROW LEVEL SECURITY;
|
||
|
||
-- 2️⃣ CRÉER POLITIQUES RLS
|
||
|
||
-- Politique SELECT : Réservé au staff uniquement
|
||
-- Justification : Les notifications sont gérées exclusivement par le staff
|
||
CREATE POLICY "select_staff_only"
|
||
ON contribution_notifications
|
||
FOR SELECT
|
||
TO public
|
||
USING (
|
||
is_staff()
|
||
);
|
||
|
||
-- Politique INSERT : Réservé au staff uniquement
|
||
CREATE POLICY "insert_staff_only"
|
||
ON contribution_notifications
|
||
FOR INSERT
|
||
TO public
|
||
WITH CHECK (
|
||
is_staff()
|
||
);
|
||
|
||
-- Politique UPDATE : Réservé au staff uniquement
|
||
CREATE POLICY "update_staff_only"
|
||
ON contribution_notifications
|
||
FOR UPDATE
|
||
TO public
|
||
USING (
|
||
is_staff()
|
||
)
|
||
WITH CHECK (
|
||
is_staff()
|
||
);
|
||
|
||
-- Politique DELETE : Réservé au staff uniquement
|
||
CREATE POLICY "delete_staff_only"
|
||
ON contribution_notifications
|
||
FOR DELETE
|
||
TO public
|
||
USING (
|
||
is_staff()
|
||
);
|
||
|
||
-- 3️⃣ VÉRIFICATION POST-CORRECTION
|
||
|
||
-- Vérifier que RLS est activé
|
||
SELECT
|
||
tablename,
|
||
rowsecurity AS rls_enabled
|
||
FROM pg_tables
|
||
WHERE tablename = 'contribution_notifications';
|
||
-- Résultat attendu : rls_enabled = true
|
||
|
||
-- Vérifier les politiques créées
|
||
SELECT
|
||
policyname,
|
||
cmd,
|
||
qual,
|
||
with_check
|
||
FROM pg_policies
|
||
WHERE tablename = 'contribution_notifications'
|
||
ORDER BY cmd, policyname;
|
||
-- Résultat attendu : 4 politiques (SELECT, INSERT, UPDATE, DELETE)
|
||
|
||
-- ================================================================================
|
||
-- NOTES IMPORTANTES
|
||
-- ================================================================================
|
||
|
||
-- ⚠️ ALTERNATIVE : Politique basée sur org_id
|
||
-- Si vous souhaitez permettre aux clients de voir leurs propres notifications :
|
||
/*
|
||
DROP POLICY IF EXISTS "select_staff_only" ON contribution_notifications;
|
||
|
||
CREATE POLICY "select_by_org_or_staff"
|
||
ON contribution_notifications
|
||
FOR SELECT
|
||
TO public
|
||
USING (
|
||
is_staff() OR is_member_of_org(org_id)
|
||
);
|
||
*/
|
||
|
||
-- ✅ JUSTIFICATION du choix "staff_only" :
|
||
-- 1. Les notifications sont envoyées par le staff via /api/staff/cotisations/[id]/notify-client
|
||
-- 2. Les clients reçoivent les emails mais n'ont pas besoin d'accéder à la table
|
||
-- 3. Évite d'exposer les métadonnées de notification aux clients
|
||
-- 4. Cohérent avec l'architecture actuelle (routes /api/staff/cotisations/notifications)
|
||
|
||
-- ================================================================================
|
||
-- ROLLBACK (en cas de problème)
|
||
-- ================================================================================
|
||
/*
|
||
-- Supprimer les politiques
|
||
DROP POLICY IF EXISTS "select_staff_only" ON contribution_notifications;
|
||
DROP POLICY IF EXISTS "insert_staff_only" ON contribution_notifications;
|
||
DROP POLICY IF EXISTS "update_staff_only" ON contribution_notifications;
|
||
DROP POLICY IF EXISTS "delete_staff_only" ON contribution_notifications;
|
||
|
||
-- Désactiver RLS (revenir à l'état initial)
|
||
ALTER TABLE contribution_notifications DISABLE ROW LEVEL SECURITY;
|
||
*/
|
||
|
||
-- ================================================================================
|
||
-- TEST DE VALIDATION
|
||
-- ================================================================================
|
||
|
||
-- Test 1 : Vérifier que staff peut accéder aux notifications
|
||
-- (Exécuter en tant que staff avec JWT valide)
|
||
/*
|
||
SET request.jwt.claim.sub = '<staff_user_id>';
|
||
SELECT count(*) FROM contribution_notifications; -- Doit retourner le nombre total
|
||
*/
|
||
|
||
-- Test 2 : Vérifier qu'un client ne peut PAS accéder aux notifications
|
||
-- (Exécuter en tant que client avec JWT valide)
|
||
/*
|
||
SET request.jwt.claim.sub = '<client_user_id>';
|
||
SELECT count(*) FROM contribution_notifications; -- Doit retourner 0 (RLS bloque)
|
||
*/
|
||
|
||
-- ================================================================================
|
||
-- FIN DU SCRIPT
|
||
-- ================================================================================
|