Hardening Multi-Tenant Isolation with Supabase RLS

The Risk of Application-Level Authorization

In many traditional architectures, the application server acts as the sole gatekeeper. You write a WHERE clause in your API endpoint—something like SELECT * FROM orders WHERE tenant_id = $1—to ensure User A doesn't see User B's data. The problem is that a single forgotten filter in one endpoint creates a critical data leak. In a multi-tenant system, this is a catastrophic failure.

The solution is to move the authorization logic from the application layer into the database itself using PostgreSQL Row Level Security (RLS). By defining access rules at the table level, the database rejects unauthorized rows regardless of how the query is written in your frontend or backend code.

How Supabase Implements RLS

Supabase utilizes PostgreSQL's native RLS, but simplifies the identity piece. When a user authenticates via Supabase Auth, they receive a JSON Web Token (JWT). When that JWT is passed to the database, Supabase populates a special function, auth.uid(), with the user's unique ID.

To implement multi-tenancy, you typically introduce a tenant_id or organization_id column to your data tables. Instead of trusting the client to send the correct ID, you create a policy that compares the user's membership in a tenant table against the tenant_id of the row they are trying to access.

Worked Example: Organization-Based Access

Imagine a SaaS application where users belong to an organization. You have a profiles table that maps users to organizations and a documents table containing the actual data.

1. Enable RLS

First, you must explicitly enable RLS. If you skip this step, the table remains public by default.

-- Run in the Supabase SQL Editor
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

2. Create the Isolation Policy

This policy allows a user to SELECT a document only if their user_id is associated with the document's organization_id in the profiles table.

CREATE POLICY \"Users can view their organization's documents\" 
ON documents 
FOR SELECT 
USING (
  organization_id IN (
    SELECT organization_id 
    FROM profiles 
    WHERE user_id = auth.uid()
  )
);

3. Verification

To verify this is working, attempt a request via the Supabase JavaScript client using a user's JWT. If the user is not part of the organization linked to a specific row, the API will return an empty array [] rather than a 403 error, as PostgreSQL simply filters out the rows the user isn't permitted to see.

Performance and the Indexing Trap

RLS is powerful, but it is not free. Every time you run a query, the database must evaluate the USING expression for every candidate row. If your policy contains a subquery (like the profiles lookup above), it can lead to severe latency as your dataset grows.

To mitigate this, ensure that any column used in an RLS policy is indexed. In the example above, documents.organization_id and profiles.user_id must have B-tree indexes. Without them, the database may perform a full table scan for every single row check, turning a millisecond query into a multi-second timeout.

The 'Service Role' Escape Hatch

There are times when you need to bypass these rules—such as for a nightly backup script or an administrative dashboard. Supabase provides a service_role key for this purpose. This key bypasses RLS entirely.

• Risk: Never expose the service_role key in a frontend application or client-side environment. Anyone with this key has full superuser access to your data.
• Use Case: Use it only in secure, server‑side environments (like Supabase Edge Functions or a private Node.js server).