Supabase RLS Multi-Tenant Patterns for Malaysian SaaS Apps

Why RLS is Critical for Multi-Tenant SaaS

When building a multi-tenant Software-as-a-Service (SaaS) application, data isolation is not optional—it's the foundation of trust. Your customers, whether they are clinics using a management system or businesses using our billing software, must be absolutely certain their data is inaccessible to other tenants. While you can build these checks into your application code, this approach is fragile. A single bug could expose sensitive information.

This is where PostgreSQL's Row-Level Security (RLS), a core feature of Supabase, becomes essential. RLS pushes security down to the database layer. It ensures that no matter how a user queries the data—through your API, a direct connection, or a flawed piece of application logic—they can only see the rows they are explicitly permitted to see. This article details six practical Supabase RLS multi-tenant patterns we have implemented in projects for our clients in Malaysia.

Pattern 1: Tenant Isolation via JWT Claim

This is the most fundamental pattern for any multi-tenant application. The core idea is to embed a tenant_id directly into the user's JSON Web Token (JWT) when they log in. This claim becomes an undeniable fact about the user's session.

Every table that contains tenant-specific data must have a tenant_id column. The RLS policy then simply checks if the tenant_id in the row matches the tenant_id from the user's JWT.

For a table like invoices, the policy would look like this:

CREATE POLICY "Allow access based on tenant" ON invoices FOR ALL USING (tenant_id = (auth.jwt() ->> 'app_metadata')::uuid);

We store the tenant_id in the app_metadata section of the JWT, which is a secure place for non-user-controlled data. This single policy, applied across all relevant tables, forms a strong perimeter, ensuring Clinic A can never see invoices belonging to Clinic B.

Pattern 2: Row Ownership by User ID

While tenant isolation is crucial, you often need more granular control within a tenant. For example, a doctor should only see their own patient notes, even if they belong to the same clinic (tenant) as other doctors.

This is achieved by adding a user_id column to the relevant table. The policy then checks if the currently authenticated user's ID matches the one in the row.

For a patient_notes table, you would combine this with the tenant check:

CREATE POLICY "Users can access their own notes within their tenant" ON patient_notes FOR ALL USING (tenant_id = (auth.jwt() ->> 'app_metadata')::uuid AND user_id = auth.uid());

This layered approach is powerful. The first check confirms the user is in the right clinic, and the second confirms they are the owner of the specific record.

Pattern 3: Complex Permissions with a Role Graph

Real-world applications require more than just owners and tenants. You need roles like 'admin', 'doctor', or 'billing_staff'. A clinic administrator, for instance, might need to see all invoices for their clinic, not just the ones they created. This is where a role graph comes in.

We typically implement this with a few tables:

• profiles: Stores user information, linked to auth.users.
• roles: A simple table with role names (e.g., 'admin', 'member').
• user_roles: A linking table that connects a user_id to a role_id within a specific tenant_id.

Because RLS policies cannot directly perform complex joins efficiently, we use a PostgreSQL SECURITY DEFINER function. This function can run with elevated privileges to check a user's role and return a simple true/false.

CREATE OR REPLACE FUNCTION check_user_role(role_to_check text) RETURNS boolean AS $$ BEGIN RETURN EXISTS ( SELECT 1 FROM user_roles ur JOIN roles r ON ur.role_id = r.id WHERE ur.user_id = auth.uid() AND ur.tenant_id = (auth.jwt() ->> 'app_metadata')::uuid AND r.name = role_to_check ); END; $$ LANGUAGE plpgsql SECURITY DEFINER;

Now, an admin policy for the invoices table becomes clean and readable:

CREATE POLICY "Admins can view all tenant invoices" ON invoices FOR SELECT USING (check_user_role('admin'));

Pattern 4: Gating Access to Soft-Deleted Records

We rarely hard-delete data. Instead, we use a "soft-delete" pattern by adding a deleted_at timestamp column. This preserves data for auditing or recovery. However, regular users should not see these soft-deleted records.

RLS is perfect for enforcing this rule at the database level. A standard user's policy includes a check to ensure deleted_at is null.

CREATE POLICY "Users can see non-deleted records" ON invoices FOR SELECT USING (deleted_at IS NULL AND tenant_id = ...);

An administrator, however, might need to view or restore these records. You can create a separate, more permissive policy just for them:

CREATE POLICY "Admins can see all records, including deleted" ON invoices FOR SELECT USING (check_user_role('admin'));

PostgreSQL applies policies with an OR condition, so if a user is an admin, the second policy grants them access even if the first one doesn't.

Pattern 5: Securing Audit Trails

For compliance, especially in systems like clinic management or billing, an immutable audit trail is necessary. This is a table that logs every important action. The RLS policies for an audit_logs table are unique:

• INSERT: Any authenticated user should be able to add to the log. The policy is simple: CREATE POLICY "Allow all inserts" ON audit_logs FOR INSERT WITH CHECK (true);
• SELECT: Only high-privilege users, like a system administrator, should be able to view the full log. CREATE POLICY "Admins can read logs" ON audit_logs FOR SELECT USING (check_user_role('system_admin'));
• UPDATE / DELETE: Absolutely no one should be able to change or delete history. CREATE POLICY "Disallow updates and deletes" ON audit_logs FOR UPDATE, DELETE USING (false);

This set of policies creates a write-only ledger for most users, ensuring the integrity of the audit trail.

How We Test RLS Policies Locally

RLS policies are powerful but can be difficult to debug. A typo can either expose all your data or lock everyone out. At JRV Systems, we never ship RLS code without rigorous local testing.

The Supabase CLI makes this straightforward. We use the supabase/seed.sql file to populate our local database with a realistic test scenario:

1. Create multiple tenants (e.g., Clinic A, Clinic B).
2. Create users within each tenant.
3. Assign different roles: an admin in Clinic A, a regular doctor in Clinic A, and a user in Clinic B.

Then, in our test scripts, we use PostgreSQL's set_config to impersonate these users and verify our policies. For example, to run a query as the admin of Clinic A:

SET LOCAL "request.jwt.claims" = '{"role":"authenticated","sub":"user_id_admin_clinic_a","app_metadata":{"tenant_id":"tenant_id_clinic_a"}}';

We then run queries to confirm that this user can see all of Clinic A's data but none of Clinic B's. This repeatable, scriptable testing process gives us confidence that our Supabase RLS multi-tenant patterns are secure before they ever reach production.