Mastering Supabase Auth and Row Level Security for Multi-Tenant SaaS

If you have ever experienced the 3 AM panic of realizing Tenant A might have just accessed Tenant B's data, you know the sheer terror of multi-tenant data leaks. When building SaaS products, application-level security—where your Node.js or Next.js backend checks if a user has access before querying the database—is a fragile illusion. A single missed WHERE clause or a forgotten middleware check can lead to a catastrophic breach.

This is where Supabase Auth combined with PostgreSQL Row Level Security (RLS) becomes your ultimate safety net. By pushing authorization down to the database layer, you ensure that no matter what happens in your application code, the database will never leak unauthorized data.

In this guide, we will dive deep into architecting a secure, high-performance multi-tenant SaaS using Supabase. We will cover robust data modeling, injecting custom claims into JWTs, and writing database-level tests for your RLS policies.

Architecture: How Supabase Auth Talks to Postgres

Before writing code, we must understand the flow. When a user logs in via Supabase Auth, they receive a JSON Web Token (JWT). When your frontend makes a request to the Postgres database (via the Supabase client or PostgREST), this JWT is passed in the Authorization header.

PostgreSQL intercepts this request. Through a custom GUC (Grand Unified Configuration) variable, Postgres exposes the decoded JWT claims to your RLS policies via the auth.jwt() function. Your RLS policies then evaluate these claims against the row data to determine access.

This means your database is entirely self-sufficient for authorization. It does not need to call your application server to verify permissions.

Deep Dive 1: Designing a Bulletproof RLS Data Model

A common mistake in multi-tenant SaaS is tying permissions directly to the user. In reality, users belong to organizations (tenants), and their permissions are scoped to that organization.

Let's design a schema that enforces this relationship strictly.

-- 1. Create the organizations table
CREATE TABLE public.organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 2. Create a mapping table for users and organizations
CREATE TABLE public.org_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID REFERENCES public.organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  UNIQUE(org_id, user_id)
);

-- 3. Create a tenant-scoped resource table
CREATE TABLE public.documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 4. Enable RLS on all tables
ALTER TABLE public.organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;

Now, let's write the RLS policies. The golden rule of RLS is to always filter by the tenant ID (org_id) and verify the user's membership.

-- Policy for documents: Users can only see documents in their organizations
CREATE POLICY "Users can view documents in their orgs" 
ON public.documents 
FOR SELECT 
USING (
  org_id IN (
    SELECT org_id FROM public.org_members WHERE user_id = auth.uid()
  )
);

-- Policy for documents: Only admins and owners can update
CREATE POLICY "Admins and owners can update documents" 
ON public.documents 
FOR UPDATE 
USING (
  org_id IN (
    SELECT org_id FROM public.org_members 
    WHERE user_id = auth.uid() AND role IN ('admin', 'owner')
  )
);

-- Policy for org_members: Users can see their own memberships
CREATE POLICY "Users can view their own memberships" 
ON public.org_members 
FOR SELECT 
USING (user_id = auth.uid());

Notice that we didn't just check auth.uid(). We joined against org_members to ensure the user actually belongs to the organization that owns the document. This is the core of multi-tenant isolation.

Deep Dive 2: Injecting Custom Claims via Supabase Auth Hooks

While the subquery approach above works, running a SELECT inside an RLS policy for every single row can become a performance bottleneck at scale. Furthermore, what if you need to enforce role-based access control (RBAC) where the user's role is checked frequently?

Instead of querying the database on every request, we can inject the user's active org_id and role directly into the JWT as custom claims using Supabase Auth Hooks.

Supabase Auth Hooks allow you to intercept the token creation process. We will write a Deno Edge Function to fetch the user's primary organization and inject it into the JWT.

// supabase/functions/auth-hook/index.ts
import { serve } from 'https://deno.land/std@0.168.0/http/server.ts'
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2'

serve(async (req) => {
  const { user, auth_event } = await req.json()

  // Initialize admin client to bypass RLS for reading user data
  const supabaseAdmin = createClient(
    Deno.env.get('SUPABASE_URL') ?? '',
    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') ?? ''
  )

  let customClaims = {}

  if (auth_event.action === 'token_refreshed' || auth_event.action === 'signup') {
    // Fetch the user's primary organization and role
    const { data: membership } = await supabaseAdmin
      .from('org_members')
      .select('org_id, role')
      .eq('user_id', user.id)
      .limit(1)
      .single()

    if (membership) {
      customClaims = {
        org_id: membership.org_id,
        role: membership.role
      }
    }
  }

  return {
    claims: customClaims
  }
})

Once this hook is deployed and configured in your Supabase dashboard, every JWT issued to the user will contain org_id and role. Your RLS policies become incredibly simple and blazing fast:

CREATE POLICY "Fast document view policy" 
ON public.documents 
FOR SELECT 
USING (org_id = (auth.jwt() ->> 'org_id')::UUID);

By moving the tenant resolution to the JWT, we eliminate database joins inside the RLS policy, drastically reducing query execution time.

Deep Dive 3: Testing RLS Policies Without the API

One of the biggest pitfalls developers face with RLS is assuming it works because the API returns the correct data. But what if a developer writes a raw SQL query in a migration script? What if a new policy inadvertently opens up data?

You must test RLS at the database level. Fortunately, Postgres allows us to mock the auth.jwt() context using set_config.

Here is a robust testing strategy using raw SQL. You can run this in your Supabase SQL Editor or integrate it into your CI/CD pipeline.

-- 1. Create a test user in auth.users (if not using a test framework that handles this)
-- For this example, assume we have a user with ID '11111111-1111-1111-1111-111111111111'

-- 2. Mock the JWT for User A (Org 1, Admin)
SELECT set_config('request.jwt.claims', '{"sub": "11111111-1111-1111-1111-111111111111", "org_id": "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa", "role": "admin"}', true);

-- 3. Attempt to select documents
-- This should return documents for Org A, but NOT Org B
SELECT id, org_id, title FROM public.documents;

-- 4. Assert the results (in a real test runner, you would check the row count)
-- If this returns rows with org_id != 'aaaaaaaa...', your RLS is broken.

-- 5. Now, switch context to User B (Org 2, Member)
SELECT set_config('request.jwt.claims', '{"sub": "22222222-2222-2222-2222-222222222222", "org_id": "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb", "role": "member"}', true);

-- 6. Attempt an UPDATE on Org A's document
-- This should fail with a policy violation or return 0 rows updated
UPDATE public.documents SET title = 'Hacked' WHERE org_id = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa';

-- 7. Reset the context after testing
SELECT set_config('request.jwt.claims', '', true);

This pattern allows you to write comprehensive integration tests for your security policies without needing to spin up the entire Supabase stack or mock HTTP requests. It tests the exact boundary where data leaks actually happen.

Performance Considerations and Common Pitfalls

While RLS is powerful, it introduces specific performance characteristics that mid-to-senior developers must navigate.

1. The Indexing Trap

RLS policies are essentially WHERE clauses appended to every query. If your policy filters by org_id, you must have an index on org_id. Without it, Postgres will perform a sequential scan on every table, bringing your database to its knees as data grows.

-- Always index your RLS filter columns!
CREATE INDEX idx_documents_org_id ON public.documents(org_id);
CREATE INDEX idx_org_members_user_id ON public.org_members(user_id);

2. The security definer Bypass

If you create a Postgres function with SECURITY DEFINER, it executes with the privileges of the user who created the function (usually postgres), completely bypassing RLS. If you must use SECURITY DEFINER (e.g., for a function that inserts into auth.users), ensure you explicitly set the search_path and manually re-apply RLS checks inside the function, or restrict its usage strictly.

3. JWT Bloat and Caching

If you inject too many custom claims into the JWT, the token size grows. Since the JWT is sent in the header of every request, bloated tokens increase latency and bandwidth. Furthermore, remember that JWTs are cached. If a user's role changes from 'member' to 'admin', the change won't reflect until their token expires or is refreshed. Always implement a mechanism to force token refresh when critical permissions change.

Key Takeaways

1. Never trust application-level security for multi-tenant data. Push authorization down to the database using RLS.
2. Design your schema around tenants. Use mapping tables (org_members) to link users to organizations, rather than assigning resources directly to users.
3. Optimize RLS with Custom Claims. Use Supabase Auth Hooks to inject tenant IDs and roles into the JWT, avoiding expensive joins inside your RLS policies.
4. Test at the database layer. Use set_config to mock JWTs and write SQL-based tests for your RLS policies. API tests are not enough.
5. Index your filter columns. RLS policies without corresponding database indexes will destroy your query performance.

When I was architecting the multi-tenant isolation for PubliFlow (publiflow.vip), a Next.js 15 SaaS starter kit I recently built, I found that relying solely on API-level tests was a massive blind spot. Implementing these exact SQL-based RLS testing patterns at the database level saved us from a critical data leak during load testing, proving that database-enforced security is the only true safety net. Building a SaaS is hard enough; let PostgreSQL do the heavy lifting for your security.

Comments

[Cenk KURTOĞLU]

Good article, and the testing section is the part most guides skip. Two things I would add, one of which is a concrete bug in the schema as published.

The UPDATE policy has no WITH CHECK, so it permits cross-tenant writes.

CREATE POLICY "Admins and owners can update documents"
ON public.documents FOR UPDATE
USING (
  org_id IN (SELECT org_id FROM public.org_members
             WHERE user_id = auth.uid() AND role IN ('admin','owner'))
);

For UPDATE, USING is checked against the row as it exists, and WITH CHECK against the row as it will be. With no WITH CHECK, Postgres falls back to using the USING expression for reads only — the new values are never validated. So an admin of Org A can run:

update public.documents set org_id = '<org B uuid>' where id = '<their own doc>';

USING passes, because at evaluation time the row still belongs to Org A. The row lands in Org B. In a multi-tenant app that is a write across the boundary the whole post is about, and your test at step 6 will not catch it — it tries to update someone else's row, which USING correctly blocks. The dangerous direction is updating your own row into someone else's tenant.

-- add the second half
CREATE POLICY "Admins and owners can update documents"
ON public.documents FOR UPDATE TO authenticated
USING      ( org_id IN (select org_id from public.org_members
                        where user_id = (select auth.uid()) and role in ('admin','owner')) )
WITH CHECK ( org_id IN (select org_id from public.org_members
                        where user_id = (select auth.uid()) and role in ('admin','owner')) );

Second: the JWT custom-claims optimization is also a revocation change, not only a performance change.

You note that a role change does not appear until the token refreshes. The direction worth calling out is removal. Once the policy reads org_id = (auth.jwt() ->> 'org_id')::UUID, deleting someone from org_members no longer removes their access — their existing token still asserts the org, and it keeps working until expiry. With the join-based policy, revocation was immediate because every query re-read the membership table. That is a real trade, and for offboarding it is the one that matters.

You may not have to make that trade. Most of the join cost comes from auth.uid() being re-evaluated per row. Wrapping it in a scalar subquery turns it into an InitPlan that runs once per statement:

-- per row
using ( org_id in (select org_id from org_members where user_id = auth.uid()) )
-- once per statement
using ( org_id in (select org_id from org_members where user_id = (select auth.uid())) )

Supabase's own benchmarks put that at 179ms to 9ms for plain auth.uid(), and 11,000ms to 7ms when a security definer helper is involved. Combined with the indexes you already recommend, the join usually stops being the bottleneck — and you keep instant revocation.

If you do keep JWT claims, short token lifetimes plus a forced refresh on membership change is the mitigation, and it is worth writing down next to the pattern so nobody adopts it without seeing the cost.

Same failure class from the read side, if it is useful — the test suite goes green either way: github.com/cekuu35/supabase-rls-le...

[PubliFlow]

Glad the testing section resonated, and excellent catch on the missing WITH CHECK clause in the UPDATE policy. Without it, a user could technically update a row's org_id to a different tenant they don't own, since USING only validates the existing row. I will patch the schema to include the WITH CHECK constraint to properly lock down the write phase. Have you found any other edge cases when testing RLS policies with Postgres triggers?

[Cenk KURTOĞLU]

Triggers and RLS interact in one place that bites hard, and it is mostly about who the function runs as.

SECURITY DEFINER trigger functions do not see RLS. The common audit pattern is exactly this shape:

create function log_change() returns trigger
language plpgsql security definer as $$
begin
  insert into audit_log(table_name, row_id, actor) values (TG_TABLE_NAME, NEW.id, auth.uid());
  return NEW;
end $$;

That insert runs as the function owner, so any policy on audit_log is bypassed. Usually intended — you want the log written even when the actor could not read the log. Worth being deliberate about, because the same property means a SECURITY DEFINER trigger that reads another table reads it unfiltered, and it is easy to build a lookup helper that way without noticing it has become a hole in the tenant boundary.

Two things I would pin on any such function:

alter function log_change() set search_path = '';

An unpinned search_path on a SECURITY DEFINER function is the classic escalation path — the caller controls resolution and can shadow a table or operator you meant to reference. And keep them tiny: the smaller the definer function, the smaller the thing running with elevated rights.

On testing specifically, the failure mode is quieter than a wrong result.

If RLS filters a row out, your UPDATE touches zero rows, so the trigger never fires. A test asserting "the audit row was not written for another tenant" then passes for the wrong reason — not because the trigger declined, but because nothing happened at all. It would pass identically against a table with no trigger, or with the trigger dropped.

So for every negative assertion, add the positive one on the same path: user A updates their own row, and the audit row is written with the right actor. If both hold, the mechanism is real. If only the negative holds, you have tested silence.

And the one that invalidates whole suites: run the tests as the role your app actually connects as. As owner you bypass RLS entirely unless the table has force row level security, so an owner-run suite exercises a path production never takes — triggers included, since the rows it can see are not the rows the app can see.

alter table public.documents force row level security;

Also worth knowing while testing: session_replication_role = 'replica' disables normal triggers, and it is settable by a superuser or the owner. If a seed or migration script sets it and does not reset it, triggers silently stop firing for the rest of that session — which looks exactly like a trigger that decided not to run.

[PubliFlow]

You nailed the exact edge case that catches everyone off guard with Supabase RLS. Relying on SECURITY DEFINER for audit logs is the standard workaround, but it always makes me nervous about ensuring the search_path is strictly locked down to prevent privilege escalation. Have you found a clean way to dynamically pass the original authenticated user's ID into the audit payload without relying on custom GUC variables?

[Cenk KURTOĞLU]

Correction to my own comment above, and it matters because I stated it as a concrete bug when it is not one.

The missing WITH CHECK on your UPDATE policy is not a vulnerability. I was wrong about that.

PostgreSQL's CREATE POLICY documentation is explicit: for ALL and UPDATE policies, when no WITH CHECK expression is defined, the USING expression is used both to determine which rows are visible and which new rows are allowed. So the attack I described does not work. An admin of Org A running:

update public.documents set org_id = '<org B uuid>' where id = '<their own doc>';

has the USING expression evaluated against the new row as well. <org B> in (orgs where I am admin) is false, and Postgres rejects it with new row violates row-level security policy. Your schema is fine on that point.

Where the missing WITH CHECK genuinely does bite is when USING is broader than the write predicate should be — using (true), or a read policy like using (is_public or org_id in (...)) where the read side is deliberately wide. Then the fallback grants writes matching that wider expression. That is not your case.

Writing WITH CHECK explicitly is still worth doing, but as documentation of intent, not as a fix — and I should have framed it that way instead of inventing an exploit.

Everything in the second half of my comment stands: the JWT custom-claims pattern does change revocation semantics, and the (select auth.uid()) wrapping does get most of the join performance back without that trade. Sorry for the noise on the first part.

[PubliFlow]

It takes maturity to correct yourself publicly, and I appreciate the clarification on the PostgreSQL default behavior. The fallback to the USING expression is definitely a subtle RLS detail that trips up a lot of developers building multi-tenant apps. While relying on it is perfectly safe for tenant isolation, explicitly defining both clauses is still a solid habit for long-term code readability.