Designing multi-tenant Supabase Row Level Security for an AI PRD generator

#supabase #postgres #security #saas

Multi-tenancy bugs rarely look like bugs. The query works. The page loads. The data is just… somebody else's.

When I built Web Dev Mehedi — a tool that turns an app idea into a PRD, a database schema, API contracts and coding-agent rules — the generator had to produce Supabase Row Level Security (RLS) policies for every table it invented, for apps it had never seen. That forced me to stop treating RLS as a per-project craft and turn it into a small set of rules a generator can apply reliably. Those rules turned out to be good rules for humans too.

Rule 1: Every tenant-owned table carries its owner

In generated schemas, any table that belongs to a user or a workspace has a non-nullable ownership column (owner_id or workspace_id) referencing the parent, with on delete cascade. No ownership column, no RLS — so the ownership column is not optional decoration; it is the thing the policy will test.

create table public.products (
  id uuid primary key default gen_random_uuid(),
  shop_id uuid not null references public.shops(id) on delete cascade,
  name text not null,
  stock integer not null default 0 check (stock >= 0),
  created_at timestamptz not null default now()
);

Rule 2: Policies are written the same hour as the table

A table without policies, on a project where RLS is enabled, fails closed — nothing is readable. Teams experience that as "Supabase is broken," disable RLS "temporarily," and ship a data leak. In the generator, create table and create policy are emitted in the same block, always. For a human team the equivalent rule is: the migration that creates the table contains its policies, or the PR doesn't merge.

alter table public.products enable row level security;

create policy "shop members manage their products"
  on public.products
  for all
  using (
    exists (
      select 1 from public.shop_members m
      where m.shop_id = products.shop_id
        and m.user_id = auth.uid()
    )
  );

Rule 3: Membership beats ownership for anything shared

The naive policy tests shop_id in (select id from shops where owner = auth.uid()). That breaks the moment a second person joins the shop — a staff member is not the owner, so they see nothing, and someone "fixes" it by widening the policy. A membership table (shop_members) as the single source of truth survives owners, staff and future roles, because the policy asks one question: is this user a member of the tenant this row belongs to?

Rule 4: One policy per operation when behaviour differs

for all is fine when reads and writes follow the same rule — it keeps generated SQL short and auditable. The moment they differ (anyone in the shop can read, only admins can delete), split into per-operation policies. Four explicit policies are easier to review than one clever one, and RLS review is where "clever" goes to die.

Rule 5: Never bypass RLS from application code to "make it work"

The service-role key bypasses RLS entirely. In generated API contracts, it is reserved for genuinely system-level jobs (billing webhooks, scheduled cleanup) and is explicitly forbidden in user-facing endpoints. When a query returns nothing, the correct debugging order is: (1) is RLS enabled on the table, (2) does a policy cover this operation, (3) does the membership row exist. "Use the service key" is not step 4.

The checklist the generator applies — steal it

Tenant table → ownership/membership column present, non-null, indexed.
RLS enabled in the same migration as the table.
A policy exists for every operation the API contract uses.
Policies test membership, not ad-hoc conditions copied from the app layer.
No user-facing endpoint uses the service-role key.

None of this is exotic. That's the point: multi-tenant safety mostly comes from applying boring rules every single time, which is exactly the kind of thing a generator — or a checklist taped to your PR template — is better at than memory.

If you want to see these rules applied to your own app idea, the generator is live: describe the product, and the schema comes back with the policies already written. Web Dev Mehedi — 50 free credits, no card.

DEV Community