DEV Community

Yasser B.
Yasser B.

Posted on • Originally published at rivestack.io

PostgreSQL Row Level Security: A Complete Guide

#postgressql #security #database #webdev

Your application code knows which tenant owns which row. Your ORM always filters by WHERE tenant_id = $1. Your team has reviewed the queries and they look fine.

Then someone forgets the WHERE clause. Or a bulk operation skips the filter. Or a new developer writes a raw query without knowing the convention. Suddenly one tenant can read another tenant's data, and you find out from a support ticket two weeks later.

Row Level Security (RLS) moves the tenant isolation logic inside PostgreSQL itself. The database enforces the policy automatically on every access, regardless of how the query was written.

What Row Level Security Does

Enable RLS on a table:

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
Enter fullscreen mode Exit fullscreen mode

Without any policies, no rows are visible to non-superusers. The safe default is deny, not permit. Then create a policy:

CREATE POLICY documents_tenant_isolation
  ON documents FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
Enter fullscreen mode Exit fullscreen mode

Setting the Tenant Context

Always use SET LOCAL (not SET) with connection poolers. SET LOCAL resets when the transaction ends, so pooled connections do not carry the wrong tenant context into the next request:

BEGIN;
SET LOCAL app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';
-- your queries here
COMMIT;
Enter fullscreen mode Exit fullscreen mode

FORCE ROW LEVEL SECURITY

Table owners bypass RLS by default. Close this gap:

ALTER TABLE documents FORCE ROW LEVEL SECURITY;
Enter fullscreen mode Exit fullscreen mode

Without it, an application connecting as the table owner silently ignores all policies. This is the most common RLS gotcha.

Permissive vs Restrictive Policies

Multiple policies on the same operation combine with OR by default (permissive). For rules that must always apply, use AS RESTRICTIVE. Restrictive policies combine with AND against all other policies.

Performance

Add an index on the tenant_id column:

CREATE INDEX idx_documents_tenant_id ON documents (tenant_id);
Enter fullscreen mode Exit fullscreen mode

Without it, every query with an RLS filter becomes a full table scan.

Common Mistakes

  • Not using FORCE ROW LEVEL SECURITY when the app connects as the table owner
  • Using SET instead of SET LOCAL with PgBouncer in transaction mode (tenant context leaks between clients)
  • Missing the index on the tenant_id column
  • Not testing cross-tenant access explicitly in your test suite

For the full guide with multi-tenant schema setup, testing patterns, EXPLAIN output, and inspecting existing policies, read the full post at rivestack.io.

Originally published at rivestack.io

Top comments (0)