La RLS s’applique-t-elle aux sous-requêtes d’une policy PostgreSQL ?

Oui. Une sous-requête écrite dans une policy, par exemple un exists (select 1 from autre_table …), s’exécute avec les droits de l’utilisateur courant : la RLS de autre_table s’y applique. Si l’utilisateur ne voit pas la ligne cherchée, le exists vaut faux et la policy refuse, sans erreur qui désigne l’autre table.

Comment vérifier une ligne invisible pour l’utilisateur dans une policy RLS ?

Avec une fonction security definer qui pose la question précise et renvoie un booléen, comme can_confirm_incident(report_id). Elle s’exécute avec les droits de son propriétaire, donc sans la RLS de la table lue, mais n’expose aucune colonne. Il faut fixer son search_path et n’accorder EXECUTE qu’au rôle qui en a besoin.

Que signifie l’erreur infinite recursion detected in policy ?

PostgreSQL renvoie 42P17 (infinite recursion detected in policy) quand l’évaluation d’une policy relit la même table, directement ou via une autre table dont la policy revient vers elle. Le correctif classique est le même que pour un exists filtré : déplacer la vérification dans une fonction security definer qui ne déclenche pas la RLS.

Quelle différence entre une vue security_invoker et une vue security_definer ?

Une vue security_invoker s’exécute avec les droits de l’appelant : la RLS des tables sous-jacentes s’applique à lui. Sans cette option, une vue PostgreSQL s’exécute avec les droits de son propriétaire et contourne la RLS de l’appelant ; elle doit alors porter elle-même sa garde dans son WHERE, avec security_barrier pour que le filtrage passe avant les fonctions de l’appelant.

RLS Postgres : le `exists` filtré par une autre policy

En RLS Postgres, un exists dans une policy d’INSERT est lui-même filtré par la RLS de la table qu’il interroge. Sur une app de signalements (Supabase self-hosted, PostgreSQL 15, PostgREST), j’ai resserré la policy de lecture des signalements : un membre ne lit plus que les siens. Effet de bord invisible à la lecture de la migration : la policy d’insertion des confirmations, sur une autre table, vérifiait le signalement visé avec un exists. Après resserrement, le membre ne voit plus ce signalement, le exists devient faux, et toute confirmation est refusée. Le correctif est une fonction security definer qui répond à la question sans exposer la ligne.

Le resserrement : une policy de lecture plus stricte

Le modèle : des membres d’une résidence signalent des incidents dans incident_reports. Un voisin peut confirmer un incident en cours (« je le constate aussi »), ce qui insère une ligne dans incident_confirmations. Jusque-là, la policy de lecture laissait un membre lire la ligne entière des signalements de ses voisins, auteur et description compris, ce qui dépassait le besoin. Le resserrement :

drop policy if exists incident_reports_select on public.incident_reports;
create policy incident_reports_select on public.incident_reports
  for select using (
    author_id = (select auth.uid())
    or public.is_manager_member(residence_id)
  );

L’auteur et le gestionnaire de la résidence lisent la table ; les voisins passeront par une vue qui n’expose ni l’auteur ni la description. Avant de livrer, j’ai inventorié les huit lecteurs de la table dans le code (services, export, Edge Function, vues) : tous lisaient leurs propres lignes, ou celles du gestionnaire, ou passaient en service role. Aucun parcours ne cassait. Sauf un, qui n’était pas un lecteur.

Pourquoi l’`exists` d’une policy d’INSERT devient faux ?

La policy d’insertion des confirmations, écrite une migration plus tôt :

create policy incident_confirmations_insert on public.incident_confirmations
  for insert with check (
    author_id = (select auth.uid())
    and exists (
      select 1 from public.incident_reports ir
      where ir.id = report_id
        and ir.deleted_at is null
        and public.is_residence_member(ir.residence_id)
        and ir.author_id <> (select auth.uid())
    )
  );

Une policy RLS est une expression SQL évaluée avec les droits de l’utilisateur courant. Sa sous-requête sur incident_reports n’y échappe pas : PostgreSQL lui applique la policy de lecture de incident_reports. Avant, le voisin voyait le signalement, le exists était vrai. Après, il ne le voit plus, puisqu’il n’en est pas l’auteur. Le exists est faux pour chaque signalement qu’on peut confirmer, par construction.

Une sous-requête dans une policy hérite de la RLS de la table qu’elle lit : resserrer une lecture peut casser une écriture sur une autre table.

Je ne l’ai pas supposé : j’ai rejoué l’ancienne policy contre la base de test après resserrement, et l’insertion échoue sur new row violates row-level security policy. Les tests existants ne le voyaient pas, et la policy modifiée ne mentionne même pas incident_confirmations.

Le correctif : une fonction security definer qui répond par oui ou non

Une fonction security definer s’exécute avec les droits de son propriétaire, pas de l’appelant : la RLS de incident_reports ne s’y applique pas. Elle reprend les conditions de l’ancien exists et ne renvoie qu’un booléen :

create or replace function public.can_confirm_incident(_report uuid)
returns boolean
language sql
security definer
stable
set search_path = public, pg_temp
as $$
  select exists (
    select 1 from public.incident_reports ir
    where ir.id = _report
      and ir.deleted_at is null
      and ir.author_id <> (select auth.uid())
      and public.is_residence_member(ir.residence_id)
  )
$$;

grant execute on function public.can_confirm_incident(uuid) to authenticated;

drop policy if exists incident_confirmations_insert on public.incident_confirmations;
create policy incident_confirmations_insert on public.incident_confirmations
  for insert with check (
    author_id = (select auth.uid())
    and public.can_confirm_incident(report_id)
  );

Le set search_path évite qu’un objet homonyme placé par l’appelant dans un autre schéma soit résolu à la place des tables visées, un classique des fonctions security definer (doc PostgreSQL 15, CREATE FUNCTION).

La vue des voisins : de security_invoker à security_definer

Même mécanisme, deuxième victime : la vue qui montre aux voisins les incidents en cours était en security_invoker, c’est-à-dire exécutée avec les droits de l’appelant. Elle héritait donc de la policy resserrée, et se serait vidée de tout signalement de voisin, sa raison d’être.

Elle est recréée sans security_invoker, donc avec les droits de son propriétaire, et avec security_barrier :

create view public.v_live_incidents_for_member
  with (security_barrier = true)
as
-- colonnes simplifiées : ni auteur ni description
select ir.id, ir.residence_id, ir.category, ir.severity,
       ir.occurred_at, ir.confirmation_count
from public.incident_reports ir
where ir.deleted_at is null
  and ir.occurred_at >= now() - interval '2 hours'
  and ir.author_id <> (select auth.uid())
  and public.is_residence_member(ir.residence_id)
  and exists (
    select 1 from public.residence_members rm
    where rm.residence_id = ir.residence_id
      and rm.user_id = (select auth.uid())
      and rm.share_level = any (array['summary'::text, 'full'::text])
      and rm.revoked_at is null
  );

Une vue qui contourne la RLS doit porter sa garde elle-même : la condition de réciprocité (on ne voit les incidents des voisins que si l’on partage les siens) a été déplacée telle quelle de la policy vers ce WHERE. security_barrier empêche qu’une fonction fournie par l’appelant dans sa requête s’exécute avant ce filtrage et lui révèle des lignes filtrées.

⚠️ Piège voisin, rencontré plus tôt sur le même projet : une policy de manager_members qui relisait manager_members dans son exists provoquait 42P17 (infinite recursion detected in policy) ; l’erreur touchait trois tables, dont deux dont les policies se référençaient mutuellement. Même remède : trois helpers security definer, puis des policies réécrites pour les appeler.

Bilan du correctif : 7 tests RLS ajoutés et 4 réécrits, 248 au total. Si une table renvoie une erreur plutôt que zéro ligne, le problème est ailleurs : voir permission denied for table, un GRANT et pas la RLS.

Lessons learned

Une sous-requête dans une policy RLS subit la RLS de la table qu’elle lit. Le exists d’une policy d’INSERT dépend donc des policies de lecture d’autres tables.
Resserrer une policy de lecture impose de chercher qui la lit indirectement. Les policies, vues et fonctions security invoker qui interrogent la table sont des lecteurs comme les autres.
Une fonction security definer qui renvoie un booléen vérifie sans exposer. Elle répond « ce signalement est-il confirmable ? » sans rendre la ligne lisible.
Une vue qui contourne la RLS doit porter sa garde dans son WHERE. Avec security_barrier, pour que le filtrage passe avant toute fonction de l’appelant.
Rejouer l’ancienne policy contre la base de test prouve l’effet de bord. Le refus d’insertion s’observe en une requête, là où la lecture du diff ne montre rien.