RLS Postgres : `INSERT … RETURNING` refusé en 403

En RLS Postgres, un INSERT … RETURNING peut être refusé en 403 alors que la policy d’INSERT est juste : le RETURNING soumet aussi la nouvelle ligne aux policies SELECT. Sur une app de signalements (Supabase self-hosted, PostgreSQL 15, PostgREST), un gestionnaire fraîchement inscrit ne pouvait pas créer sa première résidence. Il a fallu deux correctifs : le premier a réparé la policy d’INSERT, le second la policy SELECT déclenchée par le RETURNING. Les deux échouaient pour la même raison : un helper qui cherchait par son id une ligne pas encore visible.

Le symptôme : POST /rest/v1/residences 403

La création échouait côté client. Les logs de Kong, la passerelle API de Supabase, montraient la requête :

POST /rest/v1/residences 403

Le code client est banal, un insert suivi d’un select pour récupérer l’identifiant et le code de rattachement :

const { data, error } = await supabase
  .from("residences")
  .insert({ manager_id: input.managerId, name: input.name, address: input.address })
  .select("id, join_code")
  .single();

L’utilisateur était bien authentifié, et il avait bien sa ligne d’administrateur dans manager_members.

Correctif n° 1 : une policy d’INSERT qui cherche une ligne inexistante

La policy d’origine couvrait toutes les commandes d’un coup :

create policy residences_modify_member on public.residences
  for all
  using (public.is_manager_member(id))
  with check (public.is_manager_member(id));

Le helper is_manager_member(_residence) est une fonction security definer stable qui joint manager_members et residences sur residences.id = _residence. À l’INSERT, il cherche la résidence qu’on est en train de créer : il ne la trouve pas, renvoie faux, et le with check refuse.

Le correctif sépare les commandes. L’INSERT vérifie l’appartenance via manager_id, une colonne de la nouvelle ligne, avec un autre helper qui ne lit que manager_members :

drop policy if exists residences_modify_member on public.residences;

create policy residences_insert_member on public.residences
  for insert
  with check (public.is_member_of_manager(manager_id));

create policy residences_update_member on public.residences
  for update
  using (public.is_manager_member(id))
  with check (public.is_manager_member(id));

create policy residences_delete_member on public.residences
  for delete
  using (public.is_manager_member(id));

Appliqué en staging : toujours 403.

Pourquoi INSERT … RETURNING déclenche-t-il la policy SELECT ?

.select() après .insert() fait envoyer par supabase-js l’en-tête Prefer: return=representation, et PostgREST exécute un INSERT … RETURNING. Pour rendre la ligne, PostgreSQL exige qu’elle passe aussi les policies SELECT de la table (doc PostgreSQL 15, CREATE POLICY, tableau des policies appliquées par commande). Si elle échoue, c’est toute l’insertion qui est annulée.

La policy SELECT de residences était using (public.is_manager_member(id)) : le même helper, le même raisonnement par id. Et il est STABLE. Une fonction STABLE lit avec l’instantané (snapshot) pris au début de la requête appelante (doc PostgreSQL 15 sur la volatilité) : la ligne insérée par cette même requête n’y figure pas. Le helper ne voit pas la résidence, la policy SELECT refuse, PostgREST répond 403.

Avec RETURNING, une ligne neuve doit passer la policy d’INSERT et la policy SELECT : un helper qui la cherche par son id ne la trouve dans aucune des deux.

Correctif n° 2 : une seconde policy SELECT par la colonne de rattachement

Les policies permissives d’une même commande se combinent en OR. Il suffit d’en ajouter une qui raisonne sur manager_id, présent dans la nouvelle ligne :

create policy residences_select_member_via_manager on public.residences
  for select using (public.is_member_of_manager(manager_id));

Vérification directe en psql : INSERT … RETURNING id, join_code passe. Les SELECT classiques continuent de passer par la policy existante.

Deux mois plus tard, le linter de base de données de Supabase a remonté deux familles d’avertissements de performance : auth_rls_initplan (un auth.uid() nu, évalué à chaque ligne, à envelopper en (select auth.uid())) et multiple_permissive_policies. Les trois policies SELECT de residences ont alors été fusionnées en une seule, sémantique identique :

create policy residences_select on public.residences
  for select using (
    public.is_manager_member(id)
    or public.is_member_of_manager(manager_id)
    or public.is_residence_member(id)
  );

La branche manager_id reste indispensable : c’est elle qui laisse passer le RETURNING.

Pourquoi 87 tests RLS verts n’ont rien vu ?

Au moment du bug, pnpm test:rls affichait 87/87. Aucun test ne jouait l’INSERT d’une résidence depuis un compte fraîchement créé, c’est-à-dire le premier geste d’un nouveau client. Le trou a été couvert au sprint suivant. Un test d’INSERT RLS utile reproduit exactement l’appel du client : même rôle, même RETURNING.

Sur le même projet, deux autres pièges RLS de la même famille : un exists de policy filtré par la RLS d’une autre table, et permission denied for table, qui est un GRANT et pas une policy.

Lessons learned

• INSERT … RETURNING soumet la nouvelle ligne aux policies SELECT. Une policy d’INSERT correcte ne suffit pas dès que le client demande la ligne en retour.
• supabase-js ajoute RETURNING dès qu’on chaîne .select() après .insert(). L’en-tête Prefer: return=representation rend la policy SELECT décisive pour l’écriture.
• Un helper RLS STABLE ne voit pas la ligne en cours d’insertion. Il lit avec l’instantané de la requête appelante, qui ne la contient pas.
• Une policy sur une ligne neuve raisonne sur ses colonnes, pas sur son id. La colonne de rattachement (manager_id) existe déjà au moment de l’insertion.
• Le premier geste d’un compte neuf mérite son propre test RLS. Un compte qui a déjà des données ne passe pas par le même chemin RLS qu’un compte qui crée sa première ligne.