Aller au contenu principal

Fonction d'insertion de profil

Automatiser la création d’un profil utilisateur lors de l’inscription

Notions théoriques​

Qu’est-ce qu’une fonction stockée ?​

Une fonction stockée (ou procédure stockée) est un morceau de code SQL qui est enregistré dans la base de données et qui peut être exécuté automatiquement ou manuellement.

info

Dans PostgreSQL, ces fonctions peuvent être écrites en PL/pgSQL (le langage procédural de PostgreSQL).

Les fonctions stockées permettent d’automatiser des actions répétitives ou critiques, comme :

  • insérer des données liées dans plusieurs tables,
  • effectuer des vérifications complexes,
  • mettre à jour des champs automatiquement.

Qu’est-ce qu’un trigger ?​

Un trigger (déclencheur) est un mécanisme qui exécute automatiquement une fonction lorsqu’un événement spécifique se produit sur une table :

  • AFTER INSERT → après une insertion,
  • BEFORE UPDATE → avant une mise à jour,
  • AFTER DELETE → après une suppression, etc.
info

Un trigger est toujours lié à une table et à un événement (INSERT, UPDATE, DELETE).

Pourquoi utiliser un trigger ?​

Pourquoi utiliser un trigger pour les profils utilisateurs ?

Dans Supabase, la table auth.users est gérée automatiquement par le système d’authentification.
Mais pour stocker des informations supplémentaires sur chaque utilisateur (comme un username ou un avatar_url), il faut utiliser une table personnalisée : profiles.

Plutôt que d’ajouter manuellement une ligne dans profiles à chaque inscription, il est possible d’automatiser cette étape avec un trigger.

Fonctionnement attendu​

  1. Un utilisateur s’inscrit via Supabase Auth.
  2. Une ligne est automatiquement ajoutée dans auth.users.
  3. Le trigger détecte l’insertion.
  4. Le trigger appelle une fonction stockée.
  5. Cette fonction insère automatiquement une ligne dans profiles.

Structure attendue de la table profiles​

create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique,
  avatar_url text,
  created_at timestamp with time zone default timezone('utc', now()) not null,
  constraint username_length check (char_length(username) >= 3)
);
attention

Le champ id doit être le même que celui de auth.users. Il est utilisé comme clé primaire et clé étrangère.

Exemple de fonction PL/pgSQL​

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
remarque

Cette fonction insère une ligne dans profiles avec le même id que celui du nouvel utilisateur.

Création du trigger associé​

create trigger on_auth_user_created
after insert on auth.users
for each row
execute procedure public.handle_new_user();

Ce trigger s’exécute après chaque insertion dans auth.users, et appelle la fonction handle_new_user().

Bonnes pratiques cyber​

  • Tester les triggers dans un environnement isolé avant de les utiliser en production.
  • Limiter les privilèges d’exécution aux rôles autorisés.
  • Vérifier les effets de bord : un trigger mal conçu peut provoquer des boucles infinies ou des erreurs silencieuses.
astuce

Utiliser SECURITY DEFINER dans la fonction pour exécuter la fonction avec les droits de son créateur.

Exemple pratique​

Créer une fonction qui insère automatiquement un profil​

Il est possible de créer une fonction stockée simple pour insérer automatiquement une ligne dans la table profiles à chaque nouvelle inscription.

  1. Créer la table profiles si elle n’existe pas :
create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique,
  avatar_url text,
  created_at timestamp with time zone default timezone('utc', now()) not null,
  constraint username_length check (char_length(username) >= 3)
);
  1. Créer la fonction :
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
  1. Créer le trigger :
create trigger on_auth_user_created
after insert on auth.users
for each row
execute procedure public.handle_new_user();
  1. Tester le fonctionnement :
  • Aller dans l’onglet Auth de Supabase.
  • Créer un nouvel utilisateur via l’interface.
  • Vérifier que la table profiles contient automatiquement une nouvelle ligne avec le bon id.

Test de mémorisation/compréhension​


Quel est le rôle d’un trigger dans PostgreSQL ?


Quel événement doit-on surveiller pour ajouter un profil après inscription ?


Quel champ est utilisé comme clé primaire dans la table profiles ?


Que fait la fonction handle_new_user() ?


Quel langage est utilisé pour écrire une fonction stockée ?


Que signifie SECURITY DEFINER dans une fonction ?


Quel est le nom de la table surveillée par le trigger ?


Quand le trigger on_auth_user_created est-il exécuté ?


Que contient la variable new.id dans un trigger ?


Pourquoi faut-il tester les triggers dans un environnement isolé ?


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

Objectif du TP​

Créer une procédure stockée et un trigger pour insérer automatiquement un profil utilisateur dans la table profiles à chaque inscription via Supabase Auth.

Étape 1 : Vérifier que la table profiles existe avec la bonne structure​

Vérifier dans l’interface SQL de Supabase si la table profiles est déjà présente avec les colonnes suivantes : id, username, avatar_url, created_at.

Si elle n’existe pas, l’ajouter avec la commande suivante :

create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  username text unique,
  avatar_url text,
  created_at timestamp with time zone default timezone('utc', now()) not null,
  constraint username_length check (char_length(username) >= 3)
);
Une solution

Étape 2 : Écrire la fonction stockée en PL/pgSQL​

Créer une fonction nommée handle_new_user dans le schéma public. Cette fonction sera appelée par un trigger et insèrera automatiquement une ligne dans la table profiles avec l’id de l’utilisateur nouvellement inscrit.

create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$ language plpgsql security definer;
Une solution

Étape 3 : Créer le trigger lié à la table auth.users​

Associer la fonction à un trigger qui s’exécutera automatiquement après chaque insertion dans auth.users.

create trigger on_auth_user_created
after insert on auth.users
for each row
execute procedure public.handle_new_user();
Une solution

Étape 4 : Tester l’automatisation via l’inscription d’un utilisateur​

Aller dans l’onglet Auth du projet Supabase. Ajouter un utilisateur manuellement avec un email et un mot de passe.

Une fois l’utilisateur ajouté, ouvrir l’éditeur SQL et exécuter la requête suivante :

select * from public.profiles order by created_at desc;

Vérifier que le nouvel utilisateur a bien une ligne correspondante dans profiles.

Une solution

Étape 5 : Sécuriser la fonction et le trigger​

Vérifier que la fonction utilise bien security definer.
Restreindre l’accès à la fonction si nécessaire, en limitant son exécution à un rôle spécifique (par exemple, un rôle système ou un rôle d’admin).

Optionnel : tester le comportement de la procédure dans un projet de test ou dans un environnement isolé.

Une solution

Étape 6 : Supprimer et recréer un utilisateur pour tester le trigger à nouveau​

  1. Supprimer un utilisateur via l’interface Auth.
  2. Réinscrire un utilisateur avec un nouvel email.
  3. Vérifier que la table profiles a bien une nouvelle ligne.
Une solution

Étape 7 : Documenter dans un fichier SQL toutes les étapes précédentes​

Créer un fichier trigger_profiles.sql contenant toutes les requêtes nécessaires pour :

  • créer la table profiles (si besoin),
  • créer la fonction handle_new_user,
  • créer le trigger on_auth_user_created.
Une solution

Étape 8 : Vérifier l’état des tables via DBeaver​

  1. Se connecter à la base distante avec DBeaver.
  2. Rechercher les tables auth.users et public.profiles.
  3. Vérifier que les données sont bien synchronisées.
  4. Confirmer que le trigger fonctionne même depuis une interface externe.
Une solution