Infinite recursion in Postgres RLS: a SECURITY DEFINER gotcha

Spent a few hours yesterday on what looked like a haunted Postgres bug. Sharing the fix in case someone else hits it.

The setup

I had a multi-tenant app with a profiles table. I wanted "team admins can see all team profiles". Naive policy:

sql
CREATE POLICY "admin_sees_team"
    ON public.profiles FOR SELECT
    TO authenticated
    USING (
        team_id IS NOT NULL
        AND EXISTS (
            SELECT 1 FROM public.profiles me
            WHERE me.id = auth.uid()
              AND me.role = 'admin'
        )
    );
Looks innocent, right?

The bug
Every query against profiles returned:

ERROR: 42P17: infinite recursion detected in policy for relation "profiles"
The policy's EXISTS subquery hits profiles — which triggers the policy again — which hits profiles again. Postgres detects it and bails.

The fix that didn't work
My first attempt: extract the check into a SQL function with SECURITY DEFINER.

CREATE FUNCTION is_admin(uid uuid) RETURNS boolean AS $$
    SELECT EXISTS (
        SELECT 1 FROM profiles WHERE id = uid AND role = 'admin'
    );
$$ LANGUAGE sql STABLE SECURITY DEFINER;
Still recursed. Why? Postgres inlines simple SQL functions during query planning. When inlined, the SECURITY DEFINER context is lost and RLS applies to the inner query → recursion is back.

The fix that worked
Use LANGUAGE plpgsql instead. plpgsql functions are never inlined:

CREATE OR REPLACE FUNCTION is_admin(uid uuid)
RETURNS boolean
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM profiles
        WHERE id = uid AND role = 'admin'
    );
END;
$$;
Then the policy:

CREATE POLICY "admin_sees_team"
    ON profiles FOR SELECT
    TO authenticated
    USING (team_id IS NOT NULL AND is_admin(auth.uid()));
No more recursion. The plpgsql wrapper preserves the SECURITY DEFINER context, the subquery runs as the function owner (postgres), and RLS doesn't apply.

Takeaway
When a RLS policy needs to query its own table:

Don't use a raw subquery — recursion.
SQL functions look like a fix but get inlined.
plpgsql functions with SECURITY DEFINER are the only safe way.
Hope this saves someone the 3 hours I lost.