Miage-BDAV

Page principale de l'EC "Bases de données avancées" en M1 Miage UNC

TP les transactions et les niveaux d’isolation en PostgreSQL

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 transfert entre comptes bancaires. La relation considérée est la suivante :

CREATE TABLE 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.

Il faut faire attention à la gestion des sessions pas votre client favori et désactier si nécessaire l'auto-commit. Avec psql, 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 ou fenêtres !
  • Pensez bien à remettre la table account à son état initial entre deux tests. Mettre le script des INSERT dans un fichier .sql que l’on charge avec \i avec par exemple le fichier reset.sql.
  • 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

Remarque : les sections et questions marquées d’une astérisque (*) sont des approfondissements qui peuvent être laissés de côté en première lecture.

Gare au vérrou

Un deadlock, dit en français étreinte mortelle, est une situation de blocage qui survient lorsque deux transactions concurrentes accèdent de façon croisées à deux ressources protégées 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
  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 (A) est bloqué en voulant lire R2 et (B) est bloqué 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 qui modifie le solde de alice puis celui de bob;
    2. une transaction 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 attendues :

  • 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

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 tout autant côté client que côté serveur 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 manuel, ce script contient une temporisation pg_sleep_for('10 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 et l’exécuter pour créer la fonction account_transfert dans la base bdav.

On va montrer que la situation où deux transactions conuurentes 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 acid_rain.py le driver PostgreSQL pyscopg3 (le plus connu) en asynchrone pour que l’exécution des requêtes soit concurrente et non bloquante.

Remarque installer les modules Python psycopg et python_dotenv avec la commande pip install psycopg python_dotenv.

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

PGUSER=bdav
PGPASSWORD=bdav
PGHOST=localhost
PGPORT=5432
PGDATABASE=bdav
PGSCHEMA=public
PGAPPNAME=acid-rain
  • Expliquer ce que fait le programme acid_rain.py. Utiliser l’option -vv de la ligne de commande pour cela (voir l’aide python acid_rain.py --help).
  • Exécuter ce script en jouant sur l’argument --parallel et 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 à -10000€ (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.
  • (*) Une nouvelle connection est crée par chacune les fonctions reset() et transfer(). Ce n’est pas performant car l’établissement d’une connection PostgreSQL est particulièrement lourd, il y a même des outils pour cela comme https://www.pgbouncer.org/. Dans le cas présent de démonstration de l’attaque ACIDRain, peut-on partager la connection entre les appels de transfert ?
  • (*) On peut améliorer le programme en relancant la requête qui échoue. Attention toutefois à ne pas créer de programme qui boucle en essayant indéfiniment.

Nécessité du niveau SERIALIZABLE

L’attaque ACIDRain précédente est facilement mitigée 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) du script.
  • 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.
  • (*) Adapter le script acid_rain.py pour qu’il execute désormais un appel à smooth au lieu de account_transfert en tirant le bénéficiaire au hasard dans la liste des comptes existants.