Practical Supabase RLS Multi-Tenant Patterns for SaaS

Supabase, with its integrated PostgreSQL database, offers a powerful feature called Row-Level Security (RLS). For multi-tenant Software-as-a-Service (SaaS) applications, RLS is not just a feature; it's the foundation of data security. It ensures that users from one tenant (like a company or a clinic) can never access data belonging to another. At JRV Systems, we build multi-tenant systems for Malaysian businesses, and getting RLS right is a non-negotiable part of our process. Over several projects, from clinic management systems to billing platforms, we've refined a set of reliable patterns.

This article outlines six practical Supabase RLS multi-tenant patterns we use to secure our applications. These aren't theoretical concepts but battle-tested policies that are currently in production.

The Core Principle: Tenant Isolation

Before diving into specific patterns, the core principle of any multi-tenant RLS strategy is tenant isolation. Almost every table in your database that contains tenant-specific data should have a tenant_id column (you might call it organization_id or company_id).

Every RLS policy you write will then check the incoming request's identity against this tenant_id. Supabase makes this straightforward by exposing user information, particularly JWT claims, through the auth.jwt() function in PostgreSQL. Your policies will use this function to verify that a user is who they say they are and belongs to the tenant they are trying to access.

Pattern 1: Tenant by JWT Claim

This is the most fundamental and common pattern. When a user signs up or is invited to a tenant, we store their tenant_id in their user metadata. Specifically, we use the app_metadata field, as it's not modifiable by the user from the client side.

A typical policy for a projects table would look like this:

( (auth.jwt() -> 'app_metadata') ->> 'tenant_id' )::uuid = tenant_id

This policy enables access if the tenant_id from the user's secure token matches the tenant_id of the row they are trying to access. It's simple, efficient, and forms the baseline for almost all other policies.

Pattern 2: Owner by Row Column

Sometimes, tenancy isn't enough. Within a tenant, a specific record might belong to a single user. For example, a user's personal API keys or draft invoices. For these cases, we add a user_id column to the table, which stores the id of the user who owns the record.

The policy combines the tenant check with a user check:

• SELECT: A user can view a record if they are in the correct tenant AND are the owner of the record. ((auth.jwt() -> 'app_metadata') ->> 'tenant_id')::uuid = tenant_id AND auth.uid() = user_id
• INSERT: A user can insert a new record if the user_id they are inserting matches their own ID. auth.uid() = user_id

This layered approach ensures both tenant isolation and user-level ownership.

Pattern 3: Role-Based Access via a Graph

Multi-tenant applications often require different permission levels. An 'admin' can invite users and manage billing, while a 'member' can only view data. Storing a single role in the JWT can be limiting. Instead, we use a relational model—a 'graph'—to manage permissions.

We create a table, often called memberships or user_roles, with columns like user_id, tenant_id, and role (an enum of 'admin', 'member', etc.).

An RLS policy can then query this table to check for permissions. Because RLS policies can be performance-intensive, it's often best to wrap this logic in a SECURITY DEFINER function.

-- A simplified function to check roles
create or replace function check_user_role(tenant_id_to_check uuid, required_role text)
returns boolean as $$
declare
  is_authorized boolean;
begin
  select exists (
    select 1 from memberships
    where memberships.user_id = auth.uid()
    and memberships.tenant_id = tenant_id_to_check
    and memberships.role = required_role
  ) into is_authorized;
  return is_authorized;
end;
$$ language plpgsql security definer;

The RLS policy then becomes a clean call to this function: select check_user_role(tenant_id, 'admin')

We implemented this pattern for a billing SaaS where company owners could invite their accountants with read-only 'viewer' permissions.

Pattern 4: Gating Access with Soft Deletes

In many applications, especially those requiring audit trails, you never truly delete data. Instead, you perform a "soft delete" by marking a record as inactive. This is typically done with a deleted_at timestamp column that is NULL by default.

RLS is the perfect mechanism to enforce this system-wide.

• SELECT/UPDATE/DELETE Policies: Add deleted_at IS NULL to all your existing policies. This makes deleted records invisible and unchangeable to all users.
• DELETE Operations: Your application logic no longer runs DELETE FROM .... Instead, it runs UPDATE ... SET deleted_at = now() WHERE id = .... RLS ensures only users with the right permissions can perform this update.

This ensures data integrity and makes it impossible for active application logic to interact with archived records.

Pattern 5: Securing Audit Trails

If you have an audit trail table that logs all changes, its security is paramount. Users should never be able to modify or delete audit history. RLS can enforce this immutability.

For an audit_log table, the policies would be:

• INSERT: true (assuming inserts are handled by secure database triggers or functions).
• UPDATE: false.
• DELETE: false.
• SELECT: This can be restricted to tenant admins using the role-based pattern described earlier. select check_user_role(tenant_id, 'admin')

This makes the audit log an append-only ledger for tenant administrators, providing a secure record of activity.

How We Test RLS Policies Locally

RLS failures are silent—they don't throw errors, they just return empty sets of data. This makes testing essential. Relying on front-end testing alone is insufficient. At JRV Systems, we use the Supabase CLI to test RLS policies directly against the database.

Our process involves:

1. Seeding Data: We use the supabase/seed.sql file to create a predictable state. This includes creating multiple tenants, users with different roles (e.g., Tenant A Admin, Tenant B Member), and sample data for each.
2. Impersonation: We use a testing framework (like pgTAP or even simple shell scripts) that connects to the local database. Before running a query, we impersonate a specific user by setting the session's JWT claims: SET request.jwt.claims = '{"sub":"user-uuid-goes-here", "app_metadata":{"tenant_id":"tenant-a-uuid"}}';
3. Assertions: The test then runs queries and asserts the results. For example: "When authenticated as the admin for Tenant A, a SELECT on the projects table should return 3 rows." And, critically: "When authenticated as a member of Tenant B, the same query should return 0 rows."

This local, repeatable testing loop gives us high confidence that our Supabase RLS multi-tenant patterns are correctly implemented before we deploy.