patternsqlpostgresqlMajor
Row-level security (RLS) for tenant isolation in multi-tenant apps
Viewed 0 times
row level securityRLStenant isolationmulti-tenantcurrent_settingpolicy
Problem
Multi-tenant applications that filter by tenant_id in every query risk data leakage when a developer forgets to add the WHERE clause. Defense-in-depth requires enforcement at the database layer.
Solution
Enable RLS and create policies that enforce tenant isolation:
-- Enable RLS on the table:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Policy: users can only see their own tenant's rows:
CREATE POLICY tenant_isolation ON documents
USING (tenant_id = current_setting('app.current_tenant_id')::bigint);
-- Set the tenant context at the start of each request:
SET LOCAL app.current_tenant_id = '42';
-- Bypass RLS for admin role:
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
CREATE POLICY admin_bypass ON documents TO admin_role USING (true);
-- Verify policies:
SELECT * FROM pg_policies WHERE tablename = 'documents';
-- Enable RLS on the table:
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Policy: users can only see their own tenant's rows:
CREATE POLICY tenant_isolation ON documents
USING (tenant_id = current_setting('app.current_tenant_id')::bigint);
-- Set the tenant context at the start of each request:
SET LOCAL app.current_tenant_id = '42';
-- Bypass RLS for admin role:
ALTER TABLE documents FORCE ROW LEVEL SECURITY;
CREATE POLICY admin_bypass ON documents TO admin_role USING (true);
-- Verify policies:
SELECT * FROM pg_policies WHERE tablename = 'documents';
Why
RLS policies are evaluated by the query executor before any WHERE clause the application provides. Even if the application sends SELECT * FROM documents, it only sees rows satisfying the policy.
Gotchas
- Table owners bypass RLS by default; use FORCE ROW LEVEL SECURITY to enforce it for owners too
- RLS does not apply to superusers; use a dedicated application role, not a superuser connection
- current_setting() returns an empty string if the GUC is not set; use current_setting('app.tenant', true) to return NULL instead of raising an error
- RLS adds a predicate to every query; ensure the tenant_id column is indexed
Code Snippets
RLS policy using safe current_setting with missing-ok flag
-- Safe current_setting that returns NULL when unset:
CREATE POLICY tenant_isolation ON documents
USING (
tenant_id = current_setting('app.current_tenant_id', true)::bigint
);Context
SaaS applications storing multiple tenants' data in shared tables
Revisions (0)
No revisions yet.