Row-level security (RLS) is Postgres's mechanism for enforcing access policies at the database level — policies that restrict which rows a given database role can see or modify. For multi-tenant SaaS, it's one of the most important architectural decisions you'll make, and it gets remarkably little production-level documentation.
This is a production guide based on building the Meridian Analytics Platform — a multi-tenant HR SaaS with 2M+ events/day, serving enterprise tenants with strict data isolation requirements.
Why Database-Layer Isolation Matters
The naive approach to multi-tenant data isolation is application-layer filtering: every query includes a WHERE tenant_id = :current_tenant_id clause. This works. It's also dangerous.
Application-layer filtering is only as reliable as the discipline of every engineer writing every query. A single missing WHERE clause — a new engineer, a rushed feature, a query written for a one-off report — can expose tenant data. Application-layer filtering cannot be tested in a meaningful way because the failure mode is "query that works but returns wrong data," not "query that errors."
RLS moves the enforcement to the database. The database will not return rows from another tenant, regardless of what the application asks for. A misconfigured API route, an accidentally broad query, a missing WHERE clause — none of these can produce a cross-tenant data leak if RLS is configured correctly. That's a qualitatively different guarantee.
Enterprise security reviews test for this distinction explicitly.
The Basic Pattern
-- Enable RLS on the table
ALTER TABLE events ENABLE ROW LEVEL SECURITY;
-- Policy: tenants see only their own rows
CREATE POLICY tenant_isolation ON events
USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
The current_setting mechanism sets a session-level variable that Postgres uses when evaluating the policy. In your application layer:
-- At the start of each request, set the tenant context
SET LOCAL app.current_tenant_id = '{{tenant_id}}';
With this in place, every query against the events table is automatically filtered by the current tenant. No WHERE clause needed. No risk of forgetting it.
The Performance Considerations
This is where most RLS tutorials stop. In production, you need to understand what happens to query performance under RLS.
The index requirement. The RLS policy adds an implicit filter on tenant_id to every query. If your queries aren't indexed on tenant_id, this filter runs as a table scan. At scale, this is catastrophic.
The solution: composite indexes that include tenant_id as the leading column on any table with RLS:
-- Events: most queries filter on tenant + time range
CREATE INDEX events_tenant_time_idx ON events (tenant_id, event_timestamp);
-- Events: queries filtering by tenant + event type
CREATE INDEX events_tenant_type_idx ON events (tenant_id, event_type, event_timestamp);
Policy complexity. Policies that call functions — particularly functions that query other tables — create hidden performance problems. A policy like this:
-- DON'T DO THIS
CREATE POLICY tenant_isolation ON events
USING (tenant_id IN (
SELECT tenant_id FROM user_memberships WHERE user_id = current_user_id()
));
...runs a subquery on every row the query examines. This is expensive at scale. The correct pattern is to resolve the tenant_id at the application layer and set it as a session variable. Keep policies simple: tenant_id = current_setting(...)::uuid.
EXPLAIN ANALYZE is your tool. Before shipping any schema change in a system with RLS enabled, run EXPLAIN ANALYZE on your critical queries and verify the policy is being evaluated efficiently. The policy should appear in the plan as a simple filter using an existing index, not as a sequential scan or a subquery.
Supabase-Specific Implementation
Supabase has first-class RLS support and a slightly different pattern using auth.uid() and JWT-based tenant context.
-- Supabase pattern using auth context
CREATE POLICY tenant_isolation ON events
USING (tenant_id = (
SELECT (auth.jwt() -> 'user_metadata' ->> 'tenant_id')::uuid
));
The challenge with this pattern at high volume: auth.jwt() parses the JWT on every row evaluation. At 1M+ events/day, this adds up.
The optimisation: extract the tenant_id from the JWT at the Edge Function level and set it as a Postgres setting before executing queries:
-- In your Edge Function or middleware, before running queries:
SELECT set_config('app.current_tenant_id', :tenant_id, true);
Then use the faster current_setting() approach in your policies rather than parsing the JWT per row.
Schema Design for RLS
RLS performs best when the schema is designed with it in mind, not when it's bolted on.
Consistent tenant_id columns. Every table that participates in tenant isolation should have a tenant_id column with a NOT NULL constraint and a foreign key reference to the tenants table. This consistency makes the policy pattern predictable across all tables.
Cascading UUIDs. Use UUIDs for tenant_id, not sequential integers. The reason: integer tenant IDs are guessable. Even with RLS, sequential integer IDs can leak information about your tenant count or the age of a given tenant. UUIDs are opaque.
Audit tables. Audit/event log tables that store cross-tenant data (system-level events, billing events, admin actions) should have RLS disabled and be accessible only through service-role credentials, not through the tenant-scoped database role.
Testing the Isolation
RLS enforcement needs to be tested explicitly, not assumed. A test that verifies tenant isolation:
-- As tenant A, set the tenant context
SET LOCAL app.current_tenant_id = 'tenant-a-uuid';
-- Query should return only tenant A's rows
SELECT count(*) FROM events; -- Should equal only tenant A's event count
-- Attempt to query tenant B's specific row directly
SELECT * FROM events WHERE tenant_id = 'tenant-b-uuid'; -- Should return 0 rows
Write this as an automated test that runs on every schema change. The goal is a failing test if RLS is ever misconfigured — not a post-incident discovery.
The Decision to Enable
Enabling RLS on a live production database with existing data is a migration event, not a configuration change. The steps:
- Add tenant_id columns and populate them for existing rows
- Create the composite indexes
- Enable RLS with
ALTER TABLE ... ENABLE ROW LEVEL SECURITY - Create the policy
- Set the
FORCE ROW LEVEL SECURITYflag to ensure policies apply even to table owners
The order matters. Enabling RLS before creating the policy locks out all queries (the default behavior when no policy exists is to deny access to all rows for non-superusers). Write the policy before enabling.
If you're building new: enable RLS in the initial migration. It's trivially easy at the beginning and expensive to retrofit.
What Good Looks Like
A multi-tenant schema with well-implemented RLS should:
- Pass an enterprise security audit's cross-tenant isolation test
- Show no performance regression vs. equivalent queries without RLS, when indexes are correct
- Have automated tests that verify a misconfigured policy would fail
- Be self-documenting: the schema itself makes the isolation mechanism visible
That last point matters operationally. When a new engineer joins and looks at the events table, they should be able to see the RLS policy and understand the isolation model without needing it explained. Policies are documentation that the database enforces.