Postgres RLS multi-tenant boilerplate for Next.js + Supabase
Most Next.js SaaS templates ship a `team_id` column and call it multi-tenant. That is application-layer isolation: it works until one query forgets the `where team_id = ?` clause, and then a row leaks. A Postgres RLS boilerplate pushes the check into the database, so the leak path closes whether the call site is a controller, a cron job, or an admin script.
Why RLS belongs in the boilerplate, not in the backlog
Row Level Security is one of those Postgres features that is easy to defer and expensive to retrofit. Once a codebase has dozens of tenant-scoped tables and ORM calls, going back to add policies means auditing every query path and writing migrations during a security panic.
SaaSForge Core treats RLS as the default at clone time: every workspace-scoped table ships with its policy in the same SQL migration. New tables you add follow the same template. Drizzle, raw SQL, Supabase client, and Postgres functions all hit the same enforcement layer.
The JWT claim → workspace membership join
Supabase Auth issues a JWT with the user's id in the `sub` claim. A small Postgres helper function reads that claim and joins it against `workspace_members` to derive which workspaces the request is allowed to read. Every policy on a tenant-scoped table references that helper.
The trade-off worth knowing: membership lives in a table, not in the JWT. A revoked member loses access on the next query, not at next token rotation. That is the right default for B2B products where access changes need to take effect immediately.
create table documents (
id uuid primary key default gen_random_uuid(),
workspace_id uuid not null references workspaces(id) on delete cascade,
title text not null,
created_at timestamptz not null default now()
);
alter table documents enable row level security;
create policy "members read workspace documents"
on documents for select
using (
workspace_id in (
select workspace_id
from workspace_members
where user_id = (auth.jwt() ->> 'sub')::uuid
)
);What ships beyond the policies
Policies alone are not a boilerplate. SaaSForge Core also ships the surrounding pieces: a `workspaces` table, `workspace_members` with explicit roles, invitation flows via Resend, ownership transfer, and a workspace switcher in the dashboard. These are the moving parts that make RLS usable in product code, not just theoretically correct.
Migrations are written so a new tenant-scoped table is a copy-paste-rename exercise rather than a research project. The pairing of DDL and policy in the same file is the maintenance discipline that keeps long-term drift from sneaking in.
Performance notes from running RLS in production
RLS is implemented as a query rewrite: the planner injects the policy predicate into your SQL. With an index on `workspace_id` and another on `workspace_members(user_id, workspace_id)`, the policy join is cheap. The slow path is policies that cannot use indexes; the migrations ship with the indexes already in place.
Bulk admin operations that need to bypass RLS (for support, backfills, reporting) should use the Supabase service-role key and run through a clearly named server-side path. Mixing service-role queries into request-scoped controllers defeats the purpose of having RLS in the first place.