Practical Supabase RLS Multi-Tenant Patterns for SaaS

At JRV Systems, we build multi-tenant SaaS applications for Malaysian businesses, from clinic management systems to automated billing platforms. A non-negotiable requirement for these systems is data isolation: one client must never see another client's data. Supabase, with its integrated PostgreSQL database, offers a powerful tool for this called Row Level Security (RLS).

RLS allows you to write database rules that filter which rows a user can see or modify. It's like putting a firewall on every single row of your data. When implemented correctly, it provides a robust security foundation. Here are six practical Supabase RLS multi-tenant patterns we have used in our projects.

Core Supabase RLS Multi-Tenant Patterns

The foundation of any RLS policy in Supabase is authentication. Every request is tied to a JSON Web Token (JWT) which identifies the user. We can access this user's information inside our policies using functions like auth.uid() to get their unique ID.

For multi-tenant applications, we extend this by adding custom claims to the JWT. A tenant_id claim, for example, tells the database which organization the user belongs to. This single piece of information becomes the bedrock for most of our security policies.

Pattern 1: Tenant Isolation via JWT Claim

The most fundamental pattern for any SaaS is isolating data by tenant. Each row in a critical table (like invoices, patients, or projects) must have a tenant_id column. The RLS policy then simply checks if the tenant_id in the row matches the tenant_id in the user's JWT.

The policy expression looks like this:

(current_setting('request.jwt.claims', true)::jsonb ->> 'tenant_id')::uuid = tenant_id

This single line, applied to every relevant table, ensures that a user from Clinic A can never accidentally query data belonging to Clinic B. It's the first policy we write for any new multi-tenant project.

Pattern 2: Row Ownership for User-Specific Data

Within a tenant, some data belongs to a specific user. Think of user-specific API keys, personal notes, or draft documents. For these, we use a simple ownership model.

The table will have a user_id or created_by column that stores the ID of the user who created the row. The policy is straightforward:

auth.uid() = user_id

We often combine this with tenant isolation. A user can only see their own documents within their own tenant. The combined policy would be:

((current_setting('request.jwt.claims', true)::jsonb ->> 'tenant_id')::uuid = tenant_id) AND (auth.uid() = user_id)

Pattern 3: Role-Based Access via a Junction Table

Real-world applications require more granular permissions. A manager needs to see all invoices for their company, but a regular staff member should only see the ones they created. This is role-based access control (RBAC).

We implement this using a junction table, often called memberships or roles. This table links users to tenants and assigns them a role (e.g., 'admin', 'member', 'viewer').

• memberships table columns: id, user_id, tenant_id, role (text)

The RLS policy on the invoices table then checks this memberships table to see if the current user has the required role for the invoice's tenant.

For an admin who can see all invoices in their tenant:

EXISTS (SELECT 1 FROM memberships WHERE memberships.user_id = auth.uid() AND memberships.tenant_id = invoices.tenant_id AND memberships.role = 'admin')

This pattern is crucial for the clinic systems we build, where access to patient data must be strictly controlled based on whether the user is a doctor, a nurse, or an administrator.

Pattern 4: Advanced Policies for Operations and State

RLS isn't just for viewing data (SELECT). It can control INSERT, UPDATE, and DELETE operations, often based on the state of the data itself.

• Soft Deletes: When a user "deletes" a record, we often just mark it with a timestamp in a deleted_at column. An RLS policy can hide these records from regular users by adding deleted_at IS NULL to the USING clause. A separate policy might allow an 'admin' role to see all records, including soft-deleted ones, for auditing purposes.
• Immutable Audit Trails: For an audit_logs table, we want to ensure records are append-only. They can be created but never changed or deleted. We can achieve this with separate policies:
  • FOR INSERT: A permissive policy that allows users to add logs.
  • FOR UPDATE: A policy that simply uses WITH CHECK (false) to deny all updates.
  • FOR DELETE: A policy that uses USING (false) to deny all deletions.

How We Test RLS Policies Reliably

An untested security policy is a liability. A small mistake in your SQL logic could expose all your customer data. At JRV Systems, we make testing RLS a priority before any deployment.

Our primary tool is the supabase/seed.sql file. We use it to create a predictable database state for testing:

1. Create multiple tenants (e.g., 'Company A', 'Company B').
2. Create users with different roles within each tenant (e.g., admin_A, member_A, admin_B).
3. Populate tables with data belonging to each tenant.

With this seeded data, our automated test suites can use the Supabase client library to impersonate each user. We log in as member_A and assert that we can only see data for Company A, and that we cannot perform admin actions. We then log in as admin_B and run similar checks for Company B. This process verifies that our tenant isolation and role-based policies are working exactly as intended, giving us and our clients confidence in the system's security.