A couple of weeks ago I set out to build something I actually wanted to exist: a starter kit for B2B SaaS apps that handles the boring-but-critical stuff: auth, multi-tenancy, and team invites so I could stop rebuilding the same plumbing every time I had a new idea.
It went fine, right up until Postgres told me I'd created an infinite loop in my own security policy.
The setup
Nothing fancy — Next.js, Supabase, Vercel. The whole point of the kit is that users belong to organisations, not just individual accounts, because that's how basically every real B2B product works (think Slack, Notion, Linear). Multiple people share access to the same workspace, and one org's data should never, ever leak into another org's view.
That last part is where Row Level Security (RLS) comes in. RLS is Postgres's way of enforcing access rules at the database level instead of hoping your application code remembers to filter correctly every single time. You write a policy once, and Postgres enforces it on every query, no exceptions, no "oops I forgot the WHERE clause."
I set up two tables: organisations and organization_members (a join table linking users to orgs). Then I wrote what seemed like a completely reasonable policy — members should only be able to see membership rows for organisations they're already part of:
sql
create policy "Users can view memberships in their orgs"
on organization_members for select
using (
organization_id in (
select organization_id from organization_members
where user_id = auth.uid()
)
);
Read that again slowly. I didn't, and Postgres made me regret it.
infinite recursion detected in policy for relation "organization_members"
The policy for organization_members checks whether a row is visible by... querying organization_members. Which triggers the policy again. Which queries the table again. Forever.
It's such an obvious mistake in hindsight, but it's also an incredibly easy one to make, because the logic feels correct when you're writing it. "Only show rows for orgs the user belongs to" is a completely reasonable rule — I just implemented the check by querying the very table the policy was protecting, which meant every read of the table triggered another read of the table to authorise the first read.
The fix: let a function do the dirty work
The way out is a security definer function — a function that runs with elevated privileges, bypassing RLS internally so it can safely check membership without re-triggering the policy that's asking the question:
sql
create or replace function get_user_org_ids()
returns setof uuid
language sql
security definer
set search_path = public
as $$
select organization_id from organization_members where user_id = auth.uid()
$$;
Then the policy calls the function instead of querying the table directly:
sql
create policy "Users can view memberships in their orgs"
on organization_members for select
using (
organization_id in (select * from get_user_org_ids())
);
The function still ultimately reads from organization_members, but because it runs as security definer, it steps outside the RLS check instead of triggering it again. The recursion stops, and the actual security logic is unchanged — users still only see orgs they belong to.
A second, sneakier version of the same problem
I thought I was done, but a few steps later I hit new row violates row-level security policy for table "organizations" — a different table, and at first this looked like a totally separate bug.
It wasn't. When you do an insert-then-select in one call (which Supabase's client does automatically when you chain .insert().select()), Postgres inserts the row and then tries to hand it back to you by selecting it. My organisation's SELECT policy only allowed a user to see orgs they were already a member of — but at the exact moment of creation, the membership row didn't exist yet. So the insert succeeded, the select-back failed, and Postgres reported the whole thing as an RLS violation on the insert, which sent me looking in completely the wrong place for a while.
The fix was just widening the SELECT policy slightly, so an org's owner can see it immediately, even before their membership row exists:
sql
create policy "Users can view their organisations"
on organisations for select
using (
owner_id = auth.uid()
or id in (select * from get_user_org_ids())
);
What I'd tell past-me
RLS is genuinely one of the best tools for making sure you don't accidentally leak one customer's data to another — enforcing it at the database layer instead of trusting every code path to remember to filter correctly is worth the setup pain. But two things are easy to get wrong, and both bit me on the same afternoon:
Don't write a policy on table X that queries table X to authorise itself. If your policy needs to check the same table it's protecting, wrap that check in a security definer function instead of querying it directly inside the policy.
Remember that insert-then-select is two operations, not one, and your SELECT policy needs to actually allow the person who just inserted a row to read it back — which isn't automatic just because they were the one who created it.
Neither of these is documented anywhere obvious until you hit them, which is exactly why I'm writing this down now, mostly for the next person (possibly future me) who gets the same cryptic recursion error and has no idea why a policy that reads perfectly correct in English is actually an infinite loop in practice.
If you're setting up multi-tenant RLS for the first time, save yourself the afternoon I lost — write your membership-check policies through a security definer helper function from the start, not as an afterthought once Postgres yells at you.
I packaged this whole setup—auth, orgs, RLS, invites—into a starter kit so I (and hopefully others) don't have to solve it from scratch again. Happy to answer questions if you're working through something similar.
Top comments (0)