Des fonctions SQL pour jouer côté serveur

Écrire les fonctions demarrer_partie et repondre : le serveur tire les questions, enregistre les réponses et les corrige.

Objectifs de la séance

• comprendre le principe d'une fonction SQL appelée depuis l'application (RPC) ;
• créer les tables partie et partie_question et les fermer à l'écriture directe ;
• écrire des fonctions PL/pgSQL : variables, if, raise exception, for update, JSON ;
• comprendre security definer, set search_path = '' et pourquoi une telle fonction doit tout vérifier ;
• retirer le droit d'exécution aux visiteurs non connectés.

---

Petite vérification

Avant de commencer, vérifiez si vous savez...

1. dans un terminal, lancer la commande curl de la séance précédente qui ne renvoie que les bonnes réponses, et compter les lignes obtenues
2. citer les deux séances qui corrigeront la faille des bonnes réponses lisibles par tous (numéros de séance du tutoriel 8)
3. expliquer pourquoi cacher la colonne est_correcte dans le code de l'application ne protège pas les réponses

📌 Réponse attendue

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Notions théoriques

Le principe : l'application demande, le serveur décide

Au lieu de télécharger toutes les questions avec leurs bonnes réponses, l'application va dialoguer avec le serveur en trois temps :

1. demarrer_partie(theme, niveau, nombre)   crée la partie, tire les questions AU HASARD,
                                            renvoie les questions SANS est_correcte
2. repondre(partie, question, reponse)      enregistre le choix du joueur,
                                            renvoie la correction (bonne réponse + explication)
3. terminer_partie(partie)                  calcule le score et la durée   (séance 60)

Les bonnes réponses ne quittent le serveur qu'après que le joueur a répondu, une question à la fois.

Qu'est-ce qu'une fonction SQL appelée depuis l'application (RPC) ?

Une fonction SQL est un petit programme stocké dans la base de données. RPC signifie Remote Procedure Call (appel de procédure à distance). Supabase (grâce à PostgREST) expose chaque fonction du schéma public à l'adresse /rest/v1/rpc/nom_de_la_fonction. Dans l'application, on l'appelle ainsi :

const { data, error } = await supabase.rpc("repondre", {
  p_partie_id: 12,
  p_question_id: 4,
  p_reponse_id: 41,
});

Les paramètres sont envoyés par nom. Nous préfixons leurs noms par p_ pour ne jamais les confondre avec des colonnes. Les variables locales, elles, commencent par v_.

PL/pgSQL : programmer dans la base

Les fonctions de ce cours sont écrites en PL/pgSQL, le langage de programmation de PostgreSQL :

Élément	Exemple	Rôle
declare	v_partie_id bigint;	déclare des variables
:=	v_joueur_id uuid := auth.uid();	affecte une valeur
if ... then ... end if;	if v_joueur_id is null then ... end if;	condition
select ... into	select ... into v_ligne from ...	range le résultat d'une requête dans une variable
found	if not found then ...	vrai si la dernière requête a trouvé au moins une ligne
raise exception	raise exception 'Utilisateur non connecté';	arrête la fonction et annule tout ce qu'elle avait écrit

Une erreur levée par raise exception arrive dans l'application avec le code P0001 et le message écrit par nous. Comme nous l'écrivons pour le joueur (en français), l'application pourra l'afficher telle quelle.

security definer : des droits empruntés à la fonction

Une fonction SQL peut s'exécuter de deux façons :

• security invoker (par défaut) : avec les droits de celui qui l'appelle ;
• security definer : avec les droits de celui qui l'a créée (ici le propriétaire du projet, qui contourne la RLS).

Nous avons besoin du second : les tables partie et partie_question sont fermées à l'écriture directe (aucun droit pour le joueur). Seules nos fonctions, qui s'exécutent avec des droits élevés, peuvent y écrire, et seulement comme nous l'avons décidé.

Une fonction security definer sans vérification est une faille

Parce qu'elle ignore la RLS, une fonction security definer peut faire tout ce qu'on lui permet, pour n'importe quel appelant. Elle doit donc vérifier elle-même : qui appelle (auth.uid()), si les paramètres sont valides, si l'objet visé appartient bien à l'appelant. Une fonction qui accepte un paramètre p_joueur_id fourni par l'application permet à n'importe quel joueur de se faire passer pour un autre. Notre fonction ne lit jamais l'identité dans un paramètre : toujours dans auth.uid().

set search_path = '' : se protéger du détournement de noms

Quand on écrit partie sans préciser le schéma, PostgreSQL cherche la table dans une liste de schémas (le search_path). Un attaquant qui sait créer un objet portant le même nom dans un schéma placé avant le nôtre pourrait détourner la fonction. Avec set search_path = '', la liste est vide : on écrit donc toujours le nom complet, public.partie, public.question... C'est une précaution obligatoire pour les fonctions security definer.

auth.uid() et la validation des paramètres

• auth.uid() renvoie l'uuid du joueur contenu dans son jeton, ou null s'il n'y a pas de session. La fonction refuse alors de continuer.
• Aucun paramètre n'est jamais cru : le nombre de questions doit être compris entre 1 et 20, le niveau entre 1 et 3, le joueur doit avoir un pseudo.
• Anti-abus : un joueur ne peut pas créer plus de 10 parties par minute (un script pourrait sinon en créer des milliers).

Les tables partie et partie_question

PARTIE (id, joueur_id, theme_code, niveau, nb_questions, debut_le, terminee_le, score, duree_secondes)
PARTIE_QUESTION (partie_id, question_id, ordre, reponse_id, est_correcte, repondu_le)

PARTIE 1,n ---- contient ---- 1,1 PARTIE_QUESTION        JOUEUR 1,n ---- joue ---- 1,1 PARTIE

• partie_question a pour clé primaire (partie_id, question_id) : une question au plus une fois par partie ; unique (partie_id, ordre) garantit des positions distinctes ;
• reponse_id, est_correcte, repondu_le valent null tant que le joueur n'a pas répondu : null = pas encore répondu ;
• les deux tables sont fermées (revoke all) : seul le joueur peut lire ses propres parties (un grant select et une règle RLS), tout le reste passe par les fonctions.

Tirer des questions au hasard côté serveur

Le tirage se fait en SQL avec order by random() : chaque question reçoit un nombre aléatoire (alea), on trie, on garde les p_nb_questions premières, puis row_number() leur donne un numéro d'ordre. Cette méthode est simple et adaptée à une petite banque de questions (elle serait trop lente sur des millions de lignes). Si le tirage donne moins de questions que demandé, la fonction lève une exception : toute la fonction est annulée, y compris la création de la partie.

Verrouiller une ligne : for update

Que se passe-t-il si le joueur double-clique et que deux appels à repondre arrivent en même temps pour la même question ? Avec select ... for update, le premier appel verrouille la ligne de partie_question ; le second attend qu'il ait fini. Quand il peut enfin lire la ligne, reponse_id n'est plus null : il renvoie simplement la correction de la première réponse. Le résultat est le même qu'un appel unique (on dit que la fonction est idempotente), et le joueur ne peut pas changer sa réponse en la renvoyant.

Qui peut appeler la fonction ? Les droits d'exécution

Par défaut, PostgreSQL donne le droit d'exécuter toute nouvelle fonction à tout le monde (le pseudo-rôle public). Il faut donc le retirer explicitement, puis l'accorder aux seuls joueurs connectés :

revoke execute on function public.repondre(bigint, bigint, bigint) from public, anon;
grant execute on function public.repondre(bigint, bigint, bigint) to authenticated;

Le nom de la fonction est suivi de la liste des types de ses paramètres, car PostgreSQL permet plusieurs fonctions du même nom.

Construire du JSON en SQL

jsonb_build_object('cle', valeur, ...) construit un objet JSON ; jsonb_agg(...) rassemble plusieurs lignes en un tableau JSON. C'est ce qui permet à demarrer_partie de renvoyer, en une seule réponse, la partie et toutes ses questions avec leurs réponses sans la colonne est_correcte.

---

TP pour réfléchir et résoudre des problèmes

Vous allez écrire le script sql/06_partie.sql en plusieurs morceaux, puis l'exécuter en une seule fois. Dans l'application, rien ne change dans cette séance : la séance 50 utilisera ces fonctions. Restez sur la branche feature/securite.

Étape 1 — Créer les tables partie et partie_question

Créez sql/06_partie.sql. Commencez par la table partie (une ligne par partie jouée), qui suit le même modèle que joueur :

-- Une partie jouée par un joueur
create table public.partie (
  id             bigint generated by default as identity primary key,
  joueur_id      uuid not null references public.joueur (id) on delete cascade,
  theme_code     text null references public.theme (code),          -- null = tous les thèmes
  niveau         smallint null check (niveau between 1 and 3),      -- null = tous les niveaux
  nb_questions   smallint not null check (nb_questions between 1 and 20),
  debut_le       timestamptz not null default now(),
  terminee_le    timestamptz null,                                   -- null = partie en cours
  score          smallint null,
  duree_secondes integer null
);

create index partie_joueur_idx on public.partie (joueur_id);

Écrivez ensuite partie_question. Complétez la ligne de la clé primaire, qui interdit qu'une même question apparaisse deux fois dans la même partie :

Bonne pratique - Des contraintes qui portent les règles métier

« Une question au plus une fois par partie » et « positions distinctes » ne sont pas des vérifications du code : ce sont des contraintes de la base. Aucun bug de l'application ne pourra les violer.

Étape 2 — Fermer les tables à l'écriture directe

Ajoutez la RLS et les droits. Le joueur n'aura aucun droit d'écriture sur ces deux tables : il pourra seulement lire ses propres parties. Complétez la ligne qui retire tous les droits :

Bonne pratique - Si on ne peut pas écrire, on ne peut pas tricher

Un joueur qui ne peut ni insérer dans partie, ni modifier son score, ne peut pas fabriquer une fausse partie. Les seules écritures possibles sont celles que nos fonctions acceptent.

Étape 3 — Déclarer la fonction demarrer_partie

Voici l'en-tête de la fonction. Chaque ligne a un rôle de sécurité. Complétez celle qui vide le chemin de recherche des schémas :

Bonne pratique - Toujours qualifier les noms

Avec search_path vide, partie ne veut plus rien dire : on écrit public.partie, public.joueur... Si vous oubliez, PostgreSQL répond « relation does not exist » : l'erreur est bruyante, donc facile à corriger.

Étape 4 — Vérifier avant d'agir

Dans le corps de la fonction, déclarez les variables puis vérifiez tout avant de créer quoi que ce soit. Complétez la première vérification :

Bonne pratique - Des messages d'erreur pour l'utilisateur

Chaque raise exception est rédigé en français, à destination du joueur. Il n'y a rien de technique à cacher : « Choisissez un pseudo avant de jouer » est un message utile que l'application affichera tel quel.

Étape 5 — Créer la partie, tirer les questions, renvoyer le JSON

La suite de la fonction crée la partie, tire les questions au hasard, vérifie que le nombre de questions tirées est suffisant et construit la réponse JSON. Complétez la ligne qui récupère le nombre de lignes insérées par le tirage :

Remarquez que le JSON des réponses ne contient que id et texte : jamais est_correcte.

Bonne pratique - Tout ou rien

Si le tirage ne trouve pas assez de questions, l'exception annule toute la fonction, y compris la création de la partie. Aucune partie « à moitié créée » ne reste dans la base.

Étape 6 — La fonction repondre

repondre enregistre la réponse du joueur et renvoie la correction. Elle commence par chercher la question dans une partie en cours du joueur connecté et verrouille la ligne. Complétez la ligne de verrouillage :

Prenez le temps de relire la fonction, ligne par ligne, et de repérer les quatre vérifications qu'elle fait : la question appartient à la partie, la partie appartient au joueur connecté, la partie est en cours, la réponse appartient bien à cette question. Si le joueur a déjà répondu, la première réponse est conservée.

Bonne pratique - Ne faire confiance à aucun identifiant reçu

L'application envoie trois identifiants. Rien ne garantit qu'ils sont cohérents : un tricheur pourrait envoyer l'identifiant d'une réponse d'une autre question, ou d'une partie qui n'est pas la sienne. La fonction vérifie chaque lien dans la base.

Étape 7 — Limiter qui peut exécuter les fonctions

À la fin du script, retirez le droit d'exécution aux visiteurs puis accordez-le aux joueurs connectés. Complétez la ligne qui retire le droit sur repondre :

Bonne pratique - Refuser avant d'exécuter

Retirer le droit d'exécuter est une protection supplémentaire : un visiteur sans session est refusé avant même que la fonction démarre. Les vérifications internes (auth.uid()) restent indispensables : on ne se repose jamais sur une seule couche.

Étape 8 — Exécuter et tester dans le tableau de bord

Copiez tout 06_partie.sql dans Supabase > SQL Editor puis Run. Testez ensuite deux situations.

Test 1 : dans le SQL Editor, appelez la fonction. Vous y agissez en administrateur et sans session de joueur, donc auth.uid() est null :

select public.demarrer_partie('reseaux', 1, 3);

Résultat attendu : l'erreur « Utilisateur non connecté ». La fonction a bien refusé.

Test 2 : en tant que visiteur (clé publishable seule), depuis un terminal :

curl -X POST "https://VOTRE-PROJET.supabase.co/rest/v1/rpc/demarrer_partie" \
  -H "apikey: VOTRE_CLE_PUBLISHABLE" \
  -H "Content-Type: application/json" \
  -d '{"p_theme_code": null, "p_niveau": null, "p_nb_questions": 3}'

Résultat attendu : un message de refus « permission denied for function demarrer_partie » (code 42501). Le visiteur n'a pas le droit d'exécuter la fonction.

Bonne pratique - Tester ce qui doit échouer

Un test de sécurité consiste souvent à vérifier qu'une action est refusée. Un système qui accepte tout fonctionne parfaitement... mais n'est pas sécurisé.

Étape 9 — Enregistrer votre travail

Ajoutez la ligne suivante au tableau de sql/README.md :

| `06_partie.sql` | tables `partie`, `partie_question` et fonctions `demarrer_partie`, `repondre` |

puis :

git add -A
git status
git commit -m "Fonctions SQL (RPC) pour jouer côté serveur : demarrer_partie et repondre"
git push

Bonne pratique - Versionner aussi le SQL

Un script SQL est du code : il se versionne comme le reste. Le dossier sql/ permet de recréer la base de zéro, dans l'ordre, et de comprendre pourquoi elle est faite ainsi.

📌 Une solution

Vous devez être connecté pour voir le contenu.

Email : Mot de passe :

---

Test de mémorisation/compréhension

Que signifie « security definer » pour une fonction SQL ?

Elle est chiffrée et illisible pour les autres développeurs

Elle vérifie automatiquement l'identité de l'appelant avant de s'exécuter

Elle s'exécute uniquement sur le serveur de sauvegarde de la base

Elle refuse tous les appels qui ne viennent pas d'un joueur connecté

Elle s'exécute avec les droits de son créateur et non avec ceux de l'appelant

Elle est chiffrée et illisible pour les autres développeurs, et elle ne peut être appelée que par l'administrateur

Pourquoi écrit-on « set search_path = '' » dans une fonction security definer ?

Pour que la fonction s'exécute plus rapidement

Pour empêcher qu'un attaquant détourne un nom de table vers un objet qu'il a créé

Pour autoriser la fonction à lire toutes les tables du projet sans écrire le schéma

Pour que la fonction soit visible depuis l'API Supabase

Pour désactiver la RLS pendant l'exécution de la fonction

D'où la fonction demarrer_partie tire-t-elle l'identifiant du joueur ?

D'un paramètre p_joueur_id envoyé par l'application

De la table joueur, en prenant le premier pseudo trouvé

De la clé publishable envoyée dans l'en-tête apikey

De auth.uid(), jamais d'un paramètre envoyé par l'application

Du nom du téléphone du joueur, transmis dans l'URL de la requête

Que fait l'instruction « raise exception » dans une fonction plpgsql ?

Elle arrête la fonction, annule tout ce qu'elle avait fait et renvoie un message d'erreur

Elle affiche un message dans la console de l'application et continue

Elle enregistre l'erreur dans une table, puis valide quand même la transaction

Elle supprime la fonction de la base de données

Elle redémarre le serveur Supabase après une panne

Elle affiche un message dans la console de l'application, puis elle continue normalement jusqu'à la fin de la fonction

À quoi sert « for update » dans la fonction repondre ?

À modifier automatiquement la réponse du joueur

À autoriser le joueur à répondre plusieurs fois à la même question

À verrouiller la ligne : deux appels simultanés pour la même question sont traités l'un après l'autre

À accélérer la requête grâce à un index

À copier la ligne dans une table d'historique avant de la changer

À modifier automatiquement la réponse du joueur et à la copier dans une table d'historique avant de la changer

Pourquoi écrit-on « revoke execute ... from public, anon » après avoir créé une fonction ?

Parce que la fonction serait sinon supprimée au bout d'une heure

Parce que le rôle authenticated n'a jamais le droit d'exécuter une fonction

Parce que supabase-js interdit d'appeler une fonction qui a des droits d'exécution

Pour que la fonction soit exécutée deux fois, par sécurité

Parce que PostgreSQL donne par défaut le droit d'exécuter une fonction à tout le monde

Parce que le rôle authenticated n'a jamais le droit d'exécuter une fonction, quelle que soit la configuration choisie

Pourquoi la fonction demarrer_partie limite-t-elle à 10 parties par minute ?

Parce qu'un joueur humain ne peut pas lire plus de dix questions

Pour freiner les abus (scripts qui créent des milliers de parties)

Parce que PostgreSQL ne sait pas créer plus de dix lignes à la fois

Pour faire payer les joueurs au-delà de ce quota gratuit mensuel

Pour tester la connexion de l'utilisateur toutes les minutes

---

Exercices complémentaires

Exercice A : relire la fonction comme un attaquant

Pour chaque ligne de repondre, imaginez ce qu'enverrait un attaquant : un p_partie_id qui n'est pas le sien, un p_reponse_id d'une autre question, un p_question_id inexistant. Retrouvez dans le code la ligne qui l'arrête, et le message d'erreur correspondant.

Exercice B : tester en tant que joueur connecté (facultatif)

Dans le SQL Editor, vous pouvez simuler un joueur connecté pour un script. Remplacez VOTRE-UUID par votre identifiant (Authentication > Users, colonne UID) :

set local role authenticated;
select set_config('request.jwt.claims', '{"sub": "VOTRE-UUID", "role": "authenticated"}', true);
select public.demarrer_partie('reseaux', 1, 3);

La première ligne emprunte le rôle authenticated, la deuxième place dans la session un jeton fictif pour que auth.uid() renvoie votre uuid. set local et le dernier paramètre true limitent l'effet à ce script. Vous obtenez une partie réelle de 3 questions, dont les réponses ne contiennent que id et texte. Cette partie restera « en cours » dans votre table partie.

---

Ce qu'il faut retenir

Notion	Résumé
RPC	Une fonction SQL du serveur appelée par supabase.rpc("nom", { p_... }).
security definer	La fonction s'exécute avec les droits de son créateur : elle doit tout vérifier elle-même.
set search_path = ''	Oblige à écrire public.table et évite le détournement de noms.
auth.uid()	Identité du joueur, lue dans le jeton, jamais dans un paramètre.
raise exception	Arrête la fonction, annule tout, renvoie un message (code P0001).
for update	Verrouille la ligne : deux appels simultanés sont traités l'un après l'autre.
Droits d'exécution	revoke execute ... from public, anon puis grant execute ... to authenticated.

---

Aperçu de la prochaine séance

Les fonctions sont prêtes, mais l'application ne les utilise pas encore, et la colonne est_correcte est toujours lisible par tous. Dans la prochaine séance, vous allez réécrire le jeu pour qu'il joue contre le serveur : chaque réponse sera envoyée à repondre, la partie deviendra une machine à états avec une étape de vérification, et vous fermerez enfin la faille en retirant le droit de lire est_correcte.