Sécurité par ligne PostgreSQL : tester les politiques

La sécurité par ligne de PostgreSQL, ou RLS, applique des politiques aux lignes qu’un rôle de base de données peut lire ou modifier. Les privilèges de table déterminent toujours si ce rôle peut effectuer une opération. Un test utile vérifie donc à la fois une opération autorisée sur sa propre ligne et une tentative refusée sur celle d’un autre rôle.

Ce guide crée un laboratoire avec deux locataires et des rôles distincts. Nous avons exécuté 38 contrôles sur PostgreSQL 18.6 isolé : connexions réelles des rôles, écritures refusées, comportement du propriétaire et nettoyage. Le rôle de base de données sert ici d’identité du locataire ; l’exemple n’authentifie pas des utilisateurs web et ne configure pas de pool de connexions.

Séparer le propriétaire des rôles des locataires

Utilisez une nouvelle instance PostgreSQL jetable et une connexion d’administration. Les noms de schéma et de rôles doivent être libres. Les rôles étant communs à toute l’instance, une base séparée dans une instance de production ne suffit pas pour isoler cet exercice.

Le propriétaire contrôle la table et les politiques. rls_a et rls_b reçoivent les privilèges ordinaires sur les données ; rls_reader reçoit seulement SELECT. Aucun n’a les attributs superutilisateur ou BYPASSRLS. NOLOGIN permet de tester les permissions par changement de rôle administratif, sans mot de passe dans le SQL.

CREATE ROLE rls_owner NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_a NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_b NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE ROLE rls_reader NOLOGIN NOSUPERUSER NOBYPASSRLS;
CREATE SCHEMA rls_lab AUTHORIZATION rls_owner;
SET ROLE rls_owner;
CREATE TABLE rls_lab.notes (
    id integer PRIMARY KEY,
    tenant_role text NOT NULL,
    body text NOT NULL
);
INSERT INTO rls_lab.notes VALUES
(1, 'rls_a', 'A draft'), (2, 'rls_b', 'B draft');
GRANT USAGE ON SCHEMA rls_lab TO rls_a, rls_b, rls_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON rls_lab.notes TO rls_a, rls_b;
GRANT SELECT ON rls_lab.notes TO rls_reader;
ALTER TABLE rls_lab.notes ENABLE ROW LEVEL SECURITY;
RESET ROLE;

La table contient une note par locataire. La colonne tenant_role contient volontairement un simple texte correspondant au nom du rôle. Ce modèle pédagogique ne recommande pas d’utiliser les noms de rôles comme identifiants permanents de votre application.

Les privilèges ordinaires et les politiques sont deux contrôles distincts. La référence GRANT décrit les premiers ; la documentation RLS de PostgreSQL décrit les secondes. Les locataires ne reçoivent ni TRUNCATE ni la propriété de la table.

Vérifier le refus par défaut avant de créer une politique

RLS est activée, mais aucune politique n’existe encore. Exécutez ceci depuis la connexion d’administration :

SET ROLE rls_a;
SELECT count(*) AS visible_rows FROM rls_lab.notes;
RESET ROLE;

Nombre de lignes visibles observé :

0

Le locataire a le privilège SELECT, mais ne voit aucune ligne. Notre tentative d’insertion supplémentaire a également échoué avant la création de la politique. Ce zéro indique l’absence de lignes visibles pour ce rôle, pas une table vide : l’administrateur en a compté deux séparément.

Utiliser USING pour les lignes existantes et WITH CHECK pour leur nouveau contenu

Créez une politique explicite pour les deux rôles :

SET ROLE rls_owner;
CREATE POLICY own_notes ON rls_lab.notes
    FOR ALL TO rls_a, rls_b
    USING (tenant_role = current_user::text)
    WITH CHECK (tenant_role = current_user::text);
RESET ROLE;

USING limite les lignes existantes accessibles au rôle. WITH CHECK évalue le contenu proposé lors d’une insertion ou d’une modification. Les deux expressions imposent que le champ du locataire corresponde à current_user. FOR ALL couvre aussi la suppression des lignes visibles, sous réserve des privilèges de table.

La référence CREATE POLICY précise le comportement par commande. Écrire les deux expressions rend la règle d’écriture explicite lors de la revue.

SET ROLE rls_a;
SELECT current_user, id, body FROM rls_lab.notes ORDER BY id;
RESET ROLE;
SET ROLE rls_b;
SELECT current_user, id, body FROM rls_lab.notes ORDER BY id;
RESET ROLE;

Résultat capturé au format psql non aligné :

rls_a|1|A draft
rls_b|2|B draft

A voit seulement la note 1 et B seulement la note 2. Un SELECT de A ciblant explicitement la note 2 n’a toujours renvoyé aucune ligne. Le lecteur a continué à voir zéro ligne : aucune politique ne lui était applicable, malgré son privilège SELECT.

Tester les écritures autorisées et les cibles invisibles

Cette transaction insère puis modifie une note de A, avant de cibler la note existante de B. ROLLBACK conserve les données initiales :

BEGIN;
SET LOCAL ROLE rls_a;
INSERT INTO rls_lab.notes VALUES (3, 'rls_a', 'New note')
RETURNING id, tenant_role, body;
UPDATE rls_lab.notes SET body = 'Updated note' WHERE id = 3
RETURNING id, tenant_role, body;
WITH changed AS (
    UPDATE rls_lab.notes SET body = 'Wrong tenant' WHERE id = 2
    RETURNING id
)
SELECT count(*) AS changed_other_tenant FROM changed;
ROLLBACK;

3|rls_a|New note
3|rls_a|Updated note
0

L’insertion et la modification autorisées ont renvoyé la note 3. La tentative de modification de la note 2 n’a changé aucune ligne. Sa cible a été filtrée ; ce résultat ne prouve pas que l’identifiant n’existe pas.

Les tests de suppression ont suivi le même principe : supprimer la ligne de A était autorisé dans une transaction ensuite annulée, tandis que cibler celle de B ne supprimait rien. Les deux lignes initiales ont été conservées.

Exiger l’échec des écritures interdites

Exécutez séparément les deux blocs suivants. Chacun provoque volontairement une erreur. Dans une session interactive, lancez ROLLBACK après l’erreur avant de continuer. Si psql est configuré pour s’arrêter sur erreur, la dernière ligne ROLLBACK peut ne pas être exécutée ; fermer cette connexion annule la transaction ouverte. Notre test utilisait une connexion séparée pour chaque cas négatif.

A ne peut pas insérer une note attribuée à B :

BEGIN;
SET LOCAL ROLE rls_a;
INSERT INTO rls_lab.notes VALUES (4, 'rls_b', 'Wrong tenant');
ROLLBACK;

A ne peut pas non plus transférer sa note existante à B en changeant le champ du locataire :

BEGIN;
SET LOCAL ROLE rls_a;
UPDATE rls_lab.notes SET tenant_role = 'rls_b' WHERE id = 1;
ROLLBACK;

Les deux tests ont échoué avec SQLSTATE 42501 et une violation de politique RLS. Nous avons ensuite vérifié les lignes initiales, y compris leur locataire et leur contenu. Cela distingue le rejet d’un nouveau contenu de la modification précédente, qui ne trouvait simplement aucune cible visible.

Tentative de A	Résultat observé
Lire sa note	Une ligne
Lire ou modifier la note de B par son ID	Aucune ligne visible ou modifiée
Insérer ou modifier une note de A	Autorisé, puis annulé
Insérer une note de B ou réattribuer celle de A à B	Violation de politique, SQLSTATE 42501
Vider la table avec TRUNCATE	Refusé faute de privilège de table
Désactiver RLS	Refusé car A ne possède pas la table

Vérifier le contournement du propriétaire et FORCE ROW LEVEL SECURITY

Tester uniquement avec le propriétaire peut masquer l’absence de politique. Notre propriétaire ordinaire voyait les deux lignes avant l’activation de FORCE :

SET ROLE rls_owner;
SELECT count(*) AS owner_before_force FROM rls_lab.notes;
ALTER TABLE rls_lab.notes FORCE ROW LEVEL SECURITY;
SELECT count(*) AS owner_after_force FROM rls_lab.notes;
RESET ROLE;

2
0

Les comptes étaient 2, puis 0. Après FORCE, aucune politique de ce laboratoire ne s’appliquait au propriétaire, qui ne voyait donc plus rien. A voyait toujours sa ligne. Nous avons vérifié séparément que le superutilisateur administratif et un rôle BYPASSRLS temporaire doté de SELECT pouvaient encore lire les deux lignes. FORCE ne contraint pas ces rôles privilégiés.

Le propriétaire peut modifier les paramètres de sécurité de sa table. FORCE ne protège donc pas contre un propriétaire hostile. Séparez les connexions applicatives des rôles propriétaires et des rôles de contournement. Définir row_security = off en tant que A n’a pas accordé d’accès dans notre test : la lecture a échoué.

Vérifier la frontière d’identité avec de vraies connexions

La référence SET ROLE explique pourquoi le changement de rôle administratif ne constitue qu’une partie du test. Une session authentifiée comme administrateur peut changer de rôle avec des pouvoirs qu’une connexion applicative ne devrait pas posséder.

Dans l’instance privée, le test a temporairement activé les connexions des rôles avec des identifiants aléatoires. Les connexions distinctes de A et B ne lisaient que leur note. La connexion authentifiée de A ne pouvait prendre ni le rôle de B ni celui du propriétaire ; elle pouvait insérer sa note, mais pas celle de B. Tous les rôles de test ont été supprimés au nettoyage.

Cette politique se fie à current_user, pas à une valeur de locataire envoyée par un navigateur. Définir une variable de session arbitraire app.tenant sur B n’a pas changé l’accès de A. Si votre application partage un seul identifiant de base de données entre utilisateurs, cet exemple ne les distingue pas. Établissez une correspondance d’identité fiable, limitez qui peut la modifier et testez son attribution et sa réinitialisation dans le pool avant d’adopter une politique.

Examiner les privilèges et toutes les politiques applicables

Dans un test contrôlé, nous avons donné au lecteur une politique correspondante, mais retiré son privilège SELECT. La lecture a échoué : une politique ne remplace pas un privilège de table manquant.

Dans une autre transaction, une seconde politique SELECT permissive pour A avec USING (true) lui a rendu les deux lignes visibles. L’annulation a rétabli la visibilité d’une seule ligne. Examinez ensemble toutes les politiques applicables : une politique permissive large peut ouvrir l’accès même si la politique initiale paraît correcte.

Ces contrôles n’auditent pas les vues, les fonctions security-definer, l’authentification applicative, les changements concurrents de politiques ou les informations révélées par les contraintes d’intégrité. Ils ne vérifient pas non plus l’exhaustivité des sauvegardes. Le guide de sauvegarde et restauration PostgreSQL, en anglais présente une procédure de reprise distincte avec vérification des rôles et des données. Pour les coûts des requêtes, le guide EXPLAIN ANALYZE, en anglais traite l’analyse des plans ; une politique correcte ne préjuge pas des performances.

Supprimer les objets du laboratoire

Revenez à la connexion d’administration et supprimez seulement les objets créés pour l’exercice :

DROP TABLE rls_lab.notes;
DROP SCHEMA rls_lab;
DROP ROLE rls_a, rls_b, rls_reader, rls_owner;

Nos derniers contrôles ont confirmé la suppression du schéma et de tous les rôles de test. Le serveur isolé a été arrêté. Avant d’utiliser une politique dans une application, répétez les cas autorisés et refusés via son chemin de connexion réel. Vérifiez que la connexion ne peut acquérir l’identité d’un autre locataire ni un rôle de contournement.