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

Building a SaaS application is straightforward until you hit the multi-tenant data isolation wall. Supabase makes this look trivial by combining its managed Authentication service with PostgreSQL’s native Row Level Security (RLS). However, naive implementations often lead to N+1 query nightmares, silent security leaks, and JWT payload bloat.

If you are building a B2B SaaS, your database is your last line of defense. API middleware can be bypassed, client-side checks can be ignored, but RLS is enforced at the storage engine level. In this deep dive, we will explore how to properly architect Supabase Auth with RLS for a multi-tenant environment, manage custom JWT claims, and rigorously test your policies.

The Architecture: Bridging Auth and Postgres

Before writing policies, we must understand the request lifecycle. When a user logs in, Supabase Auth issues a JSON Web Token (JWT). This JWT is sent to your application and subsequently to the Supabase Postgres instance via the Authorization Bearer header.

Inside Postgres, the auth.jwt() function parses this token. By default, it only contains the user's ID (sub), email, and role. For a multi-tenant SaaS, this is insufficient. You need to know which tenant_id the user is currently acting on behalf of, and what their specific role is within that tenant.

Deep Dive 1: Injecting Custom Claims into the JWT

A common mistake is trying to override the internal auth.jwt() function or using heavy Auth Edge Functions for every request. The most robust, performant, and officially supported way to add custom claims (like tenant_id) to a Supabase JWT is by syncing data to the raw_user_meta_data column in the auth.users table. Supabase Auth automatically includes this metadata in the generated JWT.

Here is a production-ready pattern using a database trigger to keep the JWT in sync when a user's tenant membership changes.

Code Example 1: Syncing Tenant Context to JWT via Trigger

-- 1. Create a function to sync tenant_id and role to user metadata
CREATE OR REPLACE FUNCTION public.sync_user_tenant_metadata()
RETURNS TRIGGER AS $$
BEGIN
  -- Update the auth.users table with the new tenant context
  UPDATE auth.users
  SET raw_user_meta_data = jsonb_build_object(
    'tenant_id', NEW.tenant_id,
    'role', NEW.role,
    'email', (SELECT email FROM auth.users WHERE id = NEW.user_id)
  )
  WHERE id = NEW.user_id;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Attach the trigger to your tenant membership table
-- Assuming you have a table: public.tenant_members (user_id, tenant_id, role, is_active)
CREATE TRIGGER on_tenant_member_change
AFTER INSERT OR UPDATE ON public.tenant_members
FOR EACH ROW
WHEN (NEW.is_active = true)
EXECUTE FUNCTION public.sync_user_tenant_metadata();

Why this works: When a user switches workspaces (tenants) in your frontend, you update their active tenant_id in tenant_members. The trigger fires, updating raw_user_meta_data. The frontend then calls supabase.auth.refreshSession(), fetching a fresh JWT that now contains the new tenant_id.

Deep Dive 2: Writing Bulletproof RLS Policies

Now that our JWT contains the tenant_id, we can write RLS policies. A critical best practice is to avoid scattering auth.jwt() ->> 'tenant_id' across dozens of policies. Instead, create a helper function. This centralizes your logic, makes policies readable, and allows you to add caching or complex fallback logic later.

Code Example 2: Centralized RLS Helper and Policies

-- 1. Create a STABLE helper function to extract the tenant ID
-- Marking it STABLE tells the Postgres query planner it can be optimized
CREATE OR REPLACE FUNCTION public.get_current_tenant_id()
RETURNS UUID AS $$
  SELECT NULLIF(auth.jwt() ->> 'tenant_id', '')::UUID;
$$ LANGUAGE sql STABLE;

-- 2. Enable RLS on your tenant-scoped tables
ALTER TABLE public.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- 3. Create comprehensive policies
-- USING controls SELECT, UPDATE, DELETE
-- WITH CHECK controls INSERT, UPDATE
CREATE POLICY tenant_isolation_documents
ON public.documents
FOR ALL
USING (tenant_id = public.get_current_tenant_id())
WITH CHECK (tenant_id = public.get_current_tenant_id());

CREATE POLICY tenant_isolation_projects
ON public.projects
FOR ALL
USING (tenant_id = public.get_current_tenant_id())
WITH CHECK (tenant_id = public.get_current_tenant_id());

The USING vs WITH CHECK Trap: Notice that we defined both. If you only define USING, a user can UPDATE a row to change its tenant_id to a different tenant, effectively stealing data. WITH CHECK ensures that the result of an INSERT or UPDATE still complies with the policy.

Deep Dive 3: Testing RLS Strategies

Testing RLS via your REST API or GraphQL endpoint is slow and conflates application logic with database security. You must test RLS directly at the database layer. Postgres allows you to simulate authenticated sessions using set_config.

Code Example 3: Direct Database RLS Testing

-- A robust testing block for your CI/CD pipeline or local dev
DO $$
DECLARE
  mock_user_id UUID := '00000000-0000-0000-0000-000000000001';
  mock_tenant_id UUID := '11111111-1111-1111-1111-111111111111';
  hostile_tenant_id UUID := '22222222-2222-2222-2222-222222222222';
  doc_count INT;
BEGIN
  -- 1. Simulate the JWT claims for our mock user
  PERFORM set_config('request.jwt.claim.sub', mock_user_id::TEXT, TRUE);
  PERFORM set_config('request.jwt.claim.tenant_id', mock_tenant_id::TEXT, TRUE);

  -- 2. Test SELECT isolation
  SELECT COUNT(*) INTO doc_count FROM public.documents;
  -- In a real test framework (like pgTAP), you would assert this count
  -- matches exactly the documents belonging to mock_tenant_id.
  RAISE NOTICE 'User can see % documents for their tenant.', doc_count;

  -- 3. Test INSERT isolation (The Cross-Tenant Attack)
  BEGIN
    INSERT INTO public.documents (tenant_id, title)
    VALUES (hostile_tenant_id, 'Data Exfiltration Attempt');

    -- If we reach here, RLS failed!
    RAISE EXCEPTION 'CRITICAL: RLS failed to block cross-tenant insert!';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'SUCCESS: Blocked cross-tenant insert.';
  END;

  -- 4. Test UPDATE isolation
  BEGIN
    UPDATE public.documents
    SET tenant_id = hostile_tenant_id
    WHERE tenant_id = mock_tenant_id;

    RAISE EXCEPTION 'CRITICAL: RLS failed to block cross-tenant update!';
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'SUCCESS: Blocked cross-tenant update.';
  END;

  -- 5. Clean up session context
  PERFORM set_config('request.jwt.claim.sub', '', TRUE);
  PERFORM set_config('request.jwt.claim.tenant_id', '', TRUE);
END $$;

Performance Considerations

RLS policies can silently destroy your database performance if not written carefully.

1. Index Matching: Postgres can only use indexes if the RLS policy expression matches the index definition. If your policy uses tenant_id = get_current_tenant_id(), you must have a standard B-Tree index on tenant_id.
2. JWT Size Limits: JWTs have a hard limit of 4KB. Never store arrays of granular permissions in the JWT. Store the tenant_id and role, and resolve granular permissions via a fast, indexed database lookup or a lightweight Redis cache.
3. Function Volatility: Always mark your RLS helper functions as STABLE (as shown in Example 2). If you leave them as VOLATILE (the default), Postgres will execute the function for every single row evaluated, bypassing query planner optimizations.

Common Pitfalls to Avoid

• Forgetting to Enable RLS: It sounds obvious, but ALTER TABLE ... ENABLE ROW LEVEL SECURITY is easily forgotten in migrations. A table with policies but without RLS enabled is completely open.
• Caching JWTs Too Long: If you set your JWT expiry to 24 hours, a user removed from a tenant will still have database access for 24 hours. Keep JWT lifespans short (e.g., 1 hour) and use refresh tokens.
• Using SECURITY INVOKER on Helpers: If your get_current_tenant_id() function is SECURITY INVOKER, it runs with the permissions of the caller. If the caller doesn't have permission to read the auth.jwt() context properly in some edge cases, it fails. Use SECURITY DEFINER for helper functions that read auth contexts, but be extremely careful to validate inputs. (Note: For simple SQL functions reading auth.jwt(), LANGUAGE sql is usually sufficient and safe).

Key Takeaways

• Treat your database as the ultimate source of truth for security.
• Use database triggers on membership tables to sync tenant_id into raw_user_meta_data for clean JWT custom claims.
• Abstract JWT parsing into STABLE helper functions to keep policies clean and performant.
• Always define both USING and WITH CHECK in your RLS policies to prevent privilege escalation via updates.
• Test your RLS policies directly in Postgres using set_config to simulate JWTs, rather than relying solely on API tests.

When I was architecting the multi-tenant isolation for PubliFlow (a Next.js 15 SaaS Starter Kit I built, available at publiflow.vip), I initially tried stuffing all user permissions and workspace contexts directly into the JWT. It quickly led to token bloat and complex refresh logic. By shifting to the database trigger pattern for JWT claims and centralizing the RLS logic into STABLE helper functions, I reduced the auth overhead by over 60% and made the security model infinitely easier to reason about. Building SaaS is hard enough; let Postgres do the heavy lifting for your security.