Erreur Supabase "violates foreign key constraint" à l'insertion

Vous cliquez sur Enregistrer pour un nouveau profil dans votre application, et rien ne change. La page n'affiche aucun message. La console du navigateur en a un :

{
  "code": "23503",
  "details": "Key is not present in table \"users\".",
  "hint": null,
  "message": "insert or update on table \"profiles\" violates foreign key constraint \"profiles_id_fkey\""
}

Supabase "violates foreign key constraint" signifie que la ligne que vous vouliez enregistrer désigne une ligne d'une autre table, et que cette ligne n'existe pas. Ici, le profil portait un id censé appartenir à quelqu'un qui s'est inscrit, et Postgres n'a trouvé personne avec cet id. Rien n'a été enregistré.

Le moyen le plus rapide de faire disparaître le message est de supprimer la règle. Les enregistrements passent ensuite, et les lignes qu'ils créent pointent vers le vide : une ligne de commande sans commande, un profil sans personne derrière. Cherchez d'abord quelle ligne manque, et pourquoi. Elle a disparu de l'une de quelques façons.

Que signifie "violates foreign key constraint" dans Supabase ?

Une colonne de votre ligne doit correspondre à une ligne d'une autre table, la valeur envoyée ne correspond à aucune, et Postgres a refusé la ligne entière. La documentation de Postgres sur les contraintes définit la règle en une phrase : une clé étrangère "specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table."

Imaginez l'autre table comme une rue, et chacune de ses lignes comme une maison avec un numéro. Votre nouvelle ligne est une lettre, et la colonne qui porte la clé étrangère est le numéro de maison écrit dessus. Postgres est le centre de tri. Avant d'accepter la lettre, il cherche le numéro dans la rue, et une lettre pour une maison qui n'existe pas revient aussitôt. Le message dit quelle lettre est revenue. La ligne de détail dit dans quelle rue on a cherché.

Le libellé vient du fichier de Postgres qui exécute ces vérifications. Un update la déclenche aussi, quand il remplace la colonne par un numéro sans maison, et c'est pourquoi le message commence par "insert or update".

Un message qui commence par update or delete on table est la même règle vue depuis la rue : vous avez voulu démolir une maison vers laquelle des lettres pointent encore. Celui-là vient d'une suppression, et l'article sur la suppression d'utilisateurs le traite.

Quelle ligne Postgres cherche-t-il ?

Celle de la ligne de détail, dans la table que cette ligne nomme. Deux choses la rendent plus difficile à lire qu'il n'y paraît.

La valeur est parfois omise. Postgres n'affiche Key (id)=(…) que si l'appelant a le droit de lire cette colonne de votre table. Quand Row Level Security est activée sur la table où vous avez écrit, son code omet la valeur, avec le commentaire "we don't return any specifics to avoid leaking data", et vous obtenez Key is not present in table "users". C'est ce que voit une requête du navigateur vers une table protégée. La valeur est celle que votre application a envoyée : ouvrez les outils de développement de votre navigateur, trouvez la requête vers /rest/v1/profiles dans l'onglet Réseau, et lisez-la dans la charge utile.

Si votre console affiche la valeur pour une requête faite par votre application avec la clé publique, Row Level Security est désactivée sur la table où vous avez écrit. Une table dont les règles sont désactivées donne ses lignes à un inconnu aussi volontiers qu'à vous, et activer les règles sur chaque table montre l'interrupteur et la première règle. Notre scan gratuit lit votre application en ligne avec la clé publique que reçoit tout visiteur et liste les tables qui lui répondent. Il ne demande aucun compte.

Le nom de la table n'a pas de schéma. Postgres affiche users, et dans un projet Supabase, une colonne qui pointe vers des personnes pointe presque toujours vers auth.users, la table où Supabase Auth tient la liste de toutes les personnes inscrites. Elle ne figure pas parmi vos tables publiques dans le Table Editor, et c'est pourquoi le message semble nommer une table que vous n'avez jamais créée.

Pour voir exactement où pointe la règle, exécutez ceci dans le SQL Editor avec le nom de la règle tiré du message :

select conrelid::regclass as on_table,
       pg_get_constraintdef(oid) as rule
from pg_constraint
where conname = 'profiles_id_fkey';

La réponse donne la table qui porte la règle et ce que dit la règle, par exemple FOREIGN KEY (id) REFERENCES auth.users(id) ON DELETE CASCADE : votre colonne, puis la table et la colonne auxquelles elle doit correspondre. Cherchez ensuite la valeur :

select id, email, created_at from auth.users where id = '…';

Si aucune ligne ne revient, la personne que nomme votre profil n'existe pas dans ce projet.

D'où vient une ligne qui pointe vers le vide ?

De quelques endroits seulement, et la table de la ligne de détail vous dit où chercher d'abord.

La ligne visée n'a jamais été enregistrée, ou l'a été en second. Une commande et ses lignes font deux enregistrements. Si votre application enregistre d'abord les lignes, ou enregistre la commande, reçoit une erreur qu'elle ne lit jamais et continue avec les lignes, chaque ligne désigne une commande qui n'existe pas. Enregistrez d'abord la ligne visée, prenez son id dans la réponse, et lisez l'erreur de chaque étape avant la suivante :

const { data: order, error } = await supabase
  .from('orders')
  .insert({ user_id: user.id })
  .select('id')
  .single()

if (error) throw error

const { error: linesError } = await supabase
  .from('order_items')
  .insert(cart.map((item) => ({ ...item, order_id: order.id })))

.select() après une insertion relit la nouvelle ligne, donc la personne a besoin d'une règle qui l'autorise à lire ses propres commandes. La documentation de Postgres sur les policies indique qu'une nouvelle ligne qui échoue aux règles de lecture fait échouer l'enregistrement avec une erreur, et dans Supabase cette erreur s'appelle new row violates row-level security policy. Les deux enregistrements sont des requêtes séparées : si les lignes échouent, la commande est déjà enregistrée. Renvoyez les lignes, ou supprimez la commande.

L'id venait d'ailleurs. Chaque projet Supabase a son propre auth.users. Un id copié depuis votre projet de test, un id qu'un outil de création par IA a laissé dans le code depuis un aperçu, ou des lignes copiées d'un projet à un autre désignent tous des personnes que le projet en ligne n'a jamais vues. Quand vous copiez des données entre projets, copiez les lignes visées avant les lignes qui pointent vers elles. Les personnes derrière une colonne qui pointe vers auth.users ne se copient pas ainsi : elles s'inscrivent dans le nouveau projet et reçoivent de nouveaux ids en le faisant. Un profil dont l'id doit être celui de la personne casse aussi quand l'application le remplit avec une valeur aléatoire neuve, comme crypto.randomUUID(). Ce numéro n'appartient à aucune maison. Utilisez user.id de la session connectée.

La ligne a été supprimée entre-temps. Un produit retiré de votre boutique pendant qu'il était dans le panier de quelqu'un, ou un compte supprimé dans un autre onglet. L'enregistrement qui suit pointe vers une maison qui existait quand la page a été chargée. La correction est dans votre application : testez error.code === '23503' et dites à la personne ce qui a changé, par exemple "Ce produit n'est plus disponible."

Un trigger qui s'exécute trop tôt. Un trigger sur auth.users qui copie chaque nouvelle personne dans profiles doit s'exécuter après l'enregistrement de la personne. La documentation de Postgres sur les triggers dit que le changement qui déclenche un trigger BEFORE par ligne "is naturally not visible to SQL commands executed in a row-level BEFORE trigger, because it hasn't happened yet". Un profil écrit depuis un trigger before insert pointe donc vers une personne qui n'existe pas encore. Chaque inscription échoue, et Supabase la signale comme "Database error saving new user". Le modèle de Supabase lui-même, que cet article reproduit en entier, utilise after insert.

Pourquoi une valeur vide passe-t-elle ?

Parce qu'une clé étrangère ne vérifie que les valeurs présentes. La documentation de Postgres sur les contraintes le dit ainsi : "Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null." Null est le mot de Postgres pour l'absence de toute valeur. Une lettre sans numéro de maison n'est jamais vérifiée. Elle part dans le sac et n'appartient à aucune maison.

Une ligne de commande enregistrée avec un order_id vide est donc acceptée, et n'appartient à aucune commande. Votre application la retrouve plus tard, sous la forme d'une ligne qui n'apparaît dans le panier de personne. La même page donne la correction : "declare the referencing column(s) as NOT NULL".

alter table public.order_items alter column order_id set not null;

Ensuite, une valeur vide est refusée aussi, avec un message qui nomme directement la colonne, null value in column violates not-null constraint. L'instruction échoue tant que des lignes avec un order_id vide existent déjà, alors listez-les d'abord avec select * from public.order_items where order_id is null.

Une règle peut-elle cacher la ligne recherchée ?

Non. Postgres vérifie une clé étrangère contre chaque ligne de l'autre table, quoi que disent vos règles de Row Level Security. Sa documentation sur la sécurité des lignes le dit sans détour : "Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security to ensure that data integrity is maintained." Dans le code qui exécute la vérification, la recherche passe au propriétaire de l'autre table, avec un drapeau qui empêche même une règle forcée de s'appliquer.

Le centre de tri travaille donc avec le plan complet de la rue. Votre application voit la rue à travers ses règles, qui ne lui montrent peut-être que les maisons qu'elle a le droit de visiter, et la vérification n'utilise jamais cette vue. Un 23503 veut dire que la maison n'existe pour personne. Si la ligne existe et que votre application ne peut pas la voir, l'enregistrement passe.

Quelqu'un peut-il rattacher une ligne à l'enregistrement d'un autre ?

Oui, sauf si votre règle d'insertion vérifie à qui appartient cet enregistrement. La vérification de l'existence de la maison ignore vos règles, donc elle dit oui à une maison qui appartient à quelqu'un d'autre.

Prenez order_items avec cette règle d'insertion, qui a l'air sûre :

create policy "Add your own order lines"
on public.order_items for insert
to authenticated
with check (auth.uid() = user_id);

Elle vérifie que la ligne est à vous, et ne regarde jamais à quelle commande elle s'ajoute. Une personne connectée qui envoie son propre user_id avec l'order_id de quelqu'un d'autre passe la règle et passe la clé étrangère, et une ligne qu'elle a écrite figure maintenant sur la commande d'un inconnu. La même forme place un commentaire sous la publication privée d'une autre personne, ou un membre dans une équipe où personne ne l'a invité.

Remplacez-la par une règle qui regarde la commande :

create policy "Add lines to your own orders"
on public.order_items for insert
to authenticated
with check (
  exists (
    select 1 from public.orders
    where orders.id = order_items.order_id
      and orders.user_id = auth.uid()
  )
);

Supprimez la première règle en ajoutant celle-ci. Postgres combine les règles du même type avec "OR", selon les termes de sa documentation sur les policies, donc l'ancienne laisserait encore passer la ligne à elle seule.

La vérification répond aussi à une question pour quiconque a le droit d'insérer. La phrase qui suit celle citée plus haut, dans la documentation sur la sécurité des lignes, met en garde : "Care must be taken when developing schemas and row level policies to avoid 'covert channel' leaks of information through such referential integrity checks." Un enregistrement qui échoue avec 23503 dit qu'une maison n'existe pas. Un enregistrement qui réussit dit qu'elle existe, dans une rue que la personne ne peut pas lire. Les ids d'utilisateur de Supabase sont de longues valeurs aléatoires que personne ne devine. Quand une colonne pointe vers quelque chose de devinable, comme une adresse e-mail ou un id qui compte à partir de 1, une personne peut essayer des valeurs une par une et apprendre lesquelles existent.

Faut-il supprimer la clé étrangère ?

Non. Sans elle, l'enregistrement passe, la ligne pointe vers le vide, et à partir de là plus rien ne vous dit quand cela arrive. Un panier affiche une ligne pour un produit retiré le mois dernier, et un profil reste là pour une personne qui n'est plus dans auth.users.

La règle décide aussi de ce qui arrive à ces lignes quand la ligne qu'elles visent est supprimée. Avec on delete cascade sur la règle qui va de profiles à auth.users, supprimer une personne supprime son profil. Sans la règle, le profil reste, avec toutes les informations personnelles qu'il contient. L'article sur la suppression d'utilisateurs explique comment choisir ce que garde chaque table.

Si d'anciennes lignes pointent déjà vers le vide, listez-les avant de remettre une règle ou d'en durcir une, car Postgres refuse d'ajouter une clé étrangère tant qu'une ligne la viole :

select i.*
from public.order_items i
left join public.orders o on o.id = i.order_id
where i.order_id is not null
  and o.id is null;

Décidez pour chacune si elle retrouve sa commande ou si elle s'en va. Les supprimer ne se rattrape pas sans une copie de la base de données d'avant.

Avant de réparer les lignes

Gardez une copie de la base de données d'avant le moment où vous supprimez des lignes qui pointent vers le vide, copiez des données entre projets ou modifiez une règle, et soyez prévenu quand une table se met à répondre à des inconnus.

Reeve Care garde une copie de votre base de données Supabase :

• une copie chiffrée selon un calendrier, sur le stockage propre de Reeve dans l'UE, en dehors de votre compte Supabase
• chaque ligne telle qu'elle était avant qu'un nettoyage supprime les mauvaises
• chaque copie vérifiée avant de compter, en comptant les lignes de chaque table
• vos fichiers envoyés aussi, dès que vous connectez un identifiant Storage
• une restauration en un clic, avec une copie de sécurité de l'état actuel prise d'abord
• tout ce que fait Monitor

Reeve Monitor continue de vérifier de l'extérieur :

• si votre application répond, vérifié chaque minute
• la vérification de sécurité complète toutes les heures, sur trois applications au plus
• un e-mail le jour où un nouveau scan note votre application moins bien que le précédent, pour qu'une table dont les règles ont été désactivées vous parvienne sans que vous alliez voir
• un rapport mensuel, en langage clair, de ce qu'il a vu

Que faire maintenant

Commencez par la table de la ligne de détail et l'id que votre application a envoyé. Si la correction supprime des lignes ou les copie entre projets, gardez une copie de la base de données d'avant de l'exécuter.