BDAV2

Page complémentaire de l'EC bases de données avancées 2 en L3 UNC édition 2022

TP transactions et niveaux d’isolation – 2022

Introduction

Le but de ce TP est d’apprécier en pratique les risques liés à une mauvaise configuration du niveau d’isolation, en particulier, de voir que le niveau par défaut READ COMMITTED permet de corrompre la base. Le cas d’étude est celui de transferts entre comptes bancaires. L’unique relation considérée est la relation account suivante, voir le fichier reset.sql.

CREATE TABLE IF NOT EXISTS account (
    name varchar(8) PRIMARY KEY,
    balance integer
);

TRUNCATE account;
INSERT INTO account VALUES
    ('alice',   1000),
    ('bob',     2000),
    ('charlie', 1500),
    ('denise',     0);

Le cas est largement simplifié mais le problème est bien réel.

Documentation de référence

Mise en place

Il faut créer une base de données et un utilisateur dédié avec un mot de passe pour ce TP. Pour cela, autoriser les connections réseaux à PostgreSQL, avec un compte privilégié (postgres par défaut) :

  • ajouter la ligne host all all 127.0.0.1/32 scram-sha-256 dans le fichier pg_hba.conf
  • (optionel) modifier la ligne listen_addresses = '*' du fichier postgresql.conf
  • redémarrer le serveur

Ensuite, avec le compte PostgreSQL nommé postgres, créer la base et l’utilisateur en exécutant les commandes suivantes :

  • CREATE USER bdav WITH password 'bdav';
  • CREATE DATABASE bdav WITH OWNER bdav;

Ensuite, vérifier que vous pouvez vous connecter sur localhost avec le compte bdav sur la base bdav puis créer la relation account avec ses quatre tuples via le fichier reset.sql.

Encodage des caractères sous Windows

Si vous avez un message warning à propos de l’encodage des caractères dans psql sous Windows :

  • exécutez chcp 1252 dans votre terminal avant d’exécuter psql
  • soit ajouter la ligne chcp 1252 dans C:\Program Files\PostgreSQL\14\scripts\runpsql.bat

Conseils

  • Vous allez utiliser jusqu’à 3 ou 4 sessions parallèles : organisez votre écran et vos terminaux
    • Eviter les IDEs car ils contrôlent chacun les transactions implicites à leur façon.
  • Pensez bien à remettre la table account à son état initial entre deux tests.
  • Vous pouvez exécuter TABLE account ORDER BY name; entre les étapes de la transactions pour suivre l’évolution.
    • TABLE my_table ... est une commande spécifique PostgreSQL alias pour SELECT * FROM my_table ....
  • Dans psql, vous pouvez ajouter à la fin d’une commande \watch n pour la relancer toutes les n secondes, par exemple SELECT SUM(balance) FROM account; \watch 1

Modalités de rendu et d’évaluation

Le travail est à réaliser en binôme ou en monôme. Il est à rendre pour au plus tard vendredi 16 septembre 2022 à 23h59 sur https://foad.unc.nc/. Un modèle de rapport est fourni. Il sera à compléter avec la démonstration du fonctionnement :

  • soit avec des images screenshots;
  • recommandé soit avec des vidéos screencast (à déposer sur le moodle si < 50MB ou YouTube sinon), vous pouvez utiliser par exemple https://obsproject.com/.

Barème prévisionnel

  • Q1 – Démonstration de deadlocks (coeff 1.5)
  • Q2 - Attaque par race condition (coeff 1.5)
  • Q3 – Augmentation du niveau d’isolation, REPEATABLE READ (coeff 1)
  • Q4 - Verrou explicite, LOCK (coeff 1)
  • Q5 – Attaque acid rain manuelle (coeff 1)
  • Q6 – Transactions concurrentes automatisées (coeff 3)
  • Q7 – Nécessité du niveau SERIALIZABLE (coeff 2)
  • Qualité du rapport et des médias (coeff 2)
    • prend en compte la qualité de la rédaction, la clarté des explications, la précision technique et la qualité et la pertinence des captures d’écrans ou vidéos.

Gare au vérrou

Un deadlock, dit en français étreinte mortelle, est une situation de blocage qui survient lorsque deux transactions concurrentes notées (A) et (B) accèdent de façon croisées à deux ressources protégées R1 et R2 comme ci-après :

  1. (A) demande l’accès exclusif à R1 (pour faire une écriture par exemple)
  2. (B) demande l’accès exclusif à R2 (pour faire une écriture par exemple)
  3. (A) demande à lire R2
  4. (B) demande à lire R1
  5. (A) relache le vérrou sur R1
  6. (B) relache le vérrou sur R2

Dans cette situation, les étapes 5 et 6 n’auront jamais lieu car la transaction (A) est bloquée en voulant lire R2 tandis que la transaction (B) est bloquée en voulant lire R1. Notons que R1 et R2 peuvent être la même ressource. L’exécution d’une commande UPDATE sur account pose implicitement un vérrou sur la ressource (pour suspendre les actions concurrentes) et peut conduire à cette situation de blocage.

  • Montrer qu’il est possible de produire une situation de deadlock sur la table account, avec en concurrence :
    1. une transaction (A) qui modifie le solde de alice puis celui de bob;
    2. une transaction (B) qui modifie le solde de bob puis celui de alice.
  • Quelle est le message d’erreur produit par PostgreSQL quand il détecte le deadlock ?
  • Quelle est la stratégie de résolution de PostgreSQL dans cette situation ?
  • Trouver dans la documentation à quelle fréquence PostgreSQL essaie de détecter ces erreurs.

Création de monnaie par race condition entre transferts

On considère la transaction suivante qui exprime le transfert de tous les fonds de alice vers bob :

-- READ COMMITTED est le niveau par défaut, on le précise ici
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

    UPDATE account
    SET balance = balance + (SELECT balance FROM account WHERE name = 'alice')
    WHERE name = 'bob';

    UPDATE account
    SET balance = 0
    WHERE name = 'alice';

END TRANSACTION;

Cette transaction a deux propriétés souhaitée de cohérence :

  • la somme totale des balances ne change pas, c’est un invariant, la requête SELECT SUM(balance) FROM account; doit toujours renvoyer la même chose avant et après l’exécution de la transaction ;
  • elle est idempotente : exécuter deux fois (ou plus) la transaction sur un état de départ doit faire la même chose que l’exécuter une seule fois.

On considère deux exécutions parallèles de exactement la même transaction, que vous avancez pas-à-pas.

  • Montrer qu’il est possible de créer 1000€ avec un certain ordre d’exécution des commandes de la transaction. Donner cet ordre en prenant bien soin de distinguer les sessions où les commandes sont exécutées.

Solution par augmentation du niveau d’isolation

  • Montrer que ce problème disparait avec le niveau d’isolation REPEATABLE READ.
  • Relever l’erreur produite lors de la concurrence avec ce niveau d’isolation.

Solution par verrou explicite niveau tuple

La section locking rows dans la documentation explique le fonctionnement de la clause FOR UPDATE : si celle ci apparait dans une requête SELECT alors l’opération est considérée comme un UPDATE et pose un verrou sur les tuples concernés par la lecture, comme s’ils étaient modifiés.

  • Montrer qu’une autre solution au problème en restant en READ COMMITTED consiste à rajouter la clause FOR UPDATE dans la sous-requête SELECT balance FROM account WHERE name = 'alice'.

On peut suivre les verrous tuples (dits row-level locks) sur la table account en exécutant la requête SELECT (page, tuple) FROM pg_locks WHERE (relation = 'account'::regclass) AND locktype = 'tuple'; depuis une 3ème session.

  • Rejouer le scénario READ COMMITTED original en suivant la liste des verrous tuples
  • Rejouer le scénario avec le verrou explicite SELECT ...FOR UPDATE
  • Le verrou ne porte pas sur le même tuple dans les deux scénarios, vérifier le et indiquer quels sont les tuples concernés dans chacun des scenarios.

Création de monnaie par race condition entre vérification et exécution

On considère maintenant la transaction suivante où seulement 750€ sont transférés du compte alice vers bob :

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;

    UPDATE account SET balance = balance + 750
    WHERE name = 'alice';

    UPDATE account SET balance = balance - 750
    WHERE name = 'bob';

END TRANSACTION;
  • Pourquoi cette nouvelle transaction n’est-elle pas affectée par le problème précédent qui survenait quand on transfère tout les fonds d’alice ?

On va voir que même si cet extrait de code n’a pas de problème, en intéraction avec d’autres lectures il va être vulnérable à des attaques.

Attaque ACIDRain manuelle

Dans une application de transfert, on n’autorisera pas un transfert si le débiteur de dispose pas des fonds suffisants :

  1. on lit le solde de alice
  2. si alice dispose d’au moins 750€ alors on les transfère vers bob.

Ce cas est plus réaliste que le précédent et peut arriver si la lecture et les écritures ne sont pas dans les mêmes transactions ou si le niveau d’isolation est trop faible. On peut alors monter une attaque de type ACIDRain dans une application web par exemple, voir une exégèse et l’article original Sigmod 2017.

Considérons le script do_transfert.sql qui reproduit cette situation côté serveur en PL/pgSQL avec un block de commandes anonymes DO, voir https://www.postgresql.org/docs/current/sql-do.html. Pour faciliter les tests manuels, ce script contient une temporisation pg_sleep_for('5 seconds') que vous pourrez régler.

  • montrer qu’en modifiant le solde de alice dans une transaction concurrente (e.g., UPDATE account SET balance = 0 where name = 'alice';) on peut lui faire obtenir un solde négatif, ce qui a priori n’est pas censé être possible.
  • quel est le comportement de PostgreSQL dans ce scénario d’attaque :
    • quand on spécifie le niveau d’isolation REPEATABLE READ ?
    • quand on ajoute la clause FOR UPDATE dans le SELECT du block ?

Transactions concurrentes automatisées

On considère le scénrio précédent de façon encore plus réaliste, c’est-à-dire sans temporisation et sous forme d’une fonction PL/pgSQL account_transfert(v_name_from text, v_name_to text, v_amount integer) stockée côté serveur avec les trois paramètres évidents. Voir le fichier account_transfert.sql Exécuter le script pour créer la fonction account_transfert() dans la base bdav.

On va montrer que la situation où deux transactions concurrentes arrivent à corrompre l’état de la base est assez facile à obtenir et qu’en présence de concurrence d’accès, la question n’est pas de savoir si la corruption survient mais quand.

On donne un programme Python run_asyncpg.py qui utilise une bibliothèque asyncpg alternative au driver PostgreSQL pyscopg (le plus connu). Le driver asyncpg est nativement asynchrone, voir https://magicstack.github.io/asyncpg/current/. Ainsi, les exécutions de requêtes sont concurrentes et non bloquantes. Il faut utiliser asyncio pour contrôler l’exécution.

Remarque installer les modules Python asyncpg et python_dotenv avec la commande python -m pip install asyncpg python_dotenv.

Avant de commencer, créer un fichier .env au contenu comme suit dans le dossier où le script est téléchargé :

HOST=localhost
PORT=5432
DATABASE=bdav
USER=bdav
PASSWORD=bdav
  • Expliquer ce que fait le programme run_asyncpg.py. Utiliser l’option -vv de la ligne de commande
    • pour cela voir l’aide python run_asyncpg.py --help et l’option -vv lors de l’exécution
  • Vérifier empiriquement qu’il est vraisemblable qu’alice obtienne un solde négatif.
  • Estimer le taux de succès de l’attaque et essayer de faire descendre le solde d’alice à -10.000€
    • les résultats seront très variables car ils dépendent de votre OS, de votre machine, de sa charge etc.
  • Augmenter le niveau d’isolation et refaire la même expérience. Relevez l’exception obtenue.
  • Corriger votre programme Python pour qu’il ne crashe pas lors d’une exception
    • soit en la traitant dans un block try ... except ... finally dans la fonction transfer(),
    • soit avec la bonne option de asyncio.gather()

Nécessité du niveau SERIALIZABLE

L’attaque ACIDRain précédente est facilement mitigée (angliscime pour mitigate) en utilisant le niveau d’isolation REPEATABLE READ. On va montrer qu’il existe des cas où ce niveau n’est pas suffisant, on considère pour cela le script SQL smooth.sql.

  • Expliquer ce que fait la procédure smooth(v_total integer, v_name text).
  • Créer la procédure puis la tester. Une procédure s’appelle avec la commande CALL, comme par exemple CALL smooth(1000, 'alice');.
  • Vérifier qu’on peut atteindre un état incohérent avec 2 tâches asynchrones, aux niveaux read_committed mais aussi repeatable_read. Pour cela exécuter la procédure smooth() sur deux comptes différents.
  • Pourquoi en revanche, quand la procédure est appellée en parallèle sur le même compte une erreur est bien relevée au niveau repeatable_read ?
  • Vérifier que le niveau serializable en revanche garanti la cohérence.