Multi-Tenant Supabase + Postgres RLS: Stop Tenant Data Bleed

Integration tests that exercise the policy

The test that matters is the one that runs cross-tenant SELECTs and asserts they return zero rows. Most teams write the test that says "tenant A can read tenant A's rows," see it pass, and stop there. That test passes whether or not the policy works, because tenant A's query against tenant A's data succeeds in both worlds. The real test is the negative case.

import { createClient } from "@supabase/supabase-js";
import { describe, expect, it, beforeAll } from "vitest";

const SUPABASE_URL = process.env.SUPABASE_URL!;
const SUPABASE_ANON_KEY = process.env.SUPABASE_ANON_KEY!;

describe("invoices RLS: tenant isolation", () => {
  let tenantAClient: ReturnType<typeof createClient>;
  let tenantBClient: ReturnType<typeof createClient>;
  let invoiceAId: string;
  let invoiceBId: string;

  beforeAll(async () => {
    // Two pre-seeded users, each in a different tenant.
    tenantAClient = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);
    tenantBClient = createClient(SUPABASE_URL, SUPABASE_ANON_KEY);

    await tenantAClient.auth.signInWithPassword({
      email: "alice@tenant-a.test",
      password: process.env.TEST_PW_A!,
    });
    await tenantBClient.auth.signInWithPassword({
      email: "bob@tenant-b.test",
      password: process.env.TEST_PW_B!,
    });

    // Each user inserts a row in their own tenant.
    const a = await tenantAClient
      .from("invoices")
      .insert({ amount: 100, status: "open" })
      .select("id")
      .single();
    const b = await tenantBClient
      .from("invoices")
      .insert({ amount: 200, status: "open" })
      .select("id")
      .single();

    invoiceAId = a.data!.id;
    invoiceBId = b.data!.id;
  });

  it("tenant A cannot read tenant B's invoice by id", async () => {
    const { data, error } = await tenantAClient
      .from("invoices")
      .select("*")
      .eq("id", invoiceBId);

    expect(error).toBeNull();
    // The interesting assertion: zero rows, not a forbidden error.
    // RLS filters silently — the row is invisible, not denied.
    expect(data).toEqual([]);
  });

  it("tenant A cannot list tenant B's invoices via select *", async () => {
    const { data } = await tenantAClient.from("invoices").select("id");
    const ids = (data ?? []).map((r) => r.id);
    expect(ids).toContain(invoiceAId);
    expect(ids).not.toContain(invoiceBId);
  });

  it("tenant A cannot insert a row tagged with tenant B's id", async () => {
    // Even if tenant A constructs the request, the WITH CHECK clause
    // rejects the write because the tenant_id does not match the JWT.
    const tenantBId = await getTenantIdFor("bob@tenant-b.test");
    const { error } = await tenantAClient
      .from("invoices")
      .insert({ amount: 999, status: "open", tenant_id: tenantBId });

    expect(error).not.toBeNull();
  });
});

Three properties make this test useful where a naive happy-path test is not. It runs through the same client library and the same auth flow that the real application uses, so any divergence between policy and runtime shows up here. It asserts the silent-filter behavior (zero rows, no error) — which is how RLS actually works and what real attacker probes look like. And it covers both read and write paths, because a policy that protects SELECT but not INSERT lets a tenant plant rows under another tenant's identity.

Wire the test into CI so every PR that touches a multi-tenant table runs it. The test is fast — single-digit seconds — and the cost of skipping it is the cost of the bug it catches.

Common failure modes

The first sharp edge is the developer who reaches for the service_role key when something does not work. The service_role bypasses RLS by design so admin tooling can read across tenants for back-office reports and cleanup scripts. The Supabase docs are explicit: it is for administrative tasks and should not be exposed in the browser. The failure mode is more subtle than "the key leaks." It is a backend handler that uses the service_role client to do something convenient — load a dropdown, populate an export, run a search — and now that handler has god-mode against every tenant. The fix is to keep the service_role import inside a small set of files that the build pipeline audits, and ban it everywhere else with a lint rule. If a code review finds createClient(SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY) outside the audited directory, that PR does not merge.

The second is the missing FORCE. The migration enables RLS, the policies exist, the engineer tests by running the app and seeing isolation work — because the app connects as the authenticated role, not the table owner. Then a background job, written months later, connects as the migration role to do something housekeeping-related, and that job sees every row in every tenant. The Postgres documentation states it plainly: table owners normally bypass row security as well. The fix is to add ALTER TABLE ... FORCE ROW LEVEL SECURITY immediately after ENABLE, and to add a one-line audit query to CI that fails the build if any table is enabled-but-not-forced.

The third is the user_metadata versus app_metadata mix-up. A policy that reads auth.jwt() -> 'user_metadata' ->> 'tenant_id' looks correct and works in development. In production, a tenant who knows their JWT structure can update their own user_metadata through the Supabase client and rewrite the tenant_id claim. The policy then trusts their forged claim. App_metadata is the right home because Supabase controls writes to it; user_metadata is user-writable. The fix is to stamp tenant_id into app_metadata at signup or invite (via the admin API or a server-side hook) and to read it via app_metadata in every policy.

The fourth is the policy that protects SELECT but forgets to protect INSERT. The reader cannot see another tenant's data, but a malicious request can plant a row tagged with another tenant's id. On the next read, that row appears in the wrong tenant's dashboard — not because of a leak on the read side, but because a write was allowed that should have been rejected. The fix is to set the WITH CHECK clause on every policy that covers a write operation, and to write the cross-tenant INSERT test in the integration suite above.

The fifth is the JOIN that bypasses the policy. A table without RLS joined to a table with RLS leaks through the unprotected table. Policies apply per-table; if invoices is RLS-isolated but invoice_line_items is not, a SELECT with a JOIN through invoice_line_items can return rows that belong to another tenant. The fix is to enable RLS on every table in the multi-tenant set, and to audit with SELECT relname FROM pg_class WHERE relrowsecurity = false AND relkind = 'r' in a CI step that filters down to tables in your tenant schema.

What to watch in your own implementation

Open the database and run this against every public schema table that holds tenant data:

SELECT
  c.relname AS table_name,
  c.relrowsecurity AS rls_enabled,
  c.relforcerowsecurity AS rls_forced,
  (
    SELECT COUNT(*)
    FROM pg_policies p
    WHERE p.schemaname = n.nspname AND p.tablename = c.relname
  ) AS policy_count
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind = 'r'
ORDER BY c.relname;

The query gives you three columns to inspect. Tables with rls_enabled = false are wide open. Tables with rls_enabled = true, rls_forced = false are wide open to your own background jobs and migrations. Tables with policy_count = 0 and RLS enabled are unreachable (the default-deny behavior takes over — no rows visible to anyone except superusers and bypass roles). Any row that is not "enabled, forced, with policies" is a misconfiguration. Fix those before anything else.

Next, audit the application code for service_role usage. Search the repo for SERVICE_ROLE_KEY and trace every caller. For each caller, answer two questions. Does it run only in a server context that no tenant request can reach? And is the directory it lives in covered by a lint rule that prevents new callers from being added elsewhere? If the answer to either is no, the isolation is one accidental import away from being bypassed.

Then look at the JWT shape. Pull a real JWT from a production session, decode it, and check whether tenant_id lives in app_metadata or user_metadata. If it lives in user_metadata, your policies are trusting a value the tenant can edit. Move it to app_metadata, update the signup flow to stamp it from the server side, and update every policy that reads it.

Finally, write the cross-tenant SELECT integration test if you do not already have one. Two seeded tenants, one row in each, a cross-tenant query, an assertion that the row count is zero. The test takes an hour to write and runs in seconds. It is the single test that proves the policy is doing the thing the diagram says it does, and it is the test that catches the regression when someone adds a new table and forgets to enable RLS.

The one operational discipline worth adding is a quarterly review of new tables. Every multi-tenant SaaS schema grows. The discipline is: when a new table lands in the multi-tenant set, the PR that adds it also adds the tenant_id column, the FORCE RLS lines, the policy, the index on tenant_id, and a row in the cross-tenant integration test. Encode that as a checklist in the PR template, and the policy file stops drifting from the schema.

What this looks like in production

At BFEAI the tenant_id flows from the JWT through every read and write, and the policy file is the file we audit most often when adding a feature. New tables ship with RLS enabled, FORCE on, and a policy that references the same app_metadata ->> 'tenant_id' helper as every other table in the schema. The integration suite runs the cross-tenant SELECT and the cross-tenant INSERT test on every PR, and the audit query above runs in a scheduled CI job that pages if any table drifts out of the enforced-and-forced state. The result is that "tenant bleed" is not a class of bug the team has to remember to prevent in each query — it is a class of bug the database refuses to allow, and the application code is free to be written without the constant fear that one missing WHERE tenant_id = $1 will end the company.