SaaS Production-Ready
Audit RLS + Go-Live

-- AVANT (vulnerable)
-- RLS DESACTIVE sur public.users
-- Tout utilisateur authentifie peut lire tous les profils

SELECT id, email, raw_user_meta_data
FROM public.users;
-- Renvoie TOUS les users (fuite massive)

-- APRES (secure)
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_select_own"
ON public.users FOR SELECT
USING (auth.uid() = id);

CREATE POLICY "users_update_own"
ON public.users FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);

-- Test pgTAP
SELECT throws_ok(
  $$ SELECT * FROM public.users WHERE id != auth.uid() $$,
  'policy violation'
);

-- AVANT (vulnerable)
-- Une simple .eq('org_id', xxx) cote client
-- peut etre bypassee en modifiant le payload

-- Cote front (Lovable genere souvent):
const { data } = await supabase
  .from('projects')
  .select('*')
  .eq('org_id', currentOrgId);
-- ❌ Si user envoie un autre org_id → leak

-- APRES (secure)
CREATE POLICY "projects_org_members"
ON public.projects FOR SELECT
USING (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
  )
);

CREATE POLICY "projects_insert_own_org"
ON public.projects FOR INSERT
WITH CHECK (
  org_id IN (
    SELECT org_id FROM org_members
    WHERE user_id = auth.uid()
      AND role IN ('owner','admin','member')
  )
);

-- AVANT (vulnerable)
-- Bucket 'user-uploads' configure en mode public
-- URLs signees longue duree → fuite des fichiers

INSERT INTO storage.objects (bucket_id, name, owner)
VALUES ('user-uploads', 'invoice.pdf', auth.uid());
-- Pas de policy → tout le monde peut lire

-- APRES (secure)
CREATE POLICY "files_own_select"
ON storage.objects FOR SELECT
USING (
  bucket_id = 'user-uploads'
  AND auth.uid() = owner
);

CREATE POLICY "files_own_insert"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'user-uploads'
  AND auth.uid() = owner
  AND (storage.foldername(name))[1] = auth.uid()::text
);

-- Plus de URL signees longue duree, TTL 60s max