Patrons de sécurité au niveau des lignes PostgreSQL pour applications multi‑locataires

Pourquoi l'application de règles dans la base de données compte pour les apps métier

Les applications métier ont souvent des règles comme «les utilisateurs ne peuvent voir que les données de leur entreprise» ou «seuls les managers peuvent approuver les remboursements». Beaucoup d'équipes appliquent ces règles dans l'UI ou l'API et pensent que c'est suffisant. Le problème, c'est que chaque chemin supplémentaire vers la base de données devient une chance de fuite : un outil d'administration interne, un job en arrière‑plan, une requête d'analytique, un endpoint oublié, ou un bug qui saute une vérification.

L'isolation des locataires signifie qu'un client (tenant) ne peut jamais lire ou modifier les données d'un autre client, même par accident. L'accès basé sur les rôles implique que, au sein d'un même locataire, des personnes ont des pouvoirs différents, comme agent vs manager vs finance. Ces règles sont faciles à décrire, mais difficiles à garder parfaitement cohérentes quand elles vivent à plusieurs endroits.

La sécurité au niveau des lignes de PostgreSQL (Row‑Level Security, RLS) est une fonctionnalité de la base de données qui permet à la base de décider quelles lignes une requête peut voir ou modifier. Au lieu d'espérer que chaque requête de votre appli se souvienne du bon WHERE, la base applique automatiquement des politiques.

RLS n'est pas une protection magique pour tout. Elle ne remplacera pas la conception du schéma, ne remplace pas l'authentification, et ne vous protège pas d'une personne ayant déjà un rôle puissant dans la base (comme un superuser). Elle n'empêchera pas non plus des erreurs logiques du type «quelqu'un peut mettre à jour une ligne qu'il ne peut pas sélectionner» à moins d'écrire des politiques à la fois pour la lecture et pour l'écriture.

Ce que vous obtenez, c'est un filet de sécurité solide :

• Un jeu de règles pour tous les chemins qui touchent la base de données
• Moins de moments «oups» quand une nouvelle fonctionnalité est déployée
• Des audits plus clairs, parce que les règles d'accès sont visibles en SQL
• Une meilleure défense si un bug d'API passe à travers

Il y a un petit coût de mise en place. Il vous faut une manière cohérente de transmettre «qui est cet utilisateur» et «quel est le locataire» à la base, et il faut maintenir les politiques au fur et à mesure que votre appli grandit. Le retour sur investissement est important, surtout pour les SaaS et les outils internes où des données clients sensibles sont en jeu.

Les bases de la RLS sans le jargon

La Row‑Level Security (RLS) filtre automatiquement les lignes qu'une requête peut voir ou modifier. Plutôt que de compter sur chaque écran, endpoint d'API ou rapport pour «se souvenir» des règles, la base de données les applique pour vous.

Avec la RLS de PostgreSQL, vous écrivez des politiques qui sont vérifiées à chaque SELECT, INSERT, UPDATE et DELETE. Si la politique dit «cet utilisateur ne peut voir que les lignes du locataire A», alors une page d'administration oubliée, une nouvelle requête ou un hotfix précipité auront les mêmes garde‑fous.

La RLS est différente de GRANT/REVOKE. GRANT décide si un rôle peut toucher une table du tout (ou des colonnes spécifiques). La RLS décide quelles lignes à l'intérieur de cette table sont autorisées. En pratique, on utilise souvent les deux : GRANT pour limiter qui peut accéder à la table, et RLS pour limiter ce qu'ils peuvent accéder.

Elle résiste aussi au monde réel. Les vues obéissent généralement à la RLS parce que l'accès à la table sous‑jacente déclenche toujours la politique. Les jointures et sous‑requêtes sont filtrées, donc un utilisateur ne peut pas «s'infiltrer» dans les données d'un autre via une jointure. Et la politique s'applique quel que soit le client qui exécute la requête : code de l'app, console SQL, job en arrière‑plan, ou outil de reporting.

La RLS convient quand vous avez des besoins forts d'isolation des locataires, plusieurs façons d'interroger les mêmes données, ou de nombreux rôles partageant des tables (commun dans les SaaS et outils internes). Elle peut être excessive pour des petites applis avec un seul backend de confiance, ou pour des données non sensibles qui ne quittent jamais un service contrôlé. Dès que vous avez plus d'un point d'entrée (outils d'administration, exports, BI, scripts), la RLS rentabilise généralement son coût.

Commencez par cartographier locataires, rôles et propriété des données

Avant d'écrire la moindre politique, clarifiez qui possède quoi. La RLS de PostgreSQL fonctionne mieux quand votre modèle de données reflète déjà locataires, rôles et propriété.

Commencez par les locataires. Dans la plupart des apps SaaS, la règle la plus simple est : chaque table partagée qui contient des données clients a un tenant_id. Cela inclut les tables «évidentes» comme invoices, mais aussi des choses qu'on oublie souvent : attachments, comments, audit logs, et jobs en arrière‑plan.

Ensuite, nommez les rôles que les gens utilisent réellement. Gardez l'ensemble petit et lisible : owner, manager, agent, read‑only. Ce sont des rôles métier que vous mappez ensuite aux vérifications de politiques (ce ne sont pas les mêmes que les rôles de base de données).

Décidez ensuite comment les enregistrements sont possédés. Certaines tables appartiennent à un seul utilisateur (par exemple une note privée). D'autres appartiennent à une équipe (par exemple une boîte de réception partagée). Mélanger les deux sans plan conduit à des politiques difficiles à lire et faciles à contourner.

Une manière simple de documenter vos règles est de répondre aux mêmes questions pour chaque table :

• Quelle est la frontière du locataire (quelle colonne l'impose) ?
• Qui peut lire les lignes (par rôle et par propriété) ?
• Qui peut créer et mettre à jour les lignes (et sous quelles conditions) ?
• Qui peut supprimer les lignes (généralement la règle la plus stricte) ?
• Quelles exceptions sont autorisées (support, automatisation, exports) ?

Exemple : «Invoices» peut permettre aux managers de voir toutes les factures du locataire, aux agents de voir les factures des clients qui leur sont assignés, et aux utilisateurs read‑only de voir mais jamais modifier. Décidez en amont quelles règles doivent être strictes (isolation des locataires, suppressions) et lesquelles peuvent être flexibles (visibilité accrue pour les managers). Si vous construisez dans un outil no‑code comme AppMaster, cette cartographie aide aussi à aligner les attentes de l'UI et les règles en base.

Modèles de conception pour les tables multi‑locataires

La RLS multi‑locataire fonctionne mieux quand vos tables ont une forme prévisible. Si chaque table stocke le locataire différemment, vos politiques deviennent un puzzle. Une forme cohérente rend la RLS plus lisible, testable et maintenable.

Commencez par choisir un identifiant de locataire et l'utiliser partout. Les UUID sont courants car difficiles à deviner et faciles à générer. Les entiers conviennent aussi, surtout pour des applis internes. Les slugs (comme "acme") sont lisibles, mais peuvent changer : gardez‑les comme champ d'affichage, pas comme clé principale.

Pour les données scoped au locataire, ajoutez une colonne tenant_id à chaque table qui appartient à un locataire, et rendez‑la NOT NULL autant que possible. Si une ligne peut exister sans locataire, c'est souvent un signe de mélange entre données globales et données locataires, ce qui rend les politiques RLS plus fragiles.

L'indexation est simple mais importante. La plupart des requêtes dans un SaaS filtrent d'abord par tenant, puis par un champ métier comme status ou date. Un bon défaut est un index sur tenant_id, et pour les tables à fort trafic un index composite comme (tenant_id, created_at) ou (tenant_id, status) basé sur vos filtres fréquents.

Décidez tôt quelles tables sont globales et lesquelles sont scoped par locataire. Tables globales communes : countries, currency codes, ou definitions de plans. Tables scoped : customers, invoices, tickets, et tout ce que le locataire possède.

Si vous voulez des règles faciles à maintenir, gardez‑les simples :

• Tables scoped : tenant_id NOT NULL, RLS activée, politiques vérifiant toujours tenant_id.
• Tables de référence globales : pas de tenant_id, pas de politiques tenant, lecture seule pour la plupart des rôles.
• Tables partagées mais contrôlées : tables séparées par concept (évitez de mélanger global et tenant dans la même table).

Si vous construisez avec un outil comme AppMaster, cette cohérence paye dans le modèle de données aussi. Une fois tenant_id standardisé, vous pouvez réutiliser les mêmes patrons sans surprise.

Pas à pas : créez votre première politique par locataire

Un bon premier succès avec la RLS PostgreSQL est une table lisible uniquement à l'intérieur du locataire courant. Le but est simple : même si quelqu'un oublie un WHERE dans l'API, la base refuse de renvoyer des lignes d'autres locataires.

Commencez avec une table qui inclut une colonne tenant_id :

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

Une fois la RLS activée, le comportement par défaut surprend souvent : si un rôle est soumis à la RLS et qu'il n'y a pas de politique correspondante, SELECT renvoie zéro ligne (et les écritures échouent). C'est justement ce qu'on recherche au départ.

Ajoutez maintenant une politique minimale de lecture. Cet exemple suppose que votre appli définit une variable de session comme app.tenant_id après la connexion :

CREATE POLICY invoices_tenant_read
ON invoices
FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);

Ensuite, ajoutez des règles d'écriture. En RLS, USING contrôle quelles lignes existantes vous pouvez toucher, et WITH CHECK contrôle quelles nouvelles valeurs vous êtes autorisé à écrire.

CREATE POLICY invoices_tenant_insert
ON invoices
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY invoices_tenant_update
ON invoices
FOR UPDATE
USING (tenant_id = current_setting('app.tenant_id')::uuid)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY invoices_tenant_delete
ON invoices
FOR DELETE
USING (tenant_id = current_setting('app.tenant_id')::uuid);

Les politiques sont PERMISSIVE par défaut, ce qui signifie qu'une seule politique peut autoriser l'accès. Choisissez RESTRICTIVE quand vous voulez que plusieurs règles soient toutes satisfaites (utile pour ajouter une seconde garde comme «seuls les comptes actifs»).

Gardez les politiques petites et centrées sur les rôles. Plutôt qu'une grosse règle avec beaucoup de OR, créez des politiques séparées par audience (par exemple invoices_tenant_read_app_user et invoices_tenant_read_support_agent). C'est plus simple à tester, à relire et plus sûr à modifier.

Transmettre en toute sécurité le contexte locataire et utilisateur

Pour que la RLS fonctionne, la base doit savoir «qui appelle» et «à quel locataire il appartient». Les politiques RLS ne peuvent comparer les lignes qu'à des valeurs que la base peut lire au moment de la requête, il faut donc transmettre ce contexte dans la session.

Un pattern courant est de définir des variables de session après l'authentification, puis de laisser les politiques les lire via current_setting(). L'appli prouve l'identité (par exemple en validant un JWT), puis écrit les IDs de locataire et d'utilisateur dans la connexion DB.

-- Exécuter une fois par requête (ou par transaction)
SELECT set_config('app.tenant_id', '3f2a0c3e-9c7b-4d3f-9c5c-3c5e9c5d1a11', true);
SELECT set_config('app.user_id',   '8d9c6b1a-6b6d-4e32-9c0d-2bfe6f6c1111', true);
SELECT set_config('app.role',      'support_agent', true);

-- Dans une politique
-- tenant_id column is a UUID
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

Utiliser le troisième argument true rend la valeur «locale» à la transaction courante. Cela importe si vous utilisez du connection pooling : une connexion poolée peut être réutilisée par une autre requête, vous ne voulez pas que le contexte du locataire d'hier traîne.

Remplir le contexte à partir des claims JWT

Si votre API utilise des JWT, traitez les claims comme des entrées, pas comme la vérité absolue. Vérifiez d'abord la signature et la date d'expiration du token, puis copiez seulement les champs nécessaires (tenant_id, user_id, role) dans les settings de session. Évitez de laisser les clients envoyer ces valeurs directement via des headers ou des query params.

Contexte manquant ou invalide : refuser par défaut

Concevez les politiques pour que les settings manquants donnent zéro ligne.

Utilisez current_setting('app.tenant_id', true) pour que les valeurs manquantes retournent NULL. Castez au bon type (comme ::uuid) pour que les formats invalides échouent rapidement. Et échouez la requête si on ne peut pas définir le contexte locataire/utilisateur, plutôt que d'imaginer une valeur par défaut.

Cela garde le contrôle d'accès cohérent même lorsqu'une requête contourne l'UI ou qu'un nouvel endpoint est ajouté plus tard.

Patterns de rôles pratiques et maintenables

La manière la plus simple de garder les politiques RLS lisibles est de séparer identité et permissions. Une base saine est une table users plus une table memberships qui relie un utilisateur à un locataire et à un rôle (ou plusieurs rôles). Vos politiques peuvent alors répondre à une question : «Est‑ce que l'utilisateur courant a l'adhésion nécessaire pour cette ligne ?»

Gardez les noms de rôles liés à des actions réelles, pas à des intitulés de poste. invoice_viewer et invoice_approver vieillissent mieux que manager, car la politique peut s'écrire en termes d'actions.

Voici quelques patterns de rôles simples à faire évoluer :

• Propriétaire uniquement : la ligne a created_by_user_id (ou owner_user_id) et l'accès vérifie l'égalité exacte.
• Équipe uniquement : la ligne a team_id, et la politique vérifie que l'utilisateur est membre de cette équipe dans le même locataire.
• Lectures approuvées uniquement : lecture permise seulement si status = 'approved', et écritures réservées aux approbateurs.
• Règles mixtes : commencez strict, puis ajoutez de petites exceptions (par exemple «le support peut lire, mais seulement dans le locataire»).

Les admins cross‑tenant sont souvent la source d'erreurs. Gérez‑les explicitement, pas comme un raccourci «superuser» caché. Créez un concept séparé comme platform_admin (global) et exigez une vérification explicite dans la politique. Mieux : gardez l'accès cross‑tenant en lecture seule par défaut et exigez un seuil plus élevé pour les écritures.

La documentation compte plus qu'on ne le croit. Mettez un court commentaire au‑dessus de chaque politique qui explique l'intention, pas le SQL. «Les approbateurs peuvent changer le statut. Les lecteurs ne peuvent que lire les invoices approuvées.» Six mois plus tard, cette note empêche des modifications dangereuses.

Si vous utilisez un outil no‑code comme AppMaster, ces patterns s'appliquent toujours. Votre UI et API peuvent aller vite, mais les règles en base restent stables parce qu'elles reposent sur des memberships et sur des noms de rôles clairs.

Scénario d'exemple : un petit SaaS avec factures et support

Imaginez un petit SaaS qui sert plusieurs entreprises. Chaque entreprise est un locataire. L'app a des invoices (argent) et des tickets support (aide au quotidien). Les utilisateurs peuvent être agents, managers, ou support.

Modèle de données (simplifié) : chaque ligne d'invoice et de ticket a un tenant_id. Les tickets ont aussi assignee_user_id. L'app définit le locataire et l'utilisateur courants dans la session DB juste après la connexion.

Voici comment la RLS change le risque au quotidien.

Un utilisateur du locataire A ouvre l'écran des invoices et essaye de deviner un ID d'une invoice du locataire B (ou l'UI envoie accidentellement cet ID). La requête s'exécute toujours, mais la base renvoie zéro ligne parce que la politique exige invoice.tenant_id = current_tenant_id. Il n'y a pas de fuite «accès refusé», juste un résultat vide.

À l'intérieur d'un même locataire, les rôles réduisent encore l'accès. Un manager peut voir toutes les factures et tous les tickets de son locataire. Un agent ne voit que les tickets qui lui sont assignés, plus éventuellement ses brouillons. C'est là que les équipes se trompent souvent dans l'API, surtout quand les filtres sont optionnels.

Le support est un cas particulier. Il peut devoir voir des factures pour aider les clients, mais ne devrait pas pouvoir modifier des champs sensibles comme amount, bank_account, ou tax_id. Un pattern pratique :

• Autoriser SELECT sur invoices pour le rôle support (toujours scoped par locataire).
• Autoriser UPDATE seulement via un chemin «safe» (par exemple une vue exposant les colonnes modifiables, ou une politique d'update stricte rejetant les modifications de champs protégés).

Maintenant le scénario du bug d'API : un endpoint oublie d'appliquer le filtre locataire après un refactor. Sans RLS, il peut divulguer des invoices cross‑tenant. Avec la RLS, la base refuse de renvoyer des lignes hors du locataire de session, donc le bug devient un écran cassé, pas une fuite de données.

Si vous construisez ce type de SaaS avec AppMaster, vous voulez quand même ces règles en base. Les vérifications UI sont utiles, mais ce sont les règles en base qui tiennent quand quelque chose coince.

Erreurs communes et comment les éviter

La RLS est puissante, mais de petites erreurs peuvent transformer «sécurisé» en «surprenant». La plupart des problèmes apparaissent lorsqu'une nouvelle table est ajoutée, qu'un rôle change, ou que quelqu'un teste avec l'utilisateur DB incorrect.

Une erreur fréquente : oublier d'activer la RLS sur une nouvelle table. Vous écrivez des politiques pour vos tables principales, puis ajoutez une table «notes» ou «attachments» et la déployez avec un accès complet. Faites‑en une habitude : nouvelle table = RLS activée + au moins une politique.

Un autre piège : des politiques décalées entre actions. Une politique qui permet INSERT mais bloque SELECT peut donner l'impression que «les données disparaissent» juste après création. L'inverse est aussi pénible : les utilisateurs lisent des lignes qu'ils ne peuvent pas créer, alors ils contournent l'UI. Pensez en flux : «créer puis voir», «mettre à jour puis rouvrir», «supprimer puis lister».

Faites attention aux fonctions SECURITY DEFINER. Elles s'exécutent avec les privilèges du propriétaire de la fonction, ce qui peut contourner la RLS si vous n'êtes pas strict. Si vous les utilisez, gardez‑les petites, validez les entrées, et évitez le SQL dynamique sauf si nécessaire.

Évitez aussi de compter sur du filtrage côté appli tout en laissant l'accès DB ouvert. Même de bonnes API grandissent avec de nouveaux endpoints, jobs en arrière‑plan, et scripts d'admin. Si le rôle DB peut tout lire, tôt ou tard quelque chose le fera.

Pour détecter les problèmes tôt, gardez des vérifications pratiques :

• Testez en utilisant le même rôle DB que votre appli en production, pas votre utilisateur admin perso.
• Ajoutez un test négatif par table : un utilisateur d'un autre locataire doit voir zéro ligne.
• Confirmez que chaque table supporte les actions attendues : SELECT, INSERT, UPDATE, DELETE.
• Revuez l'utilisation de SECURITY DEFINER et documentez pourquoi elle est nécessaire.
• Incluez «RLS activée ?» dans les checklists de revue de code et de migrations.

Exemple : si un agent support crée une note de facture mais ne peut pas la relire, c'est souvent une politique d'INSERT sans politique SELECT correspondante (ou le contexte locataire n'est pas défini pour la session en question).

Checklist rapide pour valider votre configuration RLS

La RLS peut sembler correcte à la relecture et quand même échouer en production. La validation consiste moins à lire les politiques qu'à essayer de les casser avec des comptes et des requêtes réalistes. Testez‑la comme votre appli l'utilisera, pas comme vous espérez qu'elle fonctionne.

Créez un petit jeu d'identités de test. Utilisez au moins deux locataires (Tenant A et Tenant B). Pour chaque locataire, ajoutez un utilisateur normal et un admin/manager. Si vous supportez des rôles support ou read‑only, ajoutez‑les aussi.

Ensuite, mettez la RLS sous pression avec un petit ensemble de vérifications répétables :

• Exécutez les opérations de base pour chaque rôle : lister les lignes, récupérer une ligne par id, insérer, mettre à jour, supprimer. Pour chaque opération, testez les cas «autorisé» et «doit être bloqué».
• Prouvez les frontières locataires : en tant que Tenant A, tentez de lire ou modifier les données de Tenant B en utilisant des ids existants. Vous devez obtenir zéro ligne ou une erreur de permission, jamais des données.
• Testez les jointures pour les fuites : joignez des tables protégées à d'autres tables (y compris des lookup tables). Confirmez qu'une jointure ne peut pas ramener des lignes d'un autre locataire via une FK ou une vue.
• Vérifiez que le contexte manquant ou erroné refuse l'accès : supprimez le contexte locataire/utilisateur et recommencez. «Pas de contexte» doit échouer fermé. Essayez aussi un tenant id invalide.
• Confirmez la performance de base : regardez les plans de requête et assurez‑vous que les indexes supportent votre pattern de filtre par tenant (généralement tenant_id plus ce par quoi vous triez ou recherchez).

Si un test vous surprend, corrigez la politique ou la façon de définir le contexte d'abord. Ne le corrigez pas côté UI en espérant que la DB «tiendra le coup».

Étapes suivantes : déployer prudemment et garder la cohérence

Traitez la RLS comme un système de sécurité : introduisez‑la prudemment, vérifiez‑la souvent, et gardez les règles assez simples pour que votre équipe les respecte.

Commencez petit. Choisissez les tables dont une fuite ferait le plus de dégâts (paiements, factures, données RH, messages clients), et activez la RLS là d'abord. Les victoires rapides valent mieux qu'un déploiement massif que personne ne comprend complètement.

Un ordre de déploiement pratique ressemble souvent à :

• Tables «possédées» cœur en premier (lignes clairement rattachées à un locataire)
• Tables contenant des données personnelles (PII)
• Tables partagées mais filtrées par locataire (reports, analytics)
• Tables de jointures et cas limites (many‑to‑many)
• Tout le reste une fois les bases stables

Rendez les tests non optionnels. Les tests automatisés doivent exécuter les mêmes requêtes pour différents locataires et rôles et confirmer les changements. Incluez des vérifications «doit autoriser» et «doit refuser», car les bugs les plus coûteux sont les permissions excessives silencieuses.

Gardez un endroit clair dans le flux de requête où le contexte de session est défini avant toute requête. tenant id, user id et rôle doivent être appliqués une fois, tôt, et jamais devinés plus tard. Si vous définissez le contexte au milieu d'une transaction, vous finirez par exécuter une requête avec des valeurs manquantes ou obsolètes.

Quand vous construisez avec AppMaster, prévoyez la cohérence entre vos APIs générées et vos politiques PostgreSQL. Standardisez la manière de passer le contexte tenant/role à la base (par exemple, les mêmes variables de session pour chaque endpoint) pour que les politiques se comportent de la même façon partout. Si vous utilisez AppMaster à appmaster.io, la RLS mérite toujours d'être traitée comme l'autorité finale pour l'isolation des locataires, même si vous contrôlez aussi l'accès dans l'UI.

Enfin, surveillez ce qui échoue. Les échecs d'autorisation sont des signaux utiles, surtout juste après le déploiement. Suivez les refus répétés et étudiez‑les : s'agit‑il d'une attaque réelle, d'un flux client cassé, ou d'une politique trop stricte ?

Une courte liste d'habitudes pour garder la RLS saine :

• Mentalité par défaut «deny», avec des exceptions ajoutées volontairement
• Noms de politique clairs (table + action + audience)
• Les changements de politique revus comme du code
• Les refus consignés et revus pendant le déploiement initial
• Un petit jeu de tests ajouté pour chaque nouvelle table avec RLS