Preventing Data Leaks in Multi-Tenant Apps with Supabase RLS

In a multi-tenant application, the worst-case scenario is a "leaky" query—where User A accidentally sees User B's private data because a developer forgot to add a WHERE user_id = current_user clause to a single API endpoint. Relying on the application server to filter every request is a fragile strategy; it only takes one missed line of code to create a critical security breach.

How RLS Shifts the Security Model

Supabase uses PostgreSQL Row Level Security to treat the database as the final arbiter of truth. When a request hits the Supabase API, it carries a JSON Web Token (JWT) identifying the user. Supabase passes this identity to PostgreSQL, which then evaluates a set of Policies—essentially WHERE clauses that are automatically appended to every query.

The core of this mechanism is the auth.uid() function. This helper function extracts the user's unique ID from the JWT, allowing you to compare it against a column in your table (like user_id or organization_id) to determine access.

Implementing Tenant Isolation: A Worked Example

Consider a scenario where you have a projects table. Each project belongs to a specific user, and users should only be able to view or edit their own projects.

Step 1: Enable RLS

By default, tables are open if RLS is not enabled. Run this in the Supabase SQL Editor to lock the table down:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

Step 2: Create a Select Policy

This policy ensures that a user can only read rows where the user_id matches their own authenticated ID. This is a PERMISSIVE policy, meaning if any permissive policy evaluates to true, access is granted.

CREATE POLICY "Users can view their own projects"
ON projects
FOR SELECT
USING (auth.uid() = user_id);

Step 3: Create an Insert Policy

To prevent users from spoofing other accounts, you must ensure they can only insert rows where the user_id is set to their own ID.

CREATE POLICY "Users can create their own projects"
ON projects
FOR INSERT
WITH CHECK (auth.uid() = user_id);

Trade-offs and Limitations

While RLS provides a robust security layer, it is not a silver bullet.

• Performance Degradation: Poorly written RLS policies can lead to sequential scans, significantly degrading query performance on large datasets if the columns used are not indexed.
• Recursive Policies: Policies that query the table they are protecting can cause infinite loops and stack overflow errors.
• The Service Role Key: The service_role key bypasses RLS entirely. This is necessary for administrative backend tasks but extremely dangerous if exposed to the client-side code.
• Input Validation: RLS does not replace the need for input validation; it only controls visibility and modification rights.

Verification and Diagnostics

To ensure your RLS policies are working and efficiently, you can use EXPLAIN ANALYZE. This checks if the RLS filter is being applied as an efficient index scan rather than a full table scan.

-- Simulate a user session in the SQL editor (replace the UUID with a real user ID)
SET request.jwt.claims = '{\"sub\": \"your-uuid-here\"}';

EXPLAIN ANALYZE
SELECT * FROM projects;

Check the output for Index Scan. If you see Seq Scan, you likely need to index the user_id column used in your policy.