Part 184: Row-Level Security (RLS) in Depth: Policies & Tenant Isolation

Enforcing data access boundaries at the database engine level.

1. Why Row-Level Security?

In application-level security, if a developer forgets WHERE tenant_id = :id in just one endpoint, customer data leaks to competitors! Row-Level Security (RLS) moves tenant isolation into the database engine: queries literally cannot see rows belonging to another tenant!

2. Enabling RLS and Writing Policies (PostgreSQL)

-- 1. Enable RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 2. Force RLS even for table owners:
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

-- 3. Create security policy checking the session setting
CREATE POLICY tenant_isolation_policy ON documents
FOR ALL
USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

3. Querying with RLS in Your Application

When your web server borrows a connection from the connection pool:

BEGIN;
-- Set the tenant ID for this transaction session:
SET LOCAL app.current_tenant_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

-- This SELECT automatically filters where tenant_id matches!
SELECT * FROM documents;

COMMIT;

Key Takeaways

• Using SET LOCAL ensures tenant context is wiped clean when the transaction finishes, preventing pool leaks.

In Part 185, we explore Column-Level Encryption & Key Management with SQL!