Aller au contenu principal

Table profils / rôles

Création de la table de relation entre utilisateurs et rôles

Notions théoriques​

Le type ENUM pour les rôles​

Pourquoi utiliser un type ENUM pour les rôles ?

Dans une base de données relationnelle comme PostgreSQL (et donc Supabase), il est souvent nécessaire de limiter les valeurs possibles d’un champ à un ensemble précis.

C’est exactement ce que permet un type ENUM : un type personnalisé qui restreint les valeurs à une liste définie.

Dans notre projet, les utilisateurs peuvent avoir différents rôles :

  • guest
  • student
  • teacher
  • admin
  • super-admin

Ces rôles ont des droits différents dans l’application. Il est donc important de les gérer de manière fiable et sécurisée.

Avantages d’un type ENUM​

  • Validation automatique : PostgreSQL empêche l’insertion d’une valeur non prévue.
  • Lisibilité : les valeurs sont explicites et claires ('admin', 'student', etc.).
  • Sécurité : impossible d’ajouter un rôle non prévu par erreur.
  • Compatibilité avec Supabase : les types ENUM sont bien pris en charge dans l’interface.
astuce

Toujours préférer un type ENUM à une simple colonne texte quand les valeurs possibles sont connues et limitées.

Définition d’un type ENUM​

Voici comment créer un type ENUM dans PostgreSQL :

create type user_role as enum ('guest', 'student', 'teacher', 'admin', 'super-admin');

Cette commande crée un nouveau type user_role avec cinq valeurs autorisées.

Créer la table user_roles​

Créer une table pour stocker les rôles des utilisateurs

Une fois le type ENUM créé, on peut l’utiliser dans une table.
La table user_roles permet de lier un utilisateur à un rôle donné.

Structure de la table :

ColonneTypeDescription
iduuidIdentifiant unique (clé primaire)
user_iduuidRéférence à auth.users(id)
roleuser_roleRôle de l’utilisateur
created_attimestamp with time zoneDate de création de la ligne
updated_attimestamp with time zoneDate de dernière mise à jour

Script SQL pour créer la table​

create table public.user_roles (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references auth.users(id) not null,
  role user_role not null default 'guest',
  created_at timestamp with time zone default current_timestamp,
  updated_at timestamp with time zone default current_timestamp
);
info

Le champ role utilise le type user_role défini précédemment.

La valeur par défaut est 'guest'.

Bonnes pratiques cyber​

  • Limiter les rôles possibles via un ENUM empêche les erreurs ou attaques par injection de rôles non autorisés.
  • Auditer les changements de rôle : les champs created_at et updated_at permettent de tracer les modifications.
  • Éviter les valeurs libres : ne jamais autoriser un champ texte libre pour des rôles critiques.
attention

Ne jamais stocker les rôles dans une chaîne de caractères libre (text) sans validation stricte.

Exemple pratique​

Créer la table des rôles​

Créer le type ENUM et la table des rôles

Il est possible de créer le type ENUM et la table via le SQL Editor de Supabase.

  1. Ouvrir l’onglet SQL Editor.
  2. Exécuter la requête suivante pour créer le type ENUM :
create type user_role as enum ('guest', 'student', 'teacher', 'admin', 'super-admin');
  1. Créer ensuite la table user_roles :
create table public.user_roles (
  id uuid default uuid_generate_v4() primary key,
  user_id uuid references auth.users(id) not null,
  role user_role not null default 'guest',
  created_at timestamp with time zone default current_timestamp,
  updated_at timestamp with time zone default current_timestamp
);

Insérer un rôle pour un utilisateur​

Il est possible d’attribuer un rôle à un utilisateur existant :

insert into public.user_roles (user_id, role)
values ('<uuid_utilisateur>', 'admin');
remarque

Remplacer <uuid_utilisateur> par un identifiant réel d’utilisateur existant dans auth.users.

Vérifier le contenu de la table​

Aller dans Table Editor > user_roles et vérifier que :

  • la ligne a bien été insérée,
  • le champ role est bien rempli,
  • les dates created_at et updated_at sont présentes.

Tester la sécurité du type ENUM​

Tenter d’insérer une valeur non autorisée :

insert into public.user_roles (user_id, role)
values ('<uuid_utilisateur>', 'hacker');

Résultat attendu : une erreur indiquant que 'hacker' n’est pas une valeur valide pour user_role.

Test de mémorisation/compréhension​


Quel est l'intérêt principal d'utiliser un type ENUM pour les rôles ?


Quel type de données est utilisé pour le champ 'role' dans la table user_roles ?


Quelle commande permet de créer un type ENUM dans PostgreSQL ?


Quelle est la valeur par défaut du champ 'role' dans la table user_roles ?


Que se passe-t-il si on insère une valeur non autorisée dans un champ ENUM ?


À quoi sert le champ updated_at dans la table user_roles ?


Quel est le type de données du champ id dans la table user_roles ?


Pourquoi faut-il auditer les changements de rôles ?


Quel champ dans user_roles référence un utilisateur ?


Quelle est la bonne pratique pour gérer les rôles dans une application sécurisée ?


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

1. Créer le type ENUM user_role​

Dans l’éditeur SQL de Supabase, créer un type ENUM contenant les rôles suivants : 'guest', 'student', 'teacher', 'admin', 'super-admin'.

create type user_role as enum ('guest', 'student', 'teacher', 'admin', 'super-admin');
📌 Une solution

2. Créer la table user_roles​

Créer la table user_roles avec une référence à auth.users

Créer une table user_roles avec les colonnes suivantes :

  • id de type uuid, clé primaire, généré automatiquement
  • user_id de type uuid, référence à auth.users(id)
  • role de type user_role, avec la valeur par défaut 'guest'
  • created_at et updated_at de type timestamp with time zone, avec valeur par défaut à current_timestamp
📌 Une solution

3. Insérer un rôle​

Insérer un rôle pour un utilisateur existant

Insérer une ligne dans la table user_roles pour attribuer à un utilisateur existant le rôle 'admin'.

⚠️ Remplacer <uuid_utilisateur> par un identifiant réel d’un utilisateur de votre base Supabase.

📌 Une solution

4. Vérifier que l’insertion a bien fonctionné​

Vérifier que la ligne a bien été insérée dans la table user_roles :

  • Accéder à l’onglet Table Editor > user_roles
  • Vérifier que les colonnes user_id, role, created_at, updated_at sont correctement remplies
📌 Une solution

5. Tester la sécurité du type ENUM​

Tenter d’insérer une valeur non autorisée dans le champ role, comme 'hacker'.
Observer le comportement de PostgreSQL.

📌 Une solution

6. Modifier le rôle d’un utilisateur existant​

Mettre à jour le rôle d’un utilisateur en le passant de 'admin' à 'super-admin'.

📌 Une solution

7. Un seul rôle par utilisateur​

Ajouter une contrainte d’unicité sur user_id

Empêcher qu’un même utilisateur ait plusieurs rôles en ajoutant une contrainte UNIQUE(user_id).

📌 Une solution

8. Vérifier les contraintes de sécurité​

Essayer d’insérer un second rôle pour le même utilisateur.
Observer que la base de données empêche l’insertion.

📌 Une solution

9. Lister tous les utilisateurs avec leur rôle​

Créer une requête SQL qui affiche tous les utilisateurs et leur rôle (en joignant auth.users et user_roles).

📌 Une solution

10. Retirer le rôle d’un utilisateur​

Pour tester, supprimer un rôle attribué à un utilisateur.

📌 Une solution