Row Level Security: Per-Tenant Isolation in PostgreSQL#

Row Level Security (RLS) lets the database enforce data isolation. Instead of trusting every query to filter by tenant_id, PostgreSQL rejects rows that violate your policy before they ever reach the application.

Why RLS?#

Without RLS, every query must include the tenant filter:

-- Every developer must remember this. Every time.
SELECT * FROM invoices WHERE tenant_id = current_tenant();
-- One missed WHERE clause = data leak

With RLS, the database enforces it:

-- Policy handles filtering automatically
SELECT * FROM invoices;
-- Only returns rows for the current tenant

Enabling RLS#

Two steps: enable RLS on the table, then create policies.

-- Step 1: Enable RLS
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Step 2: Create a policy
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Important: table owners bypass RLS by default. Force it with:

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

Setting the Tenant Context#

Set the tenant at the start of each request:

-- In your connection middleware
SET LOCAL app.current_tenant = '550e8400-e29b-41d4-a716-446655440000';

SET LOCAL scopes to the current transaction, so it cannot leak between requests in a connection pool.

Request → Begin Transaction → SET LOCAL tenant → Queries → Commit
                                    ↓
                          RLS reads app.current_tenant
                          Filters every table automatically

Policy Types#

Permissive Policies (Default)#

Multiple permissive policies combine with OR:

-- Users see their own tenant's data
CREATE POLICY tenant_access ON orders
  AS PERMISSIVE
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Support staff see flagged orders across tenants
CREATE POLICY support_access ON orders
  AS PERMISSIVE
  USING (current_setting('app.role') = 'support' AND flagged = true);

-- Result: tenant's own rows OR flagged rows (if support)

Restrictive Policies#

Restrictive policies combine with AND and further narrow permissive results:

-- Permissive: see your tenant's data
CREATE POLICY tenant_access ON documents
  AS PERMISSIVE
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Restrictive: but only if not archived
CREATE POLICY hide_archived ON documents
  AS RESTRICTIVE
  USING (archived = false);

-- Result: tenant's rows AND not archived

Command-Specific Policies#

Control different operations independently:

-- Anyone in the tenant can SELECT
CREATE POLICY read_policy ON projects
  FOR SELECT
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Only owners can UPDATE
CREATE POLICY update_policy ON projects
  FOR UPDATE
  USING (tenant_id = current_setting('app.current_tenant')::uuid)
  WITH CHECK (owner_id = current_setting('app.current_user')::uuid);

-- Only admins can DELETE
CREATE POLICY delete_policy ON projects
  FOR DELETE
  USING (
    tenant_id = current_setting('app.current_tenant')::uuid
    AND current_setting('app.role') = 'admin'
  );

Multi-Tenant SaaS Pattern#

Full Schema#

-- Tenants table
CREATE TABLE tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  plan text NOT NULL DEFAULT 'free'
);

-- Users belong to tenants
CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) NOT NULL,
  email text NOT NULL,
  role text NOT NULL DEFAULT 'member'
);

-- Business data
CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid REFERENCES tenants(id) NOT NULL,
  name text NOT NULL,
  created_by uuid REFERENCES users(id)
);

-- Enable RLS on all tenant-scoped tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Tenant isolation policies
CREATE POLICY tenant_isolation ON users
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Middleware Example#

Request with JWT
    ↓
Extract tenant_id and user_id from token
    ↓
BEGIN transaction
    ↓
SET LOCAL app.current_tenant = tenant_id
SET LOCAL app.current_user = user_id
SET LOCAL app.role = user_role
    ↓
Execute queries (RLS filters automatically)
    ↓
COMMIT

Performance Impact#

Indexing Is Critical#

RLS adds a WHERE clause to every query. Without proper indexes, performance degrades:

-- Always index the tenant column
CREATE INDEX idx_invoices_tenant ON invoices (tenant_id);

-- Composite indexes for common queries
CREATE INDEX idx_invoices_tenant_date ON invoices (tenant_id, created_at DESC);

-- Partial indexes for filtered queries
CREATE INDEX idx_projects_active ON projects (tenant_id)
  WHERE archived = false;

Benchmarks#

Table: 10M rows, 1000 tenants, ~10K rows per tenant

Without RLS (manual WHERE):   2.1ms avg
With RLS (policy filter):     2.3ms avg (+10%)
With RLS (no tenant index):  145ms avg (DON'T DO THIS)

The overhead is minimal with proper indexes. The policy is essentially an additional WHERE clause that the planner optimizes normally.

Check Query Plans#

-- See how RLS affects the plan
EXPLAIN ANALYZE SELECT * FROM invoices WHERE amount > 1000;

-- Look for "Filter: (tenant_id = ...)" in the output
-- Ensure it uses the index, not a sequential scan

Testing RLS Policies#

Unit Test Pattern#

-- Test setup: create test tenants
INSERT INTO tenants (id, name) VALUES
  ('aaaa-...', 'Tenant A'),
  ('bbbb-...', 'Tenant B');

INSERT INTO projects (tenant_id, name) VALUES
  ('aaaa-...', 'Project Alpha'),
  ('bbbb-...', 'Project Beta');

-- Test: Tenant A cannot see Tenant B data
SET LOCAL app.current_tenant = 'aaaa-...';
SELECT count(*) FROM projects;
-- Expected: 1 (only Project Alpha)

-- Test: Tenant B cannot see Tenant A data
SET LOCAL app.current_tenant = 'bbbb-...';
SELECT count(*) FROM projects;
-- Expected: 1 (only Project Beta)

Test for Policy Gaps#

-- Verify no access without tenant context
RESET app.current_tenant;
SELECT count(*) FROM projects;
-- Expected: 0 (no rows visible without context)

-- Verify INSERT respects WITH CHECK
SET LOCAL app.current_tenant = 'aaaa-...';
INSERT INTO projects (tenant_id, name) VALUES ('bbbb-...', 'Sneaky');
-- Expected: ERROR (policy violation)

Automated Testing#

CI Pipeline:
  1. Create test database with RLS policies
  2. Seed with multi-tenant data
  3. For each tenant:
     - Set tenant context
     - Verify SELECT returns only own data
     - Verify INSERT/UPDATE/DELETE restricted
  4. Test cross-tenant access attempts
  5. Test missing context scenarios

Common Pitfalls#

1. Forgetting FORCE ROW LEVEL SECURITY Table owners bypass RLS. Always use FORCE if the app connects as the table owner.

2. Superuser bypass Superusers always bypass RLS. Never let your application connect as a superuser.

3. Missing policies on new tables Add RLS to your migration workflow. Every table with tenant_id needs a policy.

4. Leaking data through functions Functions run with the definer's permissions by default. Use SECURITY INVOKER:

CREATE FUNCTION get_project_count() RETURNS integer
  LANGUAGE sql SECURITY INVOKER
  AS $$ SELECT count(*)::integer FROM projects; $$;

5. Connection pooling without SET LOCAL Use SET LOCAL (transaction-scoped), not SET (session-scoped). Session-scoped settings persist across pooled connections.

RLS vs Application-Level Filtering#

                    RLS                          App Filtering
Security        Database-enforced             Developer-dependent
Performance     Optimized by planner          Manual optimization
Maintenance     Central policy                Scattered WHERE clauses
Testing         SQL-level tests               Integration tests
Complexity      Learning curve                Simple but error-prone

Use RLS as the security boundary. Application filtering can layer on top for UX (pagination, search), but never as the sole tenant isolation mechanism.

Generate your multi-tenant database architecture at codelit.io →

---

Article #446 in the Codelit engineering series. Explore our full library of system design, infrastructure, and architecture guides at codelit.io.