Row-Level Security for Multi-Tenant Analytics in Supabase

Row-Level Security in Supabase is the structural foundation for whether a multi-tenant analytics layer can be trusted, and any analytical system that bypasses it takes on the same exposure as an unprotected production query.

Application-layer filtering alone cannot guarantee tenant isolation in Supabase

Consider the most ordinary line of Supabase code a developer can write: supabase.from('orders').select('*'). If the table has no row-level access control enabled, that call returns every row in the table to anyone holding the project's anon key, no matter what filtering logic sits upstream in the application. The anon key identifies a Supabase project. It does not identify a user, and it carries no filtering logic of its own.

Supabase's architecture makes this especially consequential, because Postgres sits closer to the client than in a typical backend. Neither layer adds filtering on its own; both simply pass through whatever the database allows.

Application-layer guards, the kind built into Next.js middleware, tRPC procedures, or Express route checks, live in code that changes constantly. None of this requires carelessness; it requires only the ordinary churn of a codebase under active development. A bug in an API route that strips a WHERE clause from a query will, under application-only filtering, hand over every tenant's data at once. Under RLS, the same bug changes nothing, because the policy is evaluated by Postgres itself, independent of how the request arrived.

A row that fails the policy does not come back redacted or flagged, and does not exist as far as the requester can tell.

The strongest case against this architecture usually sounds something like: "We test thoroughly and review every query before it ships." That's a reasonable practice, but it answers a different question than the one RLS answers. Database enforcement doesn't compete with good engineering discipline. It covers the case where that discipline, like all human processes, eventually fails.

How RLS works at the Postgres level

Postgres enforces this row-level access control as a property of the table itself, not of the application that queries it. A policy is scoped to a specific table, to an operation (SELECT, INSERT, UPDATE, DELETE, or ALL), and optionally to a database role. Once RLS is turned on for a table, the default posture is deny: no row is returned, inserted, updated, or deleted unless some policy explicitly permits it.

A basic policy looks like this:

create policy "Users can view their own orders"
on orders for select
to authenticated
using (user_id = auth.uid());

Two clauses do different jobs inside a policy, and conflating them is one of the most common mistakes in early RLS design. The USING clause governs which existing rows a user can see or act on. For UPDATE statements, both clauses matter and do different work: USING stops a user from touching a row they don't own in the first place, while WITH CHECK stops that same user from reassigning a row to someone else, say, by changing a user_id column to another person's UUID after the update has already started.

Two built-in functions do most of the work inside these policies. auth.uid() returns the UUID of the authenticated user, pulled from the JWT, and returns null when no one is authenticated. auth.jwt() returns the entire JWT payload as JSON, which opens the door to custom claims like org_id or tenant_id set at login.

Policies combine in a specific, important way. By default, policies are permissive, and multiple permissive policies on the same table and operation combine with OR logic: if any one of them grants access to a row, the user gets access. Restrictive policies, created explicitly with AS RESTRICTIVE, combine with AND logic instead, and a row must satisfy every restrictive policy in addition to at least one permissive policy. Restrictive policies suit hard constraints that should never be overridden by any other rule, like a subscription-tier gate that blocks access regardless of ownership.

One credential sits outside this entire system: the service_role key bypasses every RLS policy on every table, unconditionally. The anon key, by contrast, creates a session under the anon role and respects every policy; a user's JWT creates an authenticated session that does the same. Everything built on top of RLS depends on keeping that one superuser key out of the browser.

The core multi-tenant isolation pattern: tenant_id columns, membership tables, and the JWT claim flow

Reliable multi-tenant isolation in Supabase rests on three pieces working together: a tenant_id or account_id foreign key on every shared table, a membership table mapping users to tenants, and RLS policies that check membership through that table rather than trusting any value the client sends along with a request.

A working schema looks like this:

create table accounts (
id uuid primary key default gen_random_uuid(),
name text not null
);

create table team_members (
user_id uuid references auth.users not null,
account_id uuid references accounts not null,
role text not null default 'member',
primary key (user_id, account_id)
);

create table issues (
id uuid primary key default gen_random_uuid(),
account_id uuid references accounts not null,
title text not null,
created_at timestamptz default now()
);

The SELECT policy on issues checks membership rather than any client-supplied field:

create policy "Members can view account issues"
on issues for select
to authenticated
using (
account_id in (
select account_id from team_members where user_id = auth.uid()
)
);

Any user who belongs to the account can read its rows. A single user_id column compared against auth.uid() works fine for data that belongs to one person, but B2B software where several people share one tenant's data needs the account-level membership check instead; an issue-tracking product, where every table carries an account_id and every query is scoped to the right account through this membership pattern, is a representative case of the shape this takes in production.

An alternative path sets custom claims, org_id or tenant_id, directly into the JWT through Supabase Auth hooks or Edge Functions at login. Policies can then compare a column directly against the claim:

using (account_id = (auth.jwt() ->> 'org_id')::uuid)

Whichever path a team chooses, inserts need the same scrutiny as reads. Never trust the application to attach the correct user_id or account_id to a new row. Enforce it with a WITH CHECK clause so Postgres rejects any insert where the submitted value doesn't match the authenticated user's own context, regardless of what the client sent.

Joined tables deserve a separate note, because the failure mode they produce is quiet. When a query joins two RLS-enabled tables, Postgres checks each table's policy independently. Each table in a join needs its own policy, stated explicitly, with no assumption that one table's policy extends coverage to another.

RLS governs which rows a user can touch, not which columns within those rows. A field like primary_owner_user_id, which should never change from client code under any circumstance, needs column-level GRANT/REVOKE controls layered on top of row-level policy, since RLS alone won't stop a legitimate row-owner from modifying a column they shouldn't.

Performance at scale: fixing the cost without weakening isolation

RLS does carry a real performance cost at scale, but that cost traces almost entirely to two specific, fixable mistakes: running a membership subquery for every row Postgres evaluates, and missing indexes on the columns a policy actually checks. Neither problem requires loosening isolation to solve.

The subquery pattern shown earlier, account_id in (select account_id from team_members where user_id = auth.uid()), can execute once per row on a large table, which turns into real CPU pressure once a table grows past a few hundred thousand rows.

The first fix wraps auth.uid() in its own SELECT, so Postgres evaluates it once per statement and caches the result instead of recomputing it per row:

using ((select auth.uid()) = user_id)

This single change is, by a wide margin, the optimization most developers overlook first, and it costs nothing in terms of isolation guarantees.

The second fix wraps more complex membership lookups in a function marked STABLE and SECURITY DEFINER. Marking a function STABLE tells the Postgres planner that the function returns the same result for the same arguments within a single statement, which lets the optimizer collapse what would otherwise be many separate calls into one. SECURITY DEFINER lets that function read the membership table under its own elevated privilege, bypassing RLS only on that internal lookup, while the outer policy on the actual data table still enforces the tenant boundary. Any function built this way should also set search_path = public explicitly, to close off a known class of search-path injection attack against SECURITY DEFINER functions.

Two further fixes round out this pattern. And every policy should name its target role explicitly with to authenticated, so Postgres skips policy evaluation entirely for anonymous requests that were never going to match anyway. EXPLAIN ANALYZE is the right tool for finding what's still slow: a sequential scan on a large table is the signal that an index is missing somewhere a policy depends on it.

The objection that follows naturally is that even a well-optimized RLS setup adds overhead a lean team can't afford. But application-layer filtering at the same scale requires the identical membership lookups on every single query. The same lookups move into application code instead, where the Postgres query planner can't optimize them, and where one missed code path removes the protection.

Testing RLS policies correctly and the debugging traps that give false confidence

The most dangerous state a multi-tenant Supabase application can be in is a policy that looks correct in development and fails silently in production, and the single most common cause is testing against a tool or credential that bypasses RLS without anyone realizing it.

The Supabase SQL Editor runs as a superuser and ignores RLS. A query run there will return every row in a table regardless of policy, and a developer who checks their isolation logic this way will see exactly the data they expect, even if the actual policy is broken or missing. That success reflects the superuser connection, not the policy. Testing needs to happen through the client SDK, using the anon key paired with a real user's JWT, because that's the only path that actually runs the policy the way a production request would.

A repeatable test pattern exists without needing application code at all: open a transaction, set role = 'authenticated' and request.jwt.claims to a specific user's UUID, run the query, then roll the transaction back. This gives a role-correct result every time without touching a frontend or a staging deployment.

The negative case matters as much as the positive one, and it's the one teams skip most often. Log in as a user from one tenant and confirm that another tenant's rows come back as an empty result, not an error. RLS returns empty sets rather than access-denied responses, so a missing or broken policy looks identical to "there's no data here" rather than throwing any kind of visible failure. That silence is why the negative test has to be run deliberately rather than inferred from the absence of errors elsewhere.

The join-table trap from earlier applies in testing too: a policy on one table in a join does not protect another table in that same join, so each table needs to be tested on its own. And the service_role footgun appears here in a different form: if any part of a test's setup, even just seeding test data, uses the service_role key, that test bypasses RLS and proves nothing about whether policies actually work. Admin operations like seeding should stay clearly separated from the user-level operations being tested. Finally, every UPDATE policy needs both its USING and its WITH CHECK clause written and tested separately: USING keeps a user from touching rows they don't own, while WITH CHECK keeps them from reassigning ownership of a row they do own to someone else. A policy missing WITH CHECK leaves data readable and protected, but still transferable across tenant boundaries on write.

Analytical layers that bypass RLS inherit the full blast radius of unprotected production queries

Everything above establishes what a correctly isolated Supabase application looks like at the transactional layer. An analytical layer built on top of that same database inherits every one of those guarantees, or loses all of them, depending on how it connects.

When an analytical tool queries a Supabase Postgres database through the service_role key, or through any privileged connection that bypasses RLS, it doesn't gain analytical capability it couldn't otherwise have. The service_role key looks like the obvious solution for a tool that needs to aggregate data across every tenant at once, but that convenience turns the analytics layer into a single "God User": one credential whose exposure, misuse, or simple misconfiguration leaks every tenant's data at the same time, with no row-level boundary left standing in the way.

The operational risk compounds the security risk. Moving analytical queries to a dedicated read replica addresses the resource contention, but it does nothing for isolation on its own: a read replica queried with a service_role credential still exposes every tenant's data to anyone or anything that reaches it.

AI agents sharpen this risk further. A coding agent's deletion of a production database at Replit stands as the clearest cautionary case of this pattern: any MCP server or agent holding a connection string with elevated privileges can carry out destructive operations at the full scope of those privileges, with no structural check in between. Operational caution, code review, and careful prompting all help, but none of them are the structural answer to that exposure. Tenant-scoped RLS, enforced at the database and respected by every layer that reads from it, including the analytical ones, is.