The Circular RLS Trap: What We Learned Building Multi-Tenant PostgreSQL for Senquel

This post is written for developers who are new to PostgreSQL's Row Level Security — or who understand the concept but haven't yet hit the specific failure modes that come with multi-tenant schema design. Every section below maps to something that went wrong in production, or a decision we got right by accident and then had to understand properly.

If you're already comfortable with RLS basics, jump to section 4 where the circular recursion bug starts.

1. What "multi-tenant" means for a database

Senquel is used by multiple interior design firms. Each firm's data — projects, clients, invoices, messages — must be completely invisible to every other firm. There are two common ways to enforce this:

• One database per tenant: Physically separate databases. Simple isolation, but expensive to operate and painful to query across.
• Shared database, tenant column: All firms share one database. Every row carries a firm_id column identifying which firm owns it. Cheaper, easier to maintain — but isolation is only as strong as your enforcement mechanism.

Senquel uses the second approach. The enforcement mechanism is PostgreSQL's Row Level Security.

2. The schema — every table and column explained

Before looking at policies, it helps to understand exactly what each table and column does and why it exists.

firms

The root of the tenant hierarchy. Every other table traces back to a firm.

firm_members

Links a Supabase auth user to a firm, and records their role within that firm. This is how the system knows which firm a logged-in user belongs to.

projects

One row per renovation project. Directly scoped to a firm via firm_id.

project_members

Added in migration 015. A junction table recording which users are assigned to which projects. This is what enables per-project visibility: a designer only sees a project if there's a row here linking them to it.

milestones, tasks, notes

Child tables of projects. Each carries a project_id foreign key. Their RLS policies don't check firm_id directly — instead they check whether the parent project is visible, inheriting the firm isolation automatically.

3. How RLS policies actually work

A policy has two parts: USING and WITH CHECK. They do different things and it's easy to confuse them.

With that in mind, the original firm-scoped policy for projects was:

create policy "firm members can access their firm projects"
  on projects for all
  using  (firm_id = get_my_firm_id())   -- reads: only show rows from my firm
  with check (firm_id = get_my_firm_id()); -- writes: only allow writing to my firm

4. The SECURITY DEFINER helper pattern

Inside a policy expression you can write any SQL — including subqueries. The obvious way to find the current user's firm would be:

-- Naive approach — works, but fragile
using (firm_id = (
  select firm_id from firm_members where user_id = auth.uid() limit 1
))

This runs the subquery with RLS active on firm_members too. That's fine as long as firm_members's own RLS policy doesn't reference projects. But it's a trap waiting to be walked into. The safe approach is a SECURITY DEFINER helper function that performs this lookup outside the RLS context entirely.

create or replace function get_my_firm_id()
returns uuid
language sql
security definer   -- runs as postgres; bypasses all RLS
stable             -- (see note below)
set search_path = public  -- (see note below)
as $$
  select firm_id
  from   firm_members
  where  user_id = auth.uid()
  limit 1;
$$;

-- Revoke from public first, then grant only to authenticated users
revoke all on function get_my_firm_id() from public;
grant execute on function get_my_firm_id() to authenticated;

With this helper in place, the projects policy is clean and loop-free:

create policy "firm members can access their firm projects"
  on projects for all
  using  (firm_id = get_my_firm_id())
  with check (firm_id = get_my_firm_id());

This worked cleanly for six migrations. Then migration 015 introduced project_members, and every project in the system silently disappeared overnight.

5. Migration 015: adding project-level visibility

Until migration 015, every firm member could see every project in their firm. The goal was finer control: owners and PMs keep blanket visibility; designers can only see projects they're explicitly assigned to.

We created the project_members junction table and rewrote the projects SELECT policy to check it. Here's the policy in full:

create policy "firm members select projects"
  on projects for select
  using (
    firm_id = get_my_firm_id()          -- must be in my firm
    AND (
      -- Owners and PMs: see all projects in the firm
      exists (
        select 1 from firm_members fm
        where fm.user_id = auth.uid()
          and fm.firm_id = projects.firm_id
          and fm.role in ('owner', 'pm')
      )
      OR
      -- Designers: only see projects they're a member of
      exists (
        select 1 from project_members pm
        where pm.project_id = projects.id
          and pm.user_id = auth.uid()
      )
    )
  );

We also gave project_members its own RLS policy, so designers can't read the membership table for projects they're not part of:

create policy "firm members read project_members"
  on project_members for select
  using (
    project_id in (
      select id from projects where firm_id = get_my_firm_id()
    )
  );

Both policies look correct in isolation. The problem only appears when PostgreSQL tries to evaluate them together.

6. The circular recursion bug

To understand the loop, trace exactly what PostgreSQL does when an authenticated designer runs SELECT * FROM projects:

Because this error fires on every authenticated SELECT on projects, it also blocked new project creation — the post-insert trigger that adds the creator as a lead member tries to read projects, hits the loop, and fails silently.

7. Migration 016: breaking the loop

The root cause: project_members's policy ran a bare subquery on projects, which re-triggered that table's RLS. The fix is a second SECURITY DEFINER helper that fetches the same information — which projects belong to the current user's firm — without going through RLS at all.

create or replace function get_my_firm_project_ids()
returns setof uuid   -- returns a set (zero or more UUIDs), not just one
language sql
security definer     -- bypasses RLS; joins projects + firm_members raw
stable
set search_path = public
as $$
  select p.id
  from   projects p
  join   firm_members fm on fm.firm_id = p.firm_id
  where  fm.user_id = auth.uid()
$$;

revoke all on function get_my_firm_project_ids() from public;
grant execute on function get_my_firm_project_ids() to authenticated;

The project_members policies are then rewritten to use this helper instead of the raw subquery:

-- Before (causes recursion):
create policy "firm members read project_members"
  on project_members for select
  using (project_id in (
    select id from projects where firm_id = get_my_firm_id()
    --              ^^^^^^^^ this triggers projects RLS → loop
  ));

-- After (loop-free):
create policy "firm members read project_members"
  on project_members for select
  using (project_id in (select get_my_firm_project_ids()));
  --                           ^^^^^^^^^^^^^^^^^^^^^^^^
  --     SECURITY DEFINER function — reads projects without triggering RLS

8. The backfill — and what idempotent means

Migration 016 also re-ran the membership backfill: inserting a project_members row for every (project, firm member) pair so that existing projects remained visible after the new visibility rules kicked in.

insert into project_members (project_id, user_id, role, added_by, added_at)
select p.id, fm.user_id, 'collaborator', null, now()
  from projects p
  join firm_members fm on fm.firm_id = p.firm_id
  on conflict (project_id, user_id) do nothing;

9. Learnings

• Never let two tables' RLS policies reference each other through regular subqueries. If Table A's policy checks Table B, and Table B's policy checks Table A, PostgreSQL recurses until it errors — at query time, not migration time.
• SECURITY DEFINER helpers are the correct escape valve. They should return a minimal safe result (a UUID, a set of IDs) that a policy then uses. Never a function that returns full rows. They're a gatekeeper — not a shortcut around security.
• Always add three flags to every SECURITY DEFINER function: STABLE (cache within transaction), set search_path = public (prevent schema injection), and explicit GRANT/REVOKE to the right roles only.
• RLS is not validated at migration time. CREATE POLICY succeeds even if your policy would cause infinite recursion. The error only appears at query time. Add a post-migration smoke test: run SELECT COUNT(*) FROM projects as an authenticated user before calling a migration complete.
• Write backfills idempotently. ON CONFLICT … DO NOTHING costs nothing and prevents a re-run from crashing. Every backfill in a production migration should include it.

All migrations referenced here are in the supabase/migrations/ directory. Migrations 005 (baseline RLS), 015 (project_members + the bug), and 016 (the fix) are the three most instructive to read in sequence.