Requêtes sûres pour chaque locataire

Ce que vous saurez faire

Rendez difficile l’écriture d’une requête qui oublie l’organisation. Ensuite, faites masquer les lignes des autres organisations par la base de données, même si une requête l’oublie.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 55 minutes

Ce que vous devez savoir

• Les quatre vérifications de L’autorisation dans l’API, et les clés étrangères composites de Utilisateurs et organisations.
• Les transactions, dans Transactions; le pool de connexions, dans Gestion des connexions; les plans de requête, dans Lire un plan de requête.

Votre projet

• ~/projects/ticket-api à l’instantané end-of-m04, de la dernière leçon : 17 migrations, 218 passed, 1 deselected. Vous n’avez pas besoin du fournisseur dans cette leçon.

Une chambre d’hôtel a deux verrous

Pensez à un hôtel. À votre arrivée, la réception vérifie votre passeport et vous donne une carte pour la chambre 214. C’est le premier verrou : une personne vérifie votre identité et la chambre qui vous appartient.

La porte de la chambre 214 a son propre verrou. Elle s’ouvre seulement avec une carte faite pour la chambre 214. Si la réception se trompe, ou si un client prend le mauvais couloir, la porte reste fermée. C’est le deuxième verrou.

Mais l’hôtel a aussi un passe-partout. La carte du gestionnaire ouvre toutes les portes, et les verrous ne l’arrêtent pas. Un deuxième verrou aide seulement si les personnes dans les couloirs ne portent pas le passe-partout.

L’API de billets a maintenant une réception : les quatre vérifications. Cette leçon ajoute les verrous des portes dans la base de données et trouve un passe-partout dans votre propre projet.

Le problème : un WHERE oublié

L’API contient des dizaines de requêtes. Chacune doit préciser tenant_id = …. Imaginez maintenant une nouvelle requête, écrite pendant une journée chargée, qui l’oublie :

SELECT count(*) FROM tickets;

Dans votre base de données, elle compte 240 billets : 200 de Larkfield et 40 de Bramble Books. Une liste ou une recherche fondée sur cette requête mélange les deux boutiques, comme les tentatives du module 1. Les quatre vérifications de la dernière leçon n’aident pas ici. Elles laissent Ines accéder à Bramble Books, puis la requête lui donne tout.

Cette erreur est facile à faire et difficile à voir. Elle passe tous les tests qui utilisent une seule boutique. Il faut une protection à deux niveaux : dans le code qui écrit les requêtes et dans la base qui les exécute.

Verrou 1 : l’organisation est un argument obligatoire

Ouvrez ticket_api/repository.py. Chaque fonction qui lit ou écrit les enregistrements d’une organisation a le même paramètre :

def get_ticket(conn: psycopg.Connection, ticket_id: str, *, tenant_id: str) -> dict:

Le * rend tenant_id obligatoirement nommé : le code appelant doit écrire tenant_id=… en précisant son nom. Il n’a aucune valeur par défaut, donc le code appelant ne peut pas l’omettre. Essayez de l’oublier :

python - <<'EOF'
import psycopg
from psycopg.rows import dict_row
from ticket_api import repository
from ticket_api.config import load_env, read_secret
load_env()
with psycopg.connect(read_secret("DATABASE_URL"), row_factory=dict_row) as conn:
    try:
        repository.get_ticket(conn, "T-40001")
    except TypeError as e:
        print("TypeError:", e)
    try:
        repository.get_ticket(conn, "T-40001", tenant_id="larkfield")
    except Exception as e:
        print(type(e).__name__, e)
    print(repository.get_ticket(conn, "T-40001", tenant_id="bramble")["subject"])
EOF

TypeError: get_ticket() missing 1 required keyword-only argument: 'tenant_id'
NotFound Ticket T-40001 does not exist.
Charged twice for order BB-310729

PowerShell n’a pas de <<'EOF' : enregistrez les lignes Python dans lock1.py, dans le dossier du projet, puis exécutez python lock1.py.

Trois appels, trois résultats. Sans l’organisation, Python refuse l’appel avant d’exécuter du SQL. Avec Larkfield, le billet de Bramble « n’existe pas », ce qui correspond exactement au 404 de la dernière leçon. Avec Bramble Books, le billet est retourné. Une organisation oubliée produit maintenant une erreur visible en développement, plutôt qu’une fuite discrète en production.

Dans chaque fonction, l’organisation est la première condition de chaque instruction. La liste des billets commence ainsi :

where, params = ["t.tenant_id = %(tenant_id)s"], {"limit": limit + 1, "tenant_id": tenant_id}

Les filtres de statut, d’équipe et de client viennent ensuite. Un test, test_every_function_that_reads_an_organizations_records_needs_the_organization, vérifie la signature de neuf fonctions. Si quelqu’un ajoute une valeur par défaut, le test échoue.

Les clés étrangères du module 1 protègent les écritures. Un message pointe vers son billet par les deux colonnes, (tenant_id, ticket_id). La base refuse donc un message de Larkfield sur le billet T-40001 de Bramble avec une erreur ForeignKeyViolation (violation de clé étrangère), comme dans Utilisateurs et organisations. add_message() écrit l’organisation de l’appelant dans la nouvelle ligne, jamais une valeur du corps de la requête.

Le verrou 1 dépend des personnes. Il fonctionne tant que chaque requête passe par ces fonctions et que chaque fonction suit la règle. Un script, un rapport ou une correction rapide dans psql peut encore l’oublier.

Verrou 2 : la sécurité au niveau des lignes

PostgreSQL peut filtrer les lignes lui-même. Une table avec la sécurité au niveau des lignes possède des politiques. Ces règles décident, pour chaque ligne, si l’utilisateur actuel peut la voir ou l’écrire. La base ajoute la règle à chaque requête sur la table, qu’elle soit présente ou oubliée. C’est le verrou de la porte de l’hôtel.

La migration 017_row_level_security.sql ajoute une politique à chacune des sept tables qui contiennent les enregistrements d’une organisation :

ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON tickets
    USING (tenant_id = current_setting('app.tenant_id', true))
    WITH CHECK (tenant_id = current_setting('app.tenant_id', true));

USING (utilisation) filtre les lignes qu’une requête lit. WITH CHECK (avec vérification) refuse les lignes qu’une requête écrit. Les deux comparent le tenant_id de la ligne à un paramètre, app.tenant_id. L’API le définit au début de chaque transaction, à partir de l’appelant vérifié.

Un petit exemple, étape par étape

Le script scripts/rls_demo.py exécute quatre fois la requête qui oublie l’organisation, avec des paramètres différents. Il ne change rien : chaque étape se termine par une annulation de transaction.

python -m scripts.rls_demo

SELECT count(*) FROM tickets;
  1. as the Compose user, no organization              user tickets    app.tenant_id (empty)    -> 240 tickets
  2. as the Compose user, organization bramble         user tickets    app.tenant_id bramble    -> 240 tickets
  3. as ticket_app, no organization                    user ticket_app app.tenant_id (empty)    -> 0 tickets
  4. as ticket_app, organization bramble               user ticket_app app.tenant_id bramble    -> 40 tickets
  5. as ticket_app for bramble, insert a message for Larkfield's T-30002:
     InsufficientPrivilege: new row violates row-level security policy for table "messages"

La même requête qui oublie l'organisation, quatre fois. Les étapes 1 et 2 utilisent l'utilisateur Compose tickets; les étapes 3 et 4, le rôle ticket_app. Les étapes 2 et 4 définissent app.tenant_id à bramble.

Description textuelle du graphique

Un diagramme à quatre colonnes. Étape 1, l’utilisateur Compose sans organisation : 240. Étape 2, l’utilisateur Compose avec l’organisation bramble : encore 240. Étape 3, le rôle ticket_app sans organisation : 0. Étape 4, ticket_app avec bramble : 40, les billets de Bramble Books seulement.

L’étape 2 est la surprise. L’organisation est définie et la politique existe, mais le compte reste à 240. Regardez les deux rôles dans psql. Ouvrez-le avec docker compose exec db psql -U tickets -d tickets :

SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname IN ('tickets', 'ticket_app');

  rolname   | rolsuper | rolbypassrls
------------+----------+--------------
 tickets    | t        | t
 ticket_app | f        | f
(2 rows)

L’utilisateur tickets vient de compose.yaml. L’image PostgreSQL en fait un superutilisateur, et les politiques ne s’appliquent jamais à un superutilisateur. C’est le passe-partout de l’hôtel. Le propriétaire de la table contourne aussi ses politiques, sauf si la table a FORCE ROW LEVEL SECURITY (forcer la sécurité au niveau des lignes). La migration crée donc un rôle normal, ticket_app, sans ces pouvoirs, et l’API l’adopte dans chaque transaction. Les étapes 3 et 4 montrent le résultat. Sans organisation, ticket_app ne voit rien du tout. Avec Bramble, il voit les 40 billets de Bramble.

L’étape 5 teste WITH CHECK. La transaction concerne Bramble, mais le nouveau message nomme Larkfield, donc la base le refuse.

Deux verrous pour une règle. L'application refuse un appel sans organisation; la base filtre une requête qui l'oublie, mais seulement si l'API n'est pas un superutilisateur.

Description textuelle du diagramme

À gauche, le verrou 1 : un appel à get_ticket sans tenant_id échoue dans Python avec une TypeError, avant toute exécution SQL. À droite, le verrou 2 : l’API ouvre une transaction pour Bramble Books, adopte le rôle ticket_app et définit app.tenant_id. Une requête qui oublie WHERE tenant_id passe par la politique tenant_isolation et retourne 40 billets, seulement ceux de Bramble. La même requête exécutée par le superutilisateur tickets contourne la politique et retourne 240.

Comment l’API le définit

Ouvrez ticket_api/db.py. Quand DB_ROW_SECURITY=true, la valeur par défaut, chaque transaction pour une organisation commence par une instruction :

conn.execute(
    "SELECT set_config('role', 'ticket_app', true),"
    " set_config('app.tenant_id', %s, true)",
    (tenant_id,),
)

Les routes le demandent avec db.connection(caller.tenant_id), comme vous l’avez vu dans tickets.py. Le dernier argument, true, signifie local à la transaction. Les deux paramètres prennent fin avec la transaction, avant que la connexion retourne au pool. Ce détail compte, comme le montrent les cas d’échec ci-dessous.

Essayez une transaction à la main dans psql. Prédisez d’abord les deux comptes :

BEGIN;
SET LOCAL ROLE ticket_app;
SELECT set_config('app.tenant_id', 'larkfield', true);
SELECT count(*) FROM tickets;
SELECT count(*) FROM messages;
ROLLBACK;

Les comptes sont 200 et 331 : les billets et messages de Larkfield, comme dans le tableau de chargement du module 1. Après ROLLBACK (annuler la transaction), SELECT current_user; affiche de nouveau tickets. Les tables tenants, users et memberships n’ont aucune politique, donc SELECT count(*) FROM tenants; affiche encore 2 dans la transaction. La vérification d’adhésion de la dernière leçon doit les lire avant de définir une organisation.

Certains programmes se connectent encore comme superutilisateur, volontairement. Ce sont les migrations, le chargeur scripts.load, la fixture conn des tests et la prise d’une tâche par le processus en arrière-plan du prochain module. Ils travaillent sur plusieurs organisations ou créent les tables. Chacun est un passe-partout, donc gardez cette liste courte et connue.

Son coût : les mesures

Un deuxième verrou a un coût. L’API envoie une instruction de plus par requête. Le script de mesure du cours sur les bases de données le montre, avec le jeton d’Ines sur la liste de Bramble. Il démarre sa propre copie de l’API sur le port 8765, donc ce port doit être libre :

python -m scripts.bench --tenant bramble --user usr-ines
python -m scripts.bench --tenant bramble --user usr-ines --row-security off

La dernière ligne de chaque exécution indique errors: 0 {200: 200} | database queries per request: 3 (aucune erreur, trois requêtes de base de données par requête HTTP), puis … per request: 2 (deux par requête HTTP). Les trois requêtes SQL sont la vérification d’adhésion, les paramètres et la page. Les temps sont omis ici : ils seront différents sur votre ordinateur.

Le cours a mesuré le temps sur le grand jeu de données : 300 000 billets de Larkfield et les 40 de Bramble. Chaque cas a fait l’objet de 5 passages de 200 requêtes, 4 à la fois. Peu d’autres programmes tournaient sur l’ordinateur pendant les passages.

Le point de terminaison de la liste des billets, avec la sécurité au niveau des lignes désactivée puis activée, sur le grand jeu de données : cinq passages par cas. Avec le verrou activé, chaque requête exécute une instruction de plus; les passages avec et sans verrou se chevauchent quand même.

Description textuelle du graphique

Quatre rangées de cinq points chacune, un point par passage, en millisecondes :

• Larkfield, sécurité au niveau des lignes désactivée : 4,3, 4,2, 4,3, 4,5 et 4,2; médiane de 4,3.
• Larkfield, activée : 4,4, 4,4, 4,5, 4,4 et 5,0; médiane de 4,4.
• Bramble Books, désactivée : 4,3, 4,0, 4,6, 5,0 et 4,6; médiane de 4,6.
• Bramble Books, activée : 5,2, 5,0, 5,0, 7,8 et 4,8; médiane de 5,0.

Les points avec et sans sécurité se chevauchent dans les deux organisations.

Lisez les points, pas seulement les médianes. Avec le verrou activé, la médiane de Larkfield était de 4,4 ms contre 4,3, et celle de Bramble, de 5,0 contre 4,6. Cela représente de 0,1 à 0,4 ms par requête : le coût de l’instruction supplémentaire qui définit l’organisation. Les points avec et sans verrou se chevauchent, donc le coût est à la limite du bruit de mesure. Dans la base, la requête de liste de Larkfield prenait 0,100 ms, sans politiques et avec le rôle ticket_app. Le plan montre la politique comme un One-Time Filter (filtre appliqué une seule fois), vérifié une fois par requête :

One-Time Filter: (current_setting('app.tenant_id'::text, true) = 'larkfield'::text)

La migration 016_tenant_indexes.sql a modifié l’index de la file pour qu’il commence par tenant_id, puisque chaque requête de liste commence maintenant ainsi. Vérifiez le plan pour Bramble :

python -m scripts.explain queue --tenant bramble

->  Bitmap Index Scan on tickets_tenant_queue_idx  (cost=0.00..4.29 rows=2 width=0) (actual time=0.067..0.068 rows=3.00 loops=1)
      Index Cond: ((tenant_id = 'bramble'::text) AND (status = 'open'::text) AND (team = 'billing'::text))

Ce sont deux lignes d’un plan plus long. Une remarque honnête : le cours s’attendait à un « voisin bruyant » sans cet index. Cela désigne une petite organisation dont la liste parcourt d’abord les lignes de la grande organisation. Ce phénomène n’est pas apparu sur le grand jeu de données. Avec l’ancien index du cours sur les bases de données, la liste de Bramble prenait 0,035 ms plutôt que 0,026 ms. Le planificateur utilisait un autre index commençant par tenant_id, la clé (tenant_id, ticket_id). Son filtre retirait 37 lignes, toutes des billets de Bramble, aucune de Larkfield. Le nouvel index a tout de même la bonne structure, mais à cette taille, la différence n’est pas spectaculaire.

Quand l’utiliser, et quand s’en passer

Utilisez toujours le verrou 1 : il ne coûte rien et détecte l’erreur là où elle se produit. Ajoutez la sécurité au niveau des lignes quand beaucoup de personnes écrivent des requêtes ou que des scripts et rapports accèdent aux mêmes tables. Ajoutez-la aussi quand une seule fuite coûterait très cher. Dans ce cours, elle est activée par défaut, car elle coûte une instruction par requête, une fraction de milliseconde.

Ne la traitez pas comme le seul verrou. Les politiques sont loin du code, donc un développeur peut oublier leur existence. Un superutilisateur les contourne sans aucun message. Il faut aussi gérer les pools avec soin : le paramètre doit prendre fin avec la transaction. Et elle ne remplace pas les quatre vérifications. La base connaît l’organisation seulement parce que l’API l’a définie à partir d’une adhésion vérifiée.

Erreurs fréquentes

Erreur courante

La sécurité au niveau des lignes est activée, et la requête retourne encore 240 lignes

Symptôme : vous avez activé les politiques et défini app.tenant_id, mais chaque compte couvre encore les deux boutiques. C’est l’étape 2 de rls_demo.

Cause : l’utilisateur de la connexion est un superutilisateur ou le propriétaire de la table. Les politiques ne s’appliquent pas à eux, et PostgreSQL ne donne aucun avertissement.

Diagnostic : exécutez SELECT current_user; dans la transaction, et vérifiez rolsuper dans pg_roles.

Correction : adoptez un rôle normal dans chaque transaction, comme le fait db.py, ou connectez-vous avec un rôle normal dès le départ. Pour un propriétaire de table, ajoutez ALTER TABLE … FORCE ROW LEVEL SECURITY.

Erreur courante

Un paramètre survit à sa requête

Symptôme : une requête sans organisation voit les lignes d’une autre organisation. Cela arrive seulement parfois, et seulement sous charge.

Cause : le code a défini le paramètre pour toute la session (set_config(…, false) ou SET sans LOCAL). Le pool donne la même connexion à la requête suivante, avec le paramètre. Ce script construit le montre. Enregistrez-le sous setting_leak.py dans le dossier du projet :

# Constructed for the course: a setting that outlives its request.
from psycopg_pool import ConnectionPool

from ticket_api.config import load_env, read_secret

load_env()
for local in (False, True):
    with ConnectionPool(read_secret("DATABASE_URL"), min_size=1, max_size=1) as pool:
        with pool.connection() as conn:  # request 1: Ines, Bramble Books
            conn.execute("SELECT set_config('app.tenant_id', 'bramble', %s)", (local,))
        with pool.connection() as conn:  # request 2: the next caller, the same connection
            value = conn.execute("SELECT current_setting('app.tenant_id', true)").fetchone()[0]
        print(f"is_local={local}: request 2 sees app.tenant_id = {value!r}")

python setting_leak.py

is_local=False: request 2 sees app.tenant_id = 'bramble'
is_local=True: request 2 sees app.tenant_id = ''

Correction : rendez toujours les deux paramètres locaux à la transaction (true, ou SET LOCAL). Le test test_the_settings_end_with_the_transaction le vérifie. Supprimez setting_leak.py après le test.

Erreur courante

TypeError : missing 1 required keyword-only argument : 'tenant_id' (argument nommé obligatoire manquant : tenant_id)

Symptôme : une nouvelle route ou un nouveau script s’arrête avec cette erreur.

Cause : le verrou 1 fonctionne : l’appel n’a pas d’organisation.

Correction : passez l’organisation de l’appelant vérifié, tenant_id=caller.tenant_id. N’ajoutez jamais une valeur par défaut pour faire disparaître l’erreur.

Réinitialisation. rls_demo et la transaction psql ne changent rien. Si vous avez inséré des lignes à la main, exécutez python -m scripts.load --reset.

De bout en boutAjouter la connexion et les rôles à une API existanteÉtude de cas

L’étude de cas ajoute un argument de service obligatoire à chaque requête d’une API de données. Sans cet argument, un membre des parcs recevait 94 demandes de transport parmi les 100 premières. Avec cet argument, aucune.

Vérification des connaissances

Vérifiez votre compréhension

Expliquez pourquoi définir app.tenant_id à bramble n'a pas changé le compte pour l'utilisateur Compose tickets, et comment l'API gère cela.

Afficher la réponse

L’utilisateur tickets est un superutilisateur (rolsuper et rolbypassrls valent vrai). PostgreSQL n’applique jamais les politiques de sécurité au niveau des lignes à un superutilisateur. Le compte reste donc à 240. L’API passe au rôle normal ticket_app au début de chaque transaction, avec set_config('role', 'ticket_app', true). Elle définit aussi app.tenant_id dans la même instruction. Les politiques s’appliquent alors.

Vérifiez votre compréhension

Prédisez : dans une transaction sous ticket_app avec app.tenant_id = 'bramble', que donnent ces opérations? (a) SELECT count(*) FROM messages; (b) SELECT count(*) FROM tickets WHERE ticket_id = 'T-30002'; (c) un INSERT d'un message avec tenant_id 'bramble' pour T-40001.

Afficher la réponse

(a) 58 : seulement les messages de Bramble, comme dans le tableau du chargement. (b) 0 : T-30002 appartient à Larkfield, et la politique le masque, comme s’il n’existait pas. (c) L’insertion fonctionne : le tenant_id de la ligne correspond au paramètre, donc la vérification WITH CHECK réussit. La clé étrangère composite trouve aussi le billet T-40001 de Bramble. Annulez la transaction pour ne pas conserver le message supplémentaire.

Vérifiez votre compréhension

Vérification pratique : exécutez python -m scripts.rls_demo. Quelle étape montre la clé maîtresse, et quelle ligne montre qu'une écriture dans une autre organisation est refusée?

Afficher la réponse

Étape 2 : l’utilisateur Compose tickets, avec app.tenant_id défini à bramble, compte toujours 240 billets. L’étape 5 se termine par InsufficientPrivilege: new row violates row-level security policy for table "messages" (privilège insuffisant : la nouvelle ligne enfreint la politique de sécurité au niveau des lignes de la table « messages »).

Récapitulatif

• Verrou 1 : tenant_id est un argument nommé obligatoire de chaque fonction du dépôt, et la première condition de chaque instruction. Oublier l’organisation provoque une erreur TypeError. Les clés étrangères composites refusent les liens entre organisations.
• Verrou 2 : les politiques de sécurité au niveau des lignes filtrent et vérifient chaque ligne selon app.tenant_id. Oublier un WHERE renvoie 40 billets pour Bramble, et non 240.
• Les politiques ne s’appliquent jamais à un superutilisateur. L’utilisateur Compose tickets en est un, donc l’API passe à ticket_app dans chaque transaction. Les deux paramètres sont locaux à la transaction.
• Coût : une instruction supplémentaire par requête (3 plutôt que 2). Selon la mesure du cours, cela ajoute de 0,1 à 0,4 ms par requête, à la limite du bruit.

Ensuite, l’organisation suit les données hors des tables : dans les liens de fichiers, la recherche de billets similaires, les citations de documents et le cache.

Leçon suivante : Accès aux fichiers et à la récupération