Chris de Gruijter Back to Home

Article Information

Category: Development

Published: 14 March, 2026

Author: Chris de Gruijter

Reading Time: 11 min

Tags

SupabasePostgreSQLRLSmulti-tenantSaaSsecurityauthentication
Secure database architecture visualization with multiple data layers

Multi-Tenant SaaS with Supabase: How Row Level Security Replaced My Custom Auth Middleware

Published: 14 March, 2026

When I started building the Webfluentia dashboard, my first instinct was to handle data isolation in application code. Check the user's role, filter queries by client ID, add middleware to every API route. It worked, but it was fragile — one missing filter and a client could see another client's data.

Then I discovered Supabase Row Level Security (RLS), and it changed how I think about multi-tenant data isolation entirely. Instead of trusting application code to filter data correctly, the database itself enforces who can see what. Even if my code has a bug, the wrong data never leaves PostgreSQL. Here's how I implemented it and what I learned.

The Problem: Multi-Tenancy is Hard to Get Right

Multi-tenancy means multiple customers (tenants) share the same application and database. The critical requirement is data isolation — Client A must never see Client B's data, even if a developer makes a mistake in the query logic.

There are three common approaches:

  1. Separate databases per tenant — bulletproof isolation but expensive and hard to maintain at scale
  2. Separate schemas per tenant — good isolation but migrations become complex (run on every schema)
  3. Shared tables with a tenant ID column — simplest to maintain but requires careful access control

I use approach 3 — shared tables with a client_id column. It's the easiest to maintain and scale. The challenge is ensuring that every query, in every API route, always filters by client_id. That's where RLS comes in.

What is Row Level Security?

Row Level Security (RLS) is a PostgreSQL feature that lets you define policies controlling which rows a user can see or modify. When RLS is enabled on a table, every query is automatically filtered by the policy — even if the application code doesn't include a WHERE clause.

In Supabase, RLS integrates with the auth system. When a user authenticates, their JWT contains their user ID. RLS policies can reference this via auth.uid() to filter rows based on the authenticated user. The flow looks like this:

  1. User logs in → Supabase issues a JWT with their user_id
  2. Browser makes a query using the Supabase client (which includes the JWT)
  3. PostgreSQL checks RLS policies before returning rows
  4. Only rows matching the policy are returned — everything else is invisible

My Database Schema

The dashboard uses three key tables for access control:

1. profiles — Who is this user?

CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  role TEXT NOT NULL DEFAULT 'client' 
    CHECK (role IN ('admin', 'client')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Every authenticated user has a profile. The role field determines whether they're an admin (sees everything) or a client (sees only assigned data). Profiles are auto-created by a trigger when a new auth user is inserted.

2. user_client_access — What can they see?

CREATE TABLE public.user_client_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  client_id TEXT REFERENCES client_configs(id),
  allowed_sections TEXT[] DEFAULT '{seo,leads}',
  UNIQUE(user_id, client_id)
);

This is the junction table that maps users to clients. A user can have access to multiple clients, and each mapping includes an allowed_sections array controlling which dashboard sections they can see (SEO, leads, analytics, CRM, etc.).

3. client_configs — Client metadata and credentials

CREATE TABLE public.client_configs (
  id TEXT PRIMARY KEY,        -- 'gevelpro', 'dgrenovaties', 'overbeek'
  name TEXT NOT NULL,
  domain TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT 'blue',
  r2_access_key TEXT,         -- sensitive, never exposed to browser
  r2_secret_key TEXT,         -- sensitive, never exposed to browser
  r2_bucket_name TEXT
);

The RLS Policies

Here's where it gets interesting. Each table has RLS policies that enforce data isolation at the database level.

Profile Access: Users Can Only Read Their Own Profile

ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Users can read their own profile
CREATE POLICY "Users can read own profile"
  ON public.profiles FOR SELECT
  USING (auth.uid() = id);

-- Admins can read all profiles
CREATE POLICY "Admins can read all profiles"
  ON public.profiles FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles p
      WHERE p.id = auth.uid() AND p.role = 'admin'
    )
  );

Client Configs: Only Assigned Users Can See Client Details

ALTER TABLE public.client_configs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read assigned client configs"
  ON public.client_configs FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.profiles p
      WHERE p.id = auth.uid()
      AND (
        p.role = 'admin' 
        OR id IN (
          SELECT client_id FROM public.user_client_access
          WHERE user_id = auth.uid()
        )
      )
    )
  );

This policy ensures that a client user can only see the configs for clients they're assigned to. Admins bypass this and see everything. The R2 credentials in client_configs are only accessed server-side using the service role key, which bypasses RLS entirely.

SEO Data: Filtered by client_id Through User Access

All SEO tables (keyword_tracking, ranking_history, seo_tasks, etc.) have a client_id column. The RLS policy for each table follows the same pattern:

-- Example for keyword_tracking (same pattern for all SEO tables)
ALTER TABLE seo.keyword_tracking ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read assigned client keywords"
  ON seo.keyword_tracking FOR SELECT
  USING (
    client_id IN (
      SELECT client_id FROM public.user_client_access
      WHERE user_id = auth.uid()
    )
    OR EXISTS (
      SELECT 1 FROM public.profiles
      WHERE id = auth.uid() AND role = 'admin'
    )
  );

Even if my application code forgets to filter by client_id, the database will only return rows for clients the user has access to. This is the fundamental advantage of RLS over application-level filtering.

The Server-Side Pattern: Service Role for Sensitive Data

Not everything should go through RLS. Some operations need unrestricted database access:

  • Reading R2 credentials — stored in client_configs but too sensitive for browser-side RLS
  • Agent data writes — Cloudflare Workers write SEO data without a user session
  • Admin operations — creating user accounts, managing access grants

For these, I use the Supabase service role key in server-side API routes. The service role key bypasses all RLS policies, giving full database access. This key is never exposed to the browser — it lives in server-side environment variables only.

// Server-side only — API route
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY! // bypasses RLS
);

// This reads ALL client configs, including R2 secrets
const { data } = await supabase
  .from('client_configs')
  .select('*')
  .eq('id', clientId);

Lessons Learned

1. Enable RLS on Every Table from Day One

I made the mistake of building features first and adding RLS later. This meant a period where the database was unprotected — any authenticated user could query any table without restrictions. Enable RLS immediately when creating a table, even if your initial policy is permissive. You can tighten it later.

2. Test RLS Policies with Multiple User Roles

I test every RLS policy by logging in as three different users: an admin, a client with access to one client, and a client with access to multiple clients. The most common bug is a policy that's too restrictive (blocking legitimate access) or too permissive (leaking data across tenants).

3. Watch Out for Recursive Policy References

An RLS policy on profiles that queries profiles (to check if the user is an admin) can cause infinite recursion in some configurations. Supabase handles this by using the SECURITY DEFINER context for the inner query, but it's something to be aware of. Test thoroughly.

4. Use Supabase Advisors to Catch Missing Policies

Supabase has a built-in security advisor that scans your database for tables without RLS enabled, policies with potential issues, and other security gaps. I run this after every schema migration to catch anything I missed. It's caught two tables I forgot to add RLS to — tables that would have been publicly readable.

RLS vs Application-Level Filtering: Why Not Both?

In practice, I use both. RLS is the safety net — it guarantees data isolation regardless of what the application code does. Application-level filtering (adding .eq('client_id', selectedClient) to queries) provides the correct UX — showing data for the currently selected client in the dashboard UI.

Think of RLS as a seatbelt and application-level filtering as driving carefully. You should do both, but the seatbelt is what saves you when something goes wrong.

Conclusion

Row Level Security transformed how I approach multi-tenant data isolation. Instead of scattered WHERE clauses across every query and middleware on every API route, the database enforces isolation at the lowest possible level. One RLS policy per table, and the entire application is protected.

If you're building a multi-tenant application with Supabase, start with RLS from day one. It's more work upfront than application-level filtering, but it's fundamentally more secure — and once it's set up, you stop worrying about data leaks between tenants. That peace of mind is worth the investment.

Frequently Asked Questions

Does RLS impact database performance?

RLS policies add a small overhead to every query — PostgreSQL evaluates the policy before returning rows. For most applications, this overhead is negligible (single-digit milliseconds). If your policies involve complex subqueries or joins, consider creating indexes on the columns used in policy conditions. In my dashboard with 62,000+ rows, I see no noticeable performance difference with RLS enabled.

Can I use RLS with the Supabase JavaScript client?

Yes, and it's automatic. When you create a Supabase client with the anon key and a user session, every query is filtered by RLS policies. You don't need to do anything special — just write your query normally and RLS handles the rest. Only the service role key bypasses RLS.

What happens if I forget to add an RLS policy?

If RLS is enabled on a table but no policies are defined, the default behavior is to deny all access. This is actually the safest default — it's better to accidentally block access than to accidentally expose data. Always enable RLS first, then add policies.

Can I test RLS policies locally?

Yes. Supabase CLI provides a local development environment with the same RLS behavior as production. You can also test policies directly in SQL by setting the role and JWT claims: SET LOCAL role = 'authenticated'; SET LOCAL request.jwt.claims = '{"sub":"user-uuid"}';

How do Cloudflare Workers write data if they don't have a user session?

Workers use the service role key, which bypasses RLS entirely. Each worker includes the client_id in the data it writes, so the rows are correctly tagged for multi-tenant isolation. The RLS policies only affect reads — the workers can write to any table without restrictions.

Next Article