Supabase RLS and multi-tenant isolation: how much can the database enforce?

When you build a multi-tenant SaaS, the question of where to enforce tenant isolation is one of the most consequential design decisions you will make. Application-layer filtering works — right up until someone forgets a WHERE clause and another tenant's data becomes visible. We did not want to carry that risk in Storyie, so we pushed the boundary down to the database using Supabase Row Level Security on every table.

This post covers the design decisions behind that choice, the three RLS patterns we landed on, and the trade-offs we hit in practice.

TL;DR

• Application-only filtering fails open: a missing WHERE clause leaks data. RLS fails closed: the database returns nothing it was not supposed to.
• Storyie uses tenant_members as the join table that every tenant-scoped RLS policy pivots on.
• Two ownership models coexist: user-owned data (checked via auth.uid()) and tenant-owned data (checked via a tenant_members join). Keep them separate.
• Wrap auth.uid() in a scalar subquery (SELECT auth.uid()) to avoid per-row function calls — it is a one-line optimization with a real cost impact at scale.
• Co-locate RLS policies with Drizzle schema definitions so policy changes ship in the same migration as the table change.

Three approaches to multi-tenant isolation

Multi-tenant isolation generally falls into one of three buckets:

We chose row-level isolation with RLS. Supabase ships it as a first-class PostgreSQL feature, there is no extra infrastructure to run, and the operational overhead is low enough for a small team. The trade-off is that isolation is only as good as the policies you write — which is exactly why we treat writing them carefully as non-negotiable.

Schema overview: tenant_members is the pivot point

Storyie's tenant model is straightforward. Every user who can act on behalf of a tenant has a row in tenant_members with a role column. Every tenant-scoped RLS policy goes through this table.

auth.users ──┐
             ├── tenant_members ──┬── tenants
             │   (user_id,        │   (id, slug, stripe_customer_id)
             │    tenant_id,      │
             │    role)           ├── subscriptions (tenant_id)
             │                    ├── tenant_feature_usage (tenant_id)
             └── diaries          └── invoices (tenant_id)
                 (user_id)

Notice the two ownership models living side by side:

1. User-owned data — diaries, notes: the user_id column identifies the owner directly.
2. Tenant-owned data — subscriptions, tenant_feature_usage, invoices: the tenant_id column identifies the owning tenant, and access is mediated through tenant_members.

This split maps directly onto two distinct RLS policy shapes.

RLS design patterns

Pattern 1: tenant membership for SELECT control

The base pattern for every tenant-scoped table:

-- tenants table: only members of this tenant can see it
CREATE POLICY "members_can_view_own_tenants" ON tenants
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM tenant_members
      WHERE tenant_members.tenant_id = tenants.id
      AND tenant_members.user_id = (SELECT auth.uid())
    )
  );

The (SELECT auth.uid()) wrapper is intentional. PostgreSQL treats it as a scalar subquery and evaluates it once per query rather than once per row. This is the performance optimization pattern recommended in Supabase's own documentation, and it matters when tenants has thousands of rows being scanned.

Pattern 2: role-based write control

The tenant_members table itself needs tighter control. Only owners and admins should be able to add members:

-- only owner or admin can insert new members
CREATE POLICY "admins_can_insert_members" ON tenant_members
  FOR INSERT WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM tenant_members
      WHERE user_id = (SELECT auth.uid())
      AND role IN ('owner', 'admin')
    )
  );

We use IN rather than EXISTS here deliberately. EXISTS only tells you whether a row is present; IN lets you verify that the tenant_id on the incoming row actually belongs to a tenant where the requesting user holds an elevated role. Without that check, a user could insert a row with any tenant_id they guessed.

Pattern 3: service_role-only tables

Some tables should never be readable by end users at all. Diary view logs are a good example — we aggregate them into a view_count column via a scheduled job, so there is no reason to expose raw log rows to the client:

CREATE POLICY "diary_view_logs_service_insert" ON diary_view_logs
  FOR INSERT TO service_role
  WITH CHECK (true);

No SELECT policy exists for regular users. The data is write-only from the service role and read-only through the aggregated column. This approach works well for any high-write telemetry table where the raw data has no legitimate client use case.

Why the database layer matters

The standard objection is: "Can't we just filter by tenant_id in every query?" Yes, and we do. The problem is "every query."

In practice, application-layer filtering breaks down in predictable ways:

• A new endpoint gets added and the filter is forgotten.
• An admin dashboard or batch script queries the database without tenant context.
• An ORM JOIN pulls in related rows from another tenant because the join condition was not tight enough.

RLS is the fallback when any of these happen. A query that omits a tenant_id filter does not return cross-tenant data — it returns only what the authenticated user is entitled to see. This is defense-in-depth: the application layer filters correctly in the happy path, and the database layer prevents the worst outcome when it does not.

The "fail closed" property is especially valuable for a small team. You cannot review every query for every feature before it ships, but you can trust that RLS has your back when something slips through.

// Even a completely unfiltered query stays within the current tenant
const allSubscriptions = await db.select().from(subscriptions);
// → only returns rows where the current user is a tenant member

Trade-offs to know about

Performance

RLS policies execute on every database operation. Tenant membership policies add a subquery against tenant_members on every hit. Mitigation steps we use:

• Indexes on tenant_members(user_id) and tenant_members(tenant_id).
• The (SELECT auth.uid()) scalar subquery form everywhere.
• Selective use of IN vs EXISTS depending on what the policy needs to verify.

At Storyie's current scale these are sufficient, but tenant_members is the query plan hot spot to watch as membership tables grow. EXPLAIN ANALYZE on slow queries involving tenant-scoped tables is the first debugging step.

Testing complexity

Testing against an RLS-enabled database requires a decision on every test: run as the service role and bypass policies, or run as a real user and test through them.

Our approach:

• Unit tests: service role key, RLS bypassed. Test business logic in isolation.
• E2E tests: real auth flow, RLS fully active. Catch any policy gaps that unit tests cannot see.

The discipline here is that E2E tests must never use the service role, or you lose the integration check entirely.

The user-owned / tenant-owned boundary

The hardest design question is what to do when a table does not fit cleanly into either category. "Shared diaries within a tenant" is an example — does diaries grow a tenant_id column, or does a new join table mediate access?

Our current rule: never mix ownership models on the same table. A row is owned by a user or by a tenant, not both. When a new feature blurs that line, we pause and decide explicitly rather than adding columns opportunistically. Blurred ownership makes policies hard to audit and easy to get wrong.

Drizzle ORM co-location

One of the most practical wins from this setup is using Drizzle's pgPolicy helper to define policies alongside the table schema:

export const tenants = pgTable(
  "tenants",
  {
    id: uuid("id").primaryKey().notNull().default(sql`gen_random_uuid()`),
    slug: text("slug").notNull().unique(),
    displayName: text("display_name"),
    stripeCustomerId: text("stripe_customer_id"),
    // ...
  },
  (table) => ({
    selectPolicy: pgPolicy("members_can_view_own_tenants", {
      for: "select",
      using: sql`exists (
        select 1 from tenant_members
        where tenant_members.tenant_id = ${table.id}
        and tenant_members.user_id = (select auth.uid())
      )`,
    }),
  })
);

When pnpm generate runs, the migration includes both the table DDL and the policy. A schema change and its policy update are guaranteed to land in the same PR. The failure mode we avoid is adding a new column without auditing whether the existing policies still gate access correctly — with co-location, that audit happens naturally during code review.

Where each layer fits

RLS is the last line of defense, not the only one. The application layer enforces business rules that RLS cannot express — quota limits, feature entitlements, input shape validation. The value of RLS is that it makes the failure mode of a missing application check safe rather than catastrophic.

For a small team shipping fast, that guarantee is worth the added complexity in policy design and testing. We have shipped features with missing application-layer checks and caught them before any data escaped because RLS was there. That has happened more than once, and each time it reinforced that moving the isolation guarantee to the database was the right call.

Related Posts

• Multi-tenant subdomain routing in Next.js — how tenant context flows from the URL into the application layer
• Database schema design with Drizzle ORM — the overall schema conventions that RLS policies build on
• Building a Monorepo with pnpm and TypeScript — workspace structure for the packages referenced above

Try Storyie

Storyie is a diary app that ships on web and iOS. The tenant isolation described here is what lets teams share a workspace without any risk of data crossing the boundary. The policy layer is invisible when it works correctly — which is exactly the point.