Postgres RLS Pattern

Concept of the skill

What it is: The database-enforced tenant isolation pattern for Postgres tables in a multi-organization SaaS. Mental model: The application sets the current organization; Postgres enforces which rows that organization can read or write. Why it exists: A missed WHERE org_id = ... clause should not become a cross-tenant data leak. What it is NOT: It is not a service-role migration pattern, admin reporting bypass, or generic SQL optimization guidance. Adjacent concepts: Row-level policies, session variables, service-role isolation, tenant-bound tables. One-line analogy: It is a database lock that opens only for the current organization. Common misconception: Application-level filters are equivalent to RLS; RLS moves the guardrail into the database itself.

Coverage

• The three-part policy triple — FORCE ROW LEVEL SECURITY, USING (org_id = current_setting('app.current_org_id')::uuid), and WITH CHECK (org_id = current_setting('app.current_org_id')::uuid) — and why omitting any one part leaves a gap
• The orgQuery(orgId) application wrapper — a single function that opens a transaction, sets app.current_org_id, runs the caller's query, and commits; why setting the variable once at session start is unsafe under connection pooling
• Service role bypass — legitimate cross-org operations (billing cron, admin panel, migration backfills) that must use a connection string that skips RLS, and why those code paths must be isolated from application code
• Policy audit checklist — grepping for query() calls without a preceding SET app.current_org_id as a CI-safe audit gate
• New-table checklist — steps to add RLS to a table that was created before RLS was enforced on the schema