Supabase RLS Patterns for Building Multi-Tenant Malaysian SaaS

Why RLS is Essential for Multi-Tenant SaaS

In a multi-tenant Software-as-a-Service (SaaS) application, multiple customers (tenants) use the same instance of the software, with their data stored in a shared database. For any Malaysian business, from a small clinic using our SaaS to a large enterprise using a custom billing system, data isolation is not just a feature—it's a legal and commercial necessity. One tenant must never see another tenant's data.

This is where Supabase and its implementation of PostgreSQL's Row Level Security (RLS) become incredibly powerful. Instead of writing data access logic in our application code, we enforce it directly in the database. This creates a strong, consistent security boundary. At JRV Systems, we've shipped several multi-tenant systems, and these are the core Supabase RLS multi-tenant patterns we rely on daily.

Pattern 1: Tenant Isolation via JWT Claim

This is the foundation of almost every multi-tenant RLS setup. When a user logs in, we embed their organization's identifier directly into their JSON Web Token (JWT). Supabase makes this easy by using the app_metadata object, which is non-editable by the client.

Let's say we have an invoices table. Each invoice belongs to a tenant_id.

The RLS policy ensures a user can only see invoices matching the tenant_id in their token.

• Policy Type: SELECT, INSERT, UPDATE, DELETE
• Policy Expression: (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid = tenant_id

This single line is the most critical security rule. For an INSERT or UPDATE, we use the same expression in the WITH CHECK clause to prevent a user from accidentally or maliciously writing data into another tenant's space. This is the first pattern we implement in any new project, be it for e-commerce or a dashboard.

Pattern 2: Row Ownership by User ID

Within a single tenant, you often need another layer of ownership. For example, a sales manager can see all deals for the company (Pattern 1), but a specific salesperson can only edit their own assigned deals. This is ownership at the row level.

Here, we assume the table (e.g., deals) has a user_id column that stores the Supabase auth.uid() of the record's owner.

• Policy Type: UPDATE
• Combined Policy Expression: ((auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid = tenant_id) AND (auth.uid() = user_id)

This policy combines tenant isolation with user ownership. A user must belong to the correct tenant and be the owner of the specific row to be able to update it. For a SELECT policy, you might relax the second condition to allow managers to view all deals within their tenancy.

Pattern 3: Role-Graph Logic in Security Functions

As permissions get more complex ('admin', 'manager', 'viewer'), writing them all directly into RLS policies becomes messy and repetitive. A cleaner pattern is to encapsulate this logic within a PostgreSQL function.

Imagine a memberships table that links user_id, tenant_id, and a role text field. We can create a function to check a user's role:

create or replace function is_tenant_member(check_tenant_id uuid, required_role text) returns boolean as $$ select exists ( select 1 from memberships where tenant_id = check_tenant_id and user_id = auth.uid() and role = required_role ); $$ language sql security definer;

Now, the RLS policy for a sensitive action, like deleting an invoice, becomes simple and readable:

• Policy Type: DELETE on invoices table
• Policy Expression: is_tenant_member(tenant_id, 'admin')

This is how we manage tiered access in the dashboards we build. It keeps policies clean and centralizes permission logic in one place.

Pattern 4: Gating Soft-Deleted Records

In many applications, especially those handling financial or medical data in Malaysia, records are never truly deleted from the database. Instead, they are "soft-deleted" by setting a deleted_at timestamp. RLS is perfect for hiding these records from regular API queries.

For a table like patients, the primary SELECT policy would be:

• Policy Type: SELECT
• Policy Expression: ((auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid = tenant_id) AND (deleted_at is null)

This ensures that day-to-day operations only see active records. An administrator or a special archival interface might use a query that bypasses RLS (using the service_role key) or calls a security definer function to view or restore soft-deleted records, maintaining a complete data history.

Pattern 5: Immutable Audit Trails

When an important action occurs, like a bill being paid or patient data being updated, we log it in an audit_log table. This log must be tamper-proof. RLS can enforce immutability.

We apply a set of policies to the audit_log table:

• INSERT Policy: Any authenticated user within the tenant can create a log entry. USING: ((auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid = tenant_id)
• SELECT Policy: Only users with an 'admin' role can view the audit log. USING: is_tenant_member(tenant_id, 'admin')
• UPDATE Policy: No one can ever change a log entry. WITH CHECK: false
• DELETE Policy: No one can ever delete a log entry. USING: false

This combination ensures that once an audit record is written, it cannot be altered or removed through the public API, providing a high degree of trust in the system's history.

Pattern 6: Local Testing with Supabase CLI

Finally, the most important pattern is not a policy, but a process: testing. You cannot afford to be wrong about security. The Supabase CLI uses pgTAP, a testing framework for PostgreSQL, to verify your RLS policies work as expected.

In your supabase/tests directory, you can create a test file to simulate scenarios:

1. Setup: Create two tenants and a user in each.
2. Impersonate: Use set role and set request.jwt.claims to run queries as a specific user.
3. Assert: Check that the user can only see data from their own tenant.

An example test might look like this:

-- tests/rls/invoices_rls_test.sql select plan(1);

-- Switch to the user from tenant 1 set role authenticated; select set_config('request.jwt.claims', '{"app_metadata": {"tenant_id": "..."}}', true);

-- Assert that this user cannot see any invoices from tenant 2 select is_empty( 'select id from invoices where tenant_id = ''<tenant_2_id>''', 'User from tenant 1 cannot select invoices from tenant 2.' );

Running supabase test db executes these checks. This automated verification is a non-negotiable part of our workflow at JRV Systems. It provides the confidence we need to deploy secure, reliable multi-tenant applications for our clients.