Supabase Row Level Security: A Multi-Tenant Pattern for Agency Client Sites

One Postgres database, many tenants, zero data leakage. Here’s the RLS setup that holds up under real production traffic.

John Cravey with AIFounder4 min read

One Supabase project can serve a whole book of client sites: one Postgres, one Storage layer, one Auth instance. Row Level Security policies scope every read and write to a `site_id`, and the service-role key never leaves a server context. You pay for one project instead of ten, migrations happen in one place instead of ten, and the tenant boundary sits in the database, where a leaky API can’t bypass it.

We ran this setup across the FH client book before moving our sites onto a shared CMS, and most of it still holds. Here’s how it works.

Why one project, not one per client

Cost: Supabase pricing is per-project. Ten clients means ten subscriptions for projects that mostly sit idle. One shared Pro project covers a small client book comfortably.

Operational simplicity: one place to manage migrations, one place to monitor logs, one place to set up backups. A schema change lands once and applies to every tenant at the same time.

Trade-off: a Supabase outage takes every client down. Mitigate it with Cloudflare caching in front of every read endpoint so static content keeps serving even if Supabase is unavailable, and verify your backups weekly.

The schema: site_id on every row

Every tenant-scoped table has a `site_id text not null` column. The value is a slug like `"client-a"` or `"client-b"`. There’s a `sites` table with the canonical list. Foreign keys enforce that every row points at a real site.

create table sites (
  id text primary key,
  name text not null,
  created_at timestamptz default now()
);

create table submissions (
  id uuid primary key default gen_random_uuid(),
  site_id text references sites(id) not null,
  name text not null,
  email text not null,
  phone text,
  message text,
  created_at timestamptz default now()
);

create index on submissions(site_id, created_at desc);

The RLS policies: scope by site_id, reject everything else

Every tenant-scoped table has RLS enabled. The policies allow reads and writes only when the caller’s JWT contains a `site_id` claim matching the row. The default is deny. There is no anon-read policy on tenant tables — anon access to client data is impossible from the client SDK.

alter table submissions enable row level security;

create policy "insert own site" on submissions
  for insert with check (site_id = (auth.jwt() ->> 'site_id'));

create policy "select own site" on submissions
  for select using (site_id = (auth.jwt() ->> 'site_id'));

Where the site_id comes from

Two paths. For authenticated users, the `site_id` is injected into the JWT at sign-in by a Supabase auth hook. For anonymous users (a public lead form), use the service-role key on the server side. The role bypasses RLS, so the server code has to set `site_id` itself, from the per-tenant environment variable. The client never sees the service-role key.

Storage buckets: one per tenant

Tenant data goes through RLS-scoped tables. Tenant media goes through bucket-per-tenant Storage. Give each client a public bucket (`client-a-images`, `client-b-images`) for marketing imagery, and a private bucket for anything sensitive. Public buckets are read-only-to-anon, write-only-to-service-role. Private buckets have RLS policies that scope to the user’s `site_id` claim.

An `imageUrl()` helper resolves bucket paths into public URLs. Swapping buckets later is a single-file change.

Migration workflow

Use the Supabase CLI for migrations, version-controlled in one repo. Each migration is a numbered SQL file under `supabase/migrations/`. Local development runs against a local Supabase stack via `supabase start`; migrations apply locally first, get tested in staging, then ship to prod with `supabase db push`.

# Create a new migration
supabase migration new add_lead_score_column

# Edit supabase/migrations/202605061200_add_lead_score_column.sql
# Apply locally
supabase db reset

# Apply to prod
supabase db push --linked

Four migration rules for a shared project

  1. Migrations are forward-only. Don’t write down-migrations: if a migration is wrong, ship a new one that fixes it.
  2. Never run a migration that takes more than 30s on a populated table without a maintenance window. ALTER TABLE … ADD COLUMN with a default value on a 5M-row table will lock the table.
  3. Always test on a clone of production data. `supabase db dump` then restore locally before migrating in prod. This is how you catch a long table lock before it hits production.
  4. Migrations and code changes ship together. A migration that adds a column gets the code that uses it in the same PR.

Auth: per-tenant claims

Supabase Auth uses GoTrue with custom JWT claims. Inject `site_id` into the JWT with an auth hook that runs server-side on sign-in. The hook reads the user’s row in a `user_sites` join table and adds the relevant `site_id` to the claims. Every downstream RLS check uses `auth.jwt() ->> 'site_id'`.

For anon users (public site visitors filling out a form), there’s no JWT and the public RLS policies are deliberately empty. Public form submissions go through a server action using the service-role key, which sets `site_id` from the per-tenant env var. Never trust the client to provide its own site_id.

Backups and disaster recovery

Supabase Pro includes daily point-in-time recovery. Supplement it with weekly `pg_dump` snapshots stored outside Supabase, following the “three places, two formats, one offsite” rule. If Supabase ever fully fails, you can rehydrate the database into a fresh project from those snapshots.

Monitoring and alerting

Supabase exposes query performance and connection metrics in the dashboard. Pipe slow-query logs into your alerting tool and alert on anything over 1s. The Postgres-level monitoring catches things the application monitoring doesn’t — a missing index, a runaway sequential scan, a connection pool exhaustion.

When NOT to use shared multi-tenancy

  • Regulated data: HIPAA, PCI, anything with strict data isolation requirements. Even with RLS, the regulator may insist on physical isolation. Spin up a separate project.
  • Tenants with wildly different load. One client doing 10k RPS will affect the database for everyone else. Pull the high-volume tenant onto their own project.
  • Customer-controlled schemas. If the tenant gets to define columns, the shared-schema approach breaks down. Use a different per-tenant strategy (schema-per-tenant or separate projects).

Where FH runs this now

Our client sites ran on this shared Supabase project until we moved them onto a multi-tenant CMS. The idea carried over: one database for the whole client book, one place to migrate and monitor, and every record tagged with the client it belongs to. What changed is where the boundary lives. The CMS enforces it in its own access rules rather than in RLS policies, and leads, media and logins all live there now. If you’re running multi-tenant SaaS on per-tenant databases or trying to reinvent this pattern on raw Postgres, book a consultation. There’s usually a simpler version of what you’re building.

Answers

Frequently asked questions

Why one project for many clients rather than one project each?

Operational surface. One project means one thing to monitor, back up, upgrade, and secure. A project per client multiplies every routine task by the client count and guarantees drift, because nobody upgrades thirty projects on the same day.

What does the schema need for multi-tenancy?

A tenant key on every row, without exception. One table missing it is one table with no isolation, and it will be the one someone queries from a shared helper. Uniqueness constraints become composite with the tenant key, which is the change people forget until two clients collide on a slug.

What should the RLS policies actually say?

Scope to the tenant and reject everything else. Deny by default, allow explicitly for the tenant in context. Policies that check only for an authenticated user provide no isolation at all, which is the common mistake because it passes every test written by the person who wrote it.

Where does the tenant identifier come from?

From the request context rather than from client input. A tenant chosen by the caller is not a boundary. In practice it comes from the session claim or from server-side resolution of the host, and the important property is that a browser cannot ask for someone else's data by changing a parameter.

Do service-role keys bypass RLS?

Yes, completely, which is why they never reach a browser and why server code using one must apply tenant scoping itself. Most real isolation failures are not policy bugs; they are a service-role client used in a path that assumed the policies were still doing the work.

How should storage be isolated?

A bucket per tenant, so a policy mistake exposes one client rather than the whole book. Path-prefix isolation inside one bucket works until someone writes a helper that takes a path from a parameter, and then the blast radius is everyone.

What are the migration rules for a shared project?

Apply the migration before the code that depends on it, never edit an applied migration, keep one migration per logical change, and reload the API schema cache after DDL. The first rule matters most: code deployed against a missing column returns empty results silently rather than erroring.

Why does a new table need RLS enabled explicitly?

Because creating a table does not turn it on, so a new table in a tenant-scoped schema is world-readable through the API until someone remembers. It is the single most common gap in a multi-tenant setup and it is invisible in code review.

How should auth carry tenancy?

As a claim on the session, so policies can read it without an extra lookup on every query. The claim has to be set server-side at issue time; anything the client can influence is a suggestion rather than a boundary.

What should be monitored?

Policy denials, slow queries, auth failures, and storage rate limits. Denials in particular are the honest signal: a sudden rise usually means either an attack or a broken deploy, and both are things you want to know about before a customer reports them.

When is shared multi-tenancy the wrong choice?

When a client contractually requires isolation, when data-residency rules differ per tenant, or when one tenant's load would harm the others. Those are real reasons for separate projects. Wanting them to feel separate is not, and it costs the operational simplicity that made the pattern worth having.

How do I test that isolation actually holds?

Write tests that attempt cross-tenant reads and assert they return nothing, running as a normal user rather than a service role. Tests that only assert the happy path prove the query works, not that the boundary does, and the boundary is the entire point.

Question we did not answer? Ask us directly and we will answer it here.

John Cravey, Founder
Written by
John Cravey
Founder

Founder of Frontend Horizon. Writes most of the long-form work on the FH blog.

Newer post
AI Overviews and Zero-Click Search: The 2026 SEO Reality
Older post
Cloudflare DNS and CDN: The Base Configuration for Every FH Client Site
Keep reading

More from the blog

Supabase·4 min

Supabase Edge Functions: When They’re Worth It and When They’re Not

Edge Functions are great for jobs that have to live outside your Next app. Not everything does. Here’s the decision framework.

Supabase·3 min

Reading Supabase Logs: The Five Queries That Catch 80% of Production Issues

The Supabase log explorer is underused. These five queries are the first place we look when something’s wrong.

Supabase·4 min

Migrating from Firebase to Supabase: The Real Cost and the Step-by-Step Plan

Firebase pricing scales worse than Supabase past a certain point. Here’s the migration plan that worked for one of our clients.