Protéger les lectures et écritures des exposants

Introduction

Fern Studio et Cedar Works se préparent à un petit salon artisanal. Chaque exposant doit gérer ses propres inscriptions pour le chargement dans une table PostgreSQL partagée. L’équipe du salon doit toujours pouvoir voir tous les exposants et attribuer les stands. Un filtre dans une commande client est utile pour consulter les données, mais un exposant peut l’omettre ; la base doit faire respecter cette limite.

Vous enregistrerez et appliquerez des politiques de lignes, puis utiliserez des connexions ordinaires distinctes avec mot de passe pour remplir de véritables inscriptions. PostgreSQL et les comptes limités au périmètre du cours sont prêts. Vous devez savoir naviguer dans un terminal et connaître SELECT/WHERE, INSERT, UPDATE et les tables PostgreSQL. Les rôles, la sécurité au niveau des lignes, les expressions de politique, les transactions et la syntaxe shell requise sont expliqués avant utilisation. Aucune programmation Python n’est requise. Utilisez Terminal tout au long du TP. La durée de 95 minutes est une estimation de l’auteur comprenant la lecture et les erreurs ordinaires, pas un temps de réalisation mesuré auprès de débutants.

Travaillez dans /home/labex/project/exhibitor-desk. Rédigez read-access.sql et write-access.sql, modifiez les politiques de lignes sur fair.registrations et créez ou corrigez uniquement les deux nouvelles inscriptions indiquées ensuite. Préservez tous les enregistrements d’origine, l’identité et les définitions de la table, les droits/comptes préparés, les identifiants de connexion, les outils et les services. Les fichiers de politique doivent contenir du SQL autonome : aucune commande shell, aucun changement de connexion ni inclusion externe. Les noms de politique équivalents et les arrangements corrects de politiques sont acceptés. Vous êtes l’opérateur du cours et pouvez délibérément choisir chaque compte de test préparé ; cet exercice protège les identités de base de données les unes des autres, mais ne protège pas les identifiants de connexion préparés contre l’opérateur sudo de la VM. Il ne met pas en œuvre d’authentification web et ne promet pas de protection contre tout canal auxiliaire d’information.

Limiter la visibilité sans bloquer l’équipe du salon

Dans cette étape, vous donnerez à chaque exposant une vue complète de ses propres inscriptions et garderez disponible le travail de l’organisateur sur l’ensemble des exposants. Les écritures des exposants resteront indisponibles jusqu’à l’ajout de leurs politiques d’écriture à l’étape suivante.

Commencez dans Terminal. cd change de répertoire ; ce chemin absolu fonctionne quel que soit votre répertoire courant :

cd /home/labex/project/exhibitor-desk

./ exécute un programme du répertoire courant. Le lanceur db préparé authentifie une véritable connexion PostgreSQL et exécute psql, le client PostgreSQL. Il ne filtre pas les lignes et ne fabrique pas de résultats. Listez ses alias de comptes :

./db accounts

Les alias sélectionnent ces connexions préparées séparément :

Un alias est une commodité pour vous en tant qu’opérateur, pas une permission de base accordée à Fern. Choisir cedar utilise délibérément les identifiants de connexion distincts de Cedar. Dans une connexion Fern, passer à Cedar doit rester interdit.

-c fournit une chaîne de commande. Les guillemets doubles gardent ses espaces ensemble pour le shell. session_user identifie le compte de connexion authentifié ; current_user identifie le rôle dont l’instruction utilise actuellement les permissions. Ils doivent être identiques dans ces connexions ordinaires :

./db fern -c "SELECT session_user, current_user;"

Vous devez obtenir fern_studio dans les deux colonnes. Lisez d’abord l’enregistrement normal connu de Fern. Le nombre de caisses est un décompte entier ; zéro signifie un zéro connu, pas une inscription manquante :

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 appartient à Fern, possède le contact Fern desk, 2 caisses, le stand A1 et des frais de 1200 centimes. fair est le schéma qui regroupe la table. Ces faits métier d’origine doivent rester inchangés.

Omettez maintenant le filtre client facultatif :

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Au départ, Fern voit R101/R102 et les R201/R202 de Cedar. WHERE owner_role = 'fern_studio' les masquerait dans cette seule requête, mais n’empêcherait pas cette requête sans filtre. Le même problème existe pour Cedar.

PostgreSQL possède deux couches de permissions pertinentes. Un GRANT autorise une opération sur une table ou des colonnes nommées ; les droits préparés permettent déjà à ces utilisateurs d’accéder aux opérations requises. La sécurité au niveau des lignes, ou RLS, décide en plus quelles lignes ils peuvent lire ou modifier. Activer RLS sans politique applicable refuse par défaut l’accès aux lignes ; cela n’invente pas automatiquement la règle d’appartenance.

L’editor possède cette table du cours et peut changer les politiques. Les propriétaires contournent normalement RLS ; une requête réussie de l’éditeur ne prouve donc pas qu’un exposant est restreint. Utilisez l’éditeur pour examiner les définitions et les comptes ordinaires pour tester le résultat. \d est une commande de description psql, pas du SQL :

./db editor -c "\d fair.registrations"

Les six colonnes sont registration_id, owner_role, contact, crates, booth_label et fee_cents. Une nouvelle inscription reçoit par défaut le stand Pending et des frais de 0. Les droits de colonne préparés autorisent les exposants à insérer identifiant/propriétaire/contact/caisses et à mettre à jour propriétaire/contact/caisses. Ils ne peuvent pas modifier les identifiants, stands ou frais, supprimer des lignes ni vider la table avec truncate. La politique doit encore refuser un changement de propriétaire, même si un droit UPDATE rend la colonne propriétaire accessible.

Examinez les politiques existantes via la vue de catalogue PostgreSQL pg_policies :

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access autorise tout le monde. Les politiques sont normalement permissives : celles qui s’appliquent se combinent avec OR. Ajouter une politique au périmètre étroit à côté de cette politique large autoriserait encore le résultat large. Retirez l’ancienne politique dans le cadre du changement.

L’expression USING d’une politique décide si une ligne existante est visible. owner_role = current_user compare le propriétaire de la ligne à la véritable identité de base de données. FOR SELECT limite cette politique à la lecture. L’organisateur a besoin d’une politique distincte, limitée TO fair_team, autorisant ses opérations légitimes sur toutes les lignes ; true signifie que chaque ligne passe pour ce rôle nommé.

Enregistrez la première migration. cat > read-access.sql écrit le texte suivant dans le fichier en remplaçant une ancienne copie. <<'SQL' commence un here-document : le marqueur entre guillemets garde le SQL encadré littéral jusqu’à ce que SQL apparaisse seul sur une ligne. BEGIN et COMMIT font de ce changement de politiques une transaction unique ; les instructions sont donc validées ensemble ou annulées en cas d’erreur. DROP POLICY IF EXISTS permet aussi de corriger et relancer cette migration :

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

Si le shell attend toujours parce que vous avez omis le marqueur final, saisissez SQL seul. Lisez le fichier enregistré avant de l’exécuter :

cat read-access.sql

-f demande à psql de lire les commandes d’un fichier. Appliquez-le en tant que propriétaire de table limité au périmètre du cours :

./db editor -f read-access.sql

Vous devez obtenir les messages BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY et COMMIT. Un avis indiquant qu’un de vos nouveaux noms de politique n’existait pas encore est sans gravité. Le lanceur active ON_ERROR_STOP : une erreur de syntaxe dans un fichier arrête l’exécution avec le statut psql 3. La fermeture de la connexion annule une transaction inachevée. Corrigez le fichier et relancez-le ; ne réinitialisez pas la VM. Pour une erreur de nom de fichier/chemin, utilisez le cd absolu, exécutez ls, lisez le véritable nom de fichier et réessayez. Aucune ligne d’origine n’a besoin d’être supprimée ou recréée.

Utilisez chaque identité ordinaire pour examiner l’ensemble autorisé complet :

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern voit R101/R102, avec les nombres de caisses 2/0. Cedar voit R201/R202, avec les nombres de caisses 3/1. L’organisateur voit toujours les quatre originaux :

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

C’est un état intermédiaire intentionnel : les exposants ont une politique SELECT, mais pas encore de politique INSERT/UPDATE. Gardez leurs droits préparés inchangés. Lancez la vérification du résultat ; sudo permet au vérificateur préparé d’examiner l’état protégé. Il lit le métier actif et teste le SQL de politique sur des données jetables, sans compléter votre travail manquant :

sudo python3 /opt/labex-exhibitors/verify.py visibility

Vous devez obtenir confirmation que les deux comptes voient leurs propres enregistrements et que l’organisateur reste utilisable. Ajoutez ensuite les règles distinctes pour les écritures proposées.

Vérifier les lignes existantes et les valeurs proposées

Dans cette étape, vous permettrez aux exposants d’ajouter et de modifier leurs propres inscriptions sans créer ni transférer d’enregistrements pour un autre propriétaire. Commencez dans Terminal, dans /home/labex/project/exhibitor-desk.

Une politique SELECT seule n’autorise pas un INSERT. Une politique d’écriture doit considérer deux moments. Pour UPDATE, USING sélectionne les lignes existantes que le rôle peut modifier ; WITH CHECK teste la nouvelle ligne proposée. Pour INSERT, il n’y a pas d’ancienne ligne ; sa politique utilise donc uniquement WITH CHECK. Dans les deux cas, le propriétaire proposé doit être égal à current_user.

Enregistrez la migration d’écriture avec le même format de here-document entre guillemets. Elle ajoute des politiques à la table déjà protégée par read-access.sql :

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

Lisez-la, puis appliquez-la en tant qu’éditeur :

cat write-access.sql

./db editor -f write-access.sql

Les deux instructions CREATE POLICY doivent être validées. Corriger le fichier et le relancer remplace uniquement ces politiques d’écriture nommées ; cela ne réinitialise pas les inscriptions. Si vous repartez de vos fichiers de politique enregistrés, appliquez d’abord read-access.sql, puis write-access.sql.

Des arrangements équivalents de politiques peuvent fonctionner. Une politique ALL ou UPDATE avec seulement USING utilise implicitement cette expression pour son WITH CHECK absent ; omettre cette clause n’est pas automatiquement un défaut. Ici, les politiques par commande séparées rendent chaque rôle explicite. Ce qui compte est le comportement de chaque opération permise et interdite, pas les noms de politique ni leur écriture exacte.

Testez d’abord une écriture légitime sans laisser de données de test. BEGIN démarre une transaction. RETURNING affiche la ligne réellement stockée par INSERT/UPDATE. ROLLBACK abandonne les modifications de la transaction et laisse les quatre originaux intacts :

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

Vous devez obtenir TRY-FERN, le propriétaire fern_studio et 2 caisses, puis ROLLBACK. L’insertion normale accepte un véritable zéro et la mise à jour fonctionne ; une règle qui refuse toute écriture ne satisferait pas cette exigence.

Testez maintenant une ligne existante masquée. Cette instruction doit réussir en tant que commande tout en ne trouvant aucune ligne autorisée à mettre à jour :

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

Aucune ligne ne doit être renvoyée et vous devez obtenir UPDATE 0. R201 appartient à Cedar. Un résultat à zéro ligne est un filtrage de lignes, pas un échec de connexion ni une erreur de base de données.

Les deux commandes suivantes sont des échecs SQL attendus. Toutes deux encadrent le test dans une transaction : si une mauvaise politique l’acceptait accidentellement, le ROLLBACK final l’abandonnerait quand même. Si une erreur arrête la commande avant ROLLBACK, la fermeture de la connexion abandonne aussi sa transaction inachevée.

Essayez de créer un enregistrement appartenant à Cedar en étant authentifié comme Fern. Omettez volontairement RETURNING : une vérification de politique SELECT sur les lignes renvoyées ne doit pas remplacer la règle d’écriture des lignes proposées :

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

Vous devez obtenir new row violates row-level security policy. $? est le statut de la commande shell immédiatement précédente ; affichez-le avant toute autre exécution :

echo $?

Vous devez obtenir le statut 1 pour cette commande SQL -c en échec. Un statut 2 avec une erreur de connexion/authentification est un échec différent et ne prouve pas que la politique de lignes a fonctionné. Le lanceur choisit les identifiants préparés à chaque appel ; utilisez ./db accounts et réessayez l’alias documenté plutôt que de modifier les fichiers d’identifiants.

Essayez ensuite de changer le propriétaire des lignes visibles de Fern pour Cedar. L’UPDATE constant n’a ni WHERE ni RETURNING lisant une colonne de table. Cela atteint directement le contrôle de la ligne proposée ; la transaction qui l’entoure garde même un test accidentellement accepté réversible :

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

Vous devez de nouveau obtenir l’erreur de sécurité des lignes et le statut 1. N’en déduisez pas que seules les mises à jour avec RETURNING nécessitent une protection : le SQL autorisé peut l’omettre.

Les permissions de colonne fournissent une autre limite. Un exposant peut modifier son propre contact/nombre de caisses, mais l’équipe du salon attribue les stands et frais. Cette tentative de changement de stand doit produire une erreur de permission de table, même pour les propres lignes de Fern :

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

Vous devez obtenir permission denied for table registrations, plutôt qu’une erreur de politique de ligne proposée. De même, une connexion Fern ne peut pas devenir Cedar simplement en nommant ce rôle :

./db fern -c "SET ROLE cedar_works;"

Vous devez obtenir permission denied to set role. Les exposants préparés sont des comptes de connexion ordinaires sans appartenance croisée à d’autres comptes, sans propriété de table ni BYPASSRLS. Changer un réglage d’application librement choisi n’authentifierait pas un autre compte ; la politique compare le véritable rôle de base de données.

Confirmez que les tests réversibles ont laissé les faits métier d’origine intacts :

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Les quatre lignes, contacts, nombres de caisses, stands et frais d’origine doivent correspondre à leurs valeurs initiales. Lancez la vérification de la limite d’écriture :

sudo python3 /opt/labex-exhibitors/verify.py writes

Elle vérifie les deux identités ordinaires, les écritures légitimes, des jeux de données modifiés, les écritures vers un autre propriétaire et l’accès de l’organisateur. Une réussite complète signifie que vous pouvez maintenant utiliser la table protégée pour un travail normal.

Compléter les inscriptions avec les comptes protégés

Dans cette étape, vous utiliserez les permissions opérationnelles pour préparer deux véritables inscriptions de chargement. Commencez dans Terminal, dans /home/labex/project/exhibitor-desk. Ces deux nouveaux enregistrements sont les seules lignes métier que vous pouvez ajouter ou corriger ; conservez toutes les lignes d’origine.

Fern commence une nouvelle inscription avec un nombre de caisses connu égal à zéro. Les valeurs par défaut fournies laissent son stand à Pending et ses frais à 0 jusqu’à leur attribution par l’organisateur :

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

Vous devez obtenir R301, fern_studio, Fern loading, 0, Pending, 0. Cet INSERT est réellement validé, contrairement aux essais précédents dans des transactions. Le répéter provoquerait une erreur de clé en double ; cette commande de création n’est pas idempotente.

Fixez son nombre final de caisses à quatre avec une mise à jour ordinaire ciblée :

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar crée sa propre nouvelle inscription avec une caisse :

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

Vous devez obtenir R302, cedar_works et 1. Chaque exposant voit maintenant trois enregistrements, dont ses deux enregistrements d’origine.

L’identité ordinaire de l’organisateur attribue à chaque nouvel enregistrement le stand et les frais convenus. Les frais sont exprimés en centimes entiers ; ces montants fournis sont des données du scénario, pas un calcul fiscal ou comptable :

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

Vous devez obtenir R301/C3/1800 et R302/D2/600. Ce travail utilise fair_team, pas le propriétaire de la table ; un contournement par le propriétaire ne prouverait pas que la politique de l’organisateur reste utilisable.

Lisez les enregistrements complets finaux avec chaque compte d’exposant :

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern voit R101/R102/R301, avec pour le nouveau R301 un nombre de caisses de 4, le stand C3 et des frais de 1800. Cedar voit R201/R202/R302, avec pour le nouveau R302 un nombre de caisses de 1, le stand D2 et des frais de 600. Les enregistrements d’origine doivent rester inchangés.

Si vous répétez accidentellement un INSERT, examinez le nouvel enregistrement nommé avec le compte correspondant. S’il existe déjà, utilisez un UPDATE ciblé de son contact et de son nombre de caisses plutôt que de le supprimer ou de réinitialiser la base. Par exemple, le nouvel enregistrement de Fern peut être corrigé avec UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';. Corrigez uniquement les nouveaux enregistrements nommés ; relancez l’attribution ciblée du stand et des frais par l’organisateur si vous y avez fait une erreur. Les migrations enregistrées peuvent aussi être corrigées et réappliquées sans recréer les données.

Lancez la vérification du résultat final :

sudo python3 /opt/labex-exhibitors/verify.py business

Vous devez recevoir confirmation que les deux nouvelles inscriptions sont prêtes, avec les bons propriétaires, nombres de caisses, stands et frais. Vous disposez d’un SQL de politiques réutilisable et d’un registre partagé réellement utilisable par les exposants ordinaires et l’organisateur. Le challenge indépendant applique les mêmes limites à un autre registre d’artisans.

Résumé

Vous avez distingué les permissions de table/colonne de l’appartenance des lignes, retiré une politique permissive trop large et vérifié à la fois les lignes existantes et les écritures proposées via de véritables connexions ordinaires. Deux nouvelles inscriptions et les stands attribués par l’organisateur fonctionnent maintenant sans exposer ni modifier les enregistrements d’un autre exposant.

La limite repose sur l’identité authentifiée dans la base de données. Les propriétaires de table et administrateurs restent de confiance, et cet exercice ne fournit ni connexion web ni garantie contre tout canal auxiliaire de la base.