Supabase RLS Patterns for Multi-Tenant SaaS Applications

When building a multi-tenant Software-as-a-Service (SaaS) application, nothing is more critical than data isolation. A single user from Tenant A must never, under any circumstances, see data from Tenant B. At JRV Systems, we rely on PostgreSQL's Row Level Security (RLS), a core feature of Supabase, to enforce this boundary at the database level. It's a powerful tool that, when used correctly, makes data leaks nearly impossible.

Over several projects, from clinic management systems to e-commerce platforms for Malaysian businesses, we've refined our approach. Here are six essential Supabase RLS multi-tenant patterns we have implemented in production.

1. The Foundation: Tenant ID via JWT Claims

This is the most fundamental and efficient pattern for multi-tenancy. The core idea is to embed the user's tenant_id directly into their JSON Web Token (JWT) when they log in. This token is sent with every database request, making the tenant context always available.

How it works:

1. When a user signs in, a custom function or database trigger adds their tenant_id to the app_metadata of their JWT.
2. Your RLS policy then extracts this ID to filter queries.

Supabase provides a helper function to access JWT claims, making the policy clean and readable:

CREATE POLICY "Tenant Isolation Policy" ON public.invoices FOR ALL USING (tenant_id = (auth.jwt() ->> 'app_metadata')::jsonb ->> 'tenant_id');

This single policy, applied to every table containing tenant data, ensures a hard wall between tenants. It's the first layer of security we implement in any multi-tenant system.

2. Row Ownership for Granular Control

Sometimes, tenant-level isolation isn't enough. Within a single tenant, you might need to restrict access to specific records based on who created them. For example, a doctor in a clinic should only see their own consultation notes, not those of other doctors in the same clinic.

This pattern is simple: add a user_id column to the table that stores the auth.uid() of the record's creator.

The policy combines the tenant check with a user check:

CREATE POLICY "Owner Access Policy" ON public.notes FOR ALL USING (tenant_id = get_tenant_id_from_claim() AND user_id = auth.uid());

This layered approach provides both tenant-wide and user-specific security, which is common in collaborative applications like the dashboards we build.

3. Role-Based Access with a Members Table

Complex applications require different permission levels. An 'admin' can see all invoices for a tenant, while a 'viewer' can only read them. A simple user_id check is insufficient.

Our solution is a dedicated members table that maps users to tenants with a specific role:

• user_id (UUID, foreign key to auth.users)
• tenant_id (UUID, foreign key to tenants)
• role (TEXT, e.g., 'admin', 'member')

An RLS policy can't directly query this table efficiently and securely without creating a function. We create a SECURITY DEFINER function that checks if the current user has the required role for a given tenant. This function runs with the privileges of the function owner, allowing it to bypass RLS to check the members table.

CREATE POLICY "Admin Read Access" ON public.invoices FOR SELECT USING (is_tenant_member(tenant_id, 'admin'));

This pattern keeps our policies clean and centralizes complex permission logic into a few reusable functions.

4. Gating Access to Soft-Deleted Records

We prefer soft-deleting records (marking them with a deleted_at timestamp) over permanent deletion. This preserves data for auditing or recovery. However, by default, users shouldn't see these archived records in their daily workflow.

RLS is perfect for managing this. The standard policy for a table can be updated to hide soft-deleted items:

USING (tenant_id = get_tenant_id_from_claim() AND deleted_at IS NULL)

For administrators or for a 'trash' feature, a separate, more permissive policy can be created. This policy would allow users with an 'admin' role to see records where deleted_at is NOT null, giving them the ability to view or restore data.

5. Securing Audit Trail Tables

For many of the systems we build, particularly our clinic SaaS, maintaining a secure audit trail is a regulatory requirement. This trail logs who did what and when. This data is extremely sensitive.

An audit_log table must be protected by RLS. While INSERT operations are typically handled by database triggers that run with elevated privileges, SELECT access must be strictly locked down.

A simple policy ensures users can only view audit events related to their own tenant:

CREATE POLICY "Audit Log Tenant Isolation" ON public.audit_logs FOR SELECT USING (tenant_id = get_tenant_id_from_claim());

Further rules can be added to restrict access only to tenant admins, preventing regular users from seeing the full activity log of their colleagues.

6. How We Test RLS Policies

RLS policies are application code. They can have bugs, and those bugs can lead to catastrophic data leaks. They must be tested.

We use the Supabase CLI and the pg_prove testing framework to automate our RLS tests. Our process looks like this:

1. Seed Data: We create a supabase/seed.sql file that populates the test database with multiple tenants, users with different roles, and sample data across them.
2. Test Scripts: We write SQL test files. Each test impersonates a specific user and runs queries.
3. Impersonation: The key is to set the session context to mimic a real user. We use SET LOCAL "request.jwt.claims" = '...'; to simulate a JWT with a specific tenant_id and user_id.
4. Assertions: The test script then queries the database and asserts that it only receives the data that specific user is supposed to see. If a query for Tenant B's data returns a result while impersonating a user from Tenant A, the test fails.

This automated testing gives us confidence that our Supabase RLS multi-tenant patterns are correctly implemented before we ship any code.