Schéma Postgres et Row Level Security Supabase : tutoriel pratique 2026

📍 Article principal : Supabase 2026 : guide complet.

Le RLS (Row Level Security) Postgres est la fondation sécurité de Supabase. Sans RLS, votre app est compromise. Ce tutoriel détaille les patterns validés pour SaaS B2B, marketplace, et e-commerce.

Prérequis

• Supabase en production.
• Compréhension SQL.
• Niveau : intermédiaire/avancé.
• Temps : 1-2h.

Étape 1 — Schema users (Supabase Auth)

Supabase crée auto auth.users. Étendre avec table profiles :

CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  username text UNIQUE,
  full_name text,
  avatar_url text,
  organization_id uuid REFERENCES organizations(id),
  role text NOT NULL DEFAULT 'member',
  created_at timestamptz DEFAULT now()
);

Étape 2 — Trigger auto-création profile

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, username, full_name)
  VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'full_name');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

Étape 3 — Schema multi-tenant

CREATE TABLE organizations (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  slug text UNIQUE NOT NULL,
  plan text DEFAULT 'free',
  created_at timestamptz DEFAULT now()
);

CREATE TABLE products (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  price integer NOT NULL,
  currency text DEFAULT 'XOF',
  created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_products_org ON products(organization_id);

Étape 4 — RLS policies multi-tenant

ALTER TABLE products ENABLE ROW LEVEL SECURITY;

-- Lecture : utilisateur voit produits de son org
CREATE POLICY "users see own org products"
ON products FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM profiles WHERE id = auth.uid()
  )
);

-- Création : admins org peuvent créer
CREATE POLICY "admins create products"
ON products FOR INSERT
WITH CHECK (
  organization_id IN (
    SELECT organization_id FROM profiles 
    WHERE id = auth.uid() AND role IN ('admin', 'editor')
  )
);

-- Update : éditeurs org modifient
CREATE POLICY "editors update products"
ON products FOR UPDATE
USING (
  organization_id IN (
    SELECT organization_id FROM profiles 
    WHERE id = auth.uid() AND role IN ('admin', 'editor')
  )
);

-- Delete : admins seulement
CREATE POLICY "admins delete products"
ON products FOR DELETE
USING (
  organization_id IN (
    SELECT organization_id FROM profiles 
    WHERE id = auth.uid() AND role = 'admin'
  )
);

Étape 5 — Public read avec filter

CREATE TABLE public_listings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  status text DEFAULT 'draft',
  title text NOT NULL,
  description text
);

ALTER TABLE public_listings ENABLE ROW LEVEL SECURITY;

CREATE POLICY "public reads published"
ON public_listings FOR SELECT
USING (status = 'published');

Étape 6 — Helper functions

CREATE OR REPLACE FUNCTION current_user_role()
RETURNS text AS $$
  SELECT role FROM profiles WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION current_user_org()
RETURNS uuid AS $$
  SELECT organization_id FROM profiles WHERE id = auth.uid();
$$ LANGUAGE sql SECURITY DEFINER;

-- Usage simplifié
CREATE POLICY "simpler check"
ON products FOR SELECT
USING (organization_id = current_user_org());

Étape 7 — Indexes performance

CREATE INDEX idx_profiles_org ON profiles(organization_id);
CREATE INDEX idx_products_org_status ON products(organization_id, created_at DESC);
CREATE INDEX idx_products_search ON products USING gin(to_tsvector('french', name || ' ' || description));

Étape 8 — Test policies

-- En tant qu'utilisateur X
SET ROLE authenticated;
SET request.jwt.claim.sub = 'user-id-X';
SELECT * FROM products;
-- Doit retourner uniquement produits org de X

Étape 9 — Migrations versionnées

Supabase CLI : supabase migration new add_products_table → fichier .sql versionné. Apply : supabase db push.

Étape 10 — Audit RLS

Studio → Authentication → Policies. Liste toutes les policies. Vérifier coverage de chaque table.

Erreurs fréquentes

Adaptation au contexte ouest-africain

Trois précisions. Multi-currency : champ currency par produit (XOF, MAD, EUR). RLS pas affecté. Multi-pays : organizations.country pour filter par marché. Conformité : RLS = sécurité au niveau DB, satisfait audit ARTCI/CDP.

Tutoriels frères

• Déployer Supabase
• Auth OAuth

FAQ

RLS performance impact ? Minimal avec indexes. Vérifier EXPLAIN.

Bypass RLS pour admin tasks ? service_role key (jamais frontend).

Test policies ? Studio → SQL Editor avec SET ROLE authenticated.

Migrations en prod ? Supabase CLI ou flyway. Versionnées Git.

pgvector pour IA ? Oui inclus, ALTER TABLE pour ajouter colonne vector.

Pour aller plus loin

• 🔝 Pilier : Guide complet Supabase 2026
• Documentation RLS : supabase.com/docs/guides/auth/row-level-security