Supabase RLS for Multi-Tenant AI: Closing the Default-Open Door

The problem

You build a table in Supabase. The schema is fine. Auth works. You add WHERE user_id = current_user to every query in the application and ship. Two weeks later somebody opens DevTools, pulls your anon key out of the JavaScript bundle, and queries the PostgREST endpoint directly with curl. Every row in every table comes back.

This is not a bug. It is the design. Supabase generates a PostgREST API automatically for every table, and the moment that table exists it is queryable from any client with the anon key — which lives in every browser bundle and is visible to anyone who opens the network tab. Without Row-Level Security enabled, every row in that table is publicly readable through the auto-generated API.

Application-level filtering does not close this door. Direct API access bypasses your application entirely. This walks through the RLS policies that close it, the multi-tenant isolation patterns that make user A invisible to user B at the database layer, and the dual-protocol access control that keeps AP2 mandates from leaking into ACP queries.

What most people get wrong

Mistake one: treating RLS as defense-in-depth. Row-Level Security is not a layer on top of application logic. It is the foundation beneath it. Application filtering is an optimization for reducing unnecessary data transfer. RLS is the enforcement mechanism that makes unauthorized access structurally impossible at the database level. Teams that treat RLS as “extra protection” tend to ship with it disabled in dev, forget to enable it in production, and discover the gap during a penetration test or worse.

Mistake two: enabling RLS without forcing it. ALTER TABLE ... ENABLE ROW LEVEL SECURITY flips the switch — but in Supabase the postgres role (which the service role uses for certain operations) is the table owner, and the table owner bypasses RLS by default. Without FORCE ROW LEVEL SECURITY you have policies that protect against client traffic and quietly let your own server-side code (and any code that holds the service role key) sail past them. Most teams discover this when they audit access patterns and find that the “secure” table has been read by background jobs that should never have touched it.

A third mistake worth flagging — policy composition with OR. Multiple policies of the same command type combine with OR, not AND. A single overly permissive admin policy can undermine every other restriction on the table. If your “admins read all” policy checks auth.jwt() ->> 'role' = 'admin' and your JWT claims are not properly validated through Auth hooks, any user who manipulates their token could gain admin access. The composition rule is the most common source of catastrophic RLS misses.

A working approach

Enable, force, deny by default. Every table gets the same two-command opening, no exceptions:

-- Enable RLS (blocks ALL access until policies are added)
ALTER TABLE public.intent_mandates
  ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (critical for Supabase)
ALTER TABLE public.intent_mandates
  FORCE ROW LEVEL SECURITY;

With RLS enabled and no policies, the table is completely inaccessible from the client. That is intentional. It forces explicit access rules before any traffic flows. Do not disable RLS to “fix” the problem — add the correct policies.

The basic user-isolation policy uses auth.uid() to read the authenticated user’s UUID from the JWT:

-- Users can only see their own mandates
CREATE POLICY "Users read own mandates"
ON public.intent_mandates
FOR SELECT
USING (
  user_id = auth.uid()
);

The USING clause is a boolean expression evaluated for every row — only rows where it returns true are visible. USING applies to SELECT, UPDATE, and DELETE. For inserts you need WITH CHECK:

-- Users can only create mandates for themselves
CREATE POLICY "Users create own mandates"
ON public.intent_mandates
FOR INSERT
WITH CHECK (
  user_id = auth.uid()
);

Without this, a malicious client could insert rows attributed to other users by changing the user_id field in the request body. WITH CHECK is evaluated against the proposed row after the operation.

UPDATE is the one that catches teams. It needs both clauses, and the reason matters:

-- Users can update their own mandates,
-- but cannot reassign them to another user
CREATE POLICY "Users update own mandates"
ON public.intent_mandates
FOR UPDATE
USING (
  user_id = auth.uid()     -- Can only see own rows
)
WITH CHECK (
  user_id = auth.uid()     -- Cannot change user_id
);

The USING clause determines which rows the user can attempt to modify. The WITH CHECK clause determines whether the modified row is valid. Omit WITH CHECK and PostgreSQL falls back to the USING expression — which is usually correct, but making it explicit prevents subtle bugs when the policy is edited later by someone who doesn’t remember the fallback rule.

Beyond auth.uid(), the auth.jwt() function reads the full token payload, enabling policies based on custom claims:

-- Only premium users can access premium features table
CREATE POLICY "Premium users access premium features"
ON public.premium_features
FOR SELECT
USING (
  (auth.jwt() -> 'app_metadata' ->> 'tier')
    IN ('premium', 'enterprise')
);

Custom claims are set through Supabase Auth hooks, not from the client. The hook runs server-side at token issuance time and enriches the JWT with values from your profiles table — tier, organization id, role, whatever the policy needs. Setting claims from the client side is the canonical way to ship a privilege-escalation vulnerability.

Indexes matter. RLS policies run on every query, and WHERE user_id = auth.uid() against a non-indexed column at scale will eat the database. Add a B-tree index on every column that appears in a USING or WITH CHECK expression. This is not optional once your tenant count crosses three figures.

Where this scales

The article above is the spine. The full book covers what the policy templates above don’t reach:

• Multi-tenant isolation models — user-level, organization-level, and role-based. Each model has its own policy shape, indexing strategy, and trade-off. Organization-level isolation needs a security-definer lookup function to keep policy evaluation O(1); the book includes the function.
• Protocol-specific access control — dual-protocol architectures like AP2 and ACP need policies that key off protocol_type and JSONB protocol_metadata so an ACP session cannot read AP2 mandate fields and vice versa. The cross-protocol policy chapter handles the merchant and agent access paths.
• The two-database strategy — separating consumer platforms from compliance-critical infrastructure. One project for soft.house (consumer + commerce); a second isolated project for trust.authority (identity, credentials, audit-grade requirements). Cross-database communication patterns, schema differences, and migration strategy for two projects.
• Ephemeral development workflow — PII never lands in dev databases because dev databases never persist. Seed files, OAuth testing without ephemeral auth, RLS coverage tests against fresh containers.
• RLS testing strategies — positive tests (authorized access works), negative tests (isolation holds), attack tests (escalation attempts fail), and the IDOR test suite that exercises every user_id-bearing column with a token from a different user.
• The production hardening checklist — audit logging at the database layer, PgBouncer connection pooling, backup security, disaster recovery, and the seven-section pre-launch sign-off that closes the gap from “RLS enabled” to “RLS audited.”

Included with the book

• rls-policy-templates.sql — battle-tested policy library covering the eight most common patterns: user owns their data, own data plus public read (marketplace listings), organization-scoped data, role-based access, time-windowed access, soft-delete with restore, public read plus owner write, and admin override with audit trail. Drop in, swap the table names, deploy.

Get the full picture

Readers of this also chose

Questions readers ask

Is RLS only a Supabase thing, or does this apply to vanilla Postgres?

The policies port. PostgreSQL has RLS since version 9.5; the CREATE POLICY syntax is the same. The Supabase-specific pieces are the helper functions: auth.uid() returns the authenticated user UUID from the JWT, auth.jwt() returns the full payload, and auth.role() returns the Supabase role. On vanilla Postgres you write your own equivalents using current_setting('request.jwt.claim.sub') or similar, depending on your auth path.

Will RLS slow down my queries at scale?

It can if you skip indexes. Every USING expression runs per row, so WHERE user_id = auth.uid() against a non-indexed user_id column linearly degrades. Index every column that appears in a policy expression. Organization-level isolation needs a SECURITY DEFINER function to memoize the org lookup — the book includes the pattern.

What about the service role — doesn't that bypass RLS anyway?

Yes, by design. The service role key is meant for server-side code that needs full access (background jobs, admin endpoints, migrations). Treat it like a database password: never embed in client code, never log, never share. FORCE ROW LEVEL SECURITY is the safety net that keeps the table owner from accidentally bypassing policies during ordinary server operations — the service role still bypasses, but the gap is intentional and contained.

How do I test RLS policies without manually creating ten user accounts?

Two paths. First, write SQL tests against the database role-switching mechanism — SET LOCAL role TO authenticated; SET LOCAL request.jwt.claim.sub TO '<user-uuid>' — to simulate a user without going through the auth API. Second, run the test suite against ephemeral containers seeded with fixed UUIDs, which is what the ephemeral development workflow chapter walks through. Both are in the book.

What's the refund policy?

Lemon Squeezy's standard refund window applies. If the patterns don't fit your schema, the refund link is in the receipt email.