Postgres rls pattern

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

Philosophy of the skill

Row-level security on Postgres is the difference between "we checked org_id in the WHERE clause" and "the database rejects cross-org reads at the storage layer." Application-level checks are deleted by a single missing WHERE clause; RLS cannot be bypassed unless you use the service role explicitly. The cost is a session variable that must be set before every query and a discipline of never using the service role for application queries. Both costs are cheap relative to the consequence of a cross-tenant data leak.

Schema Pattern

-- 1. Enable and force RLS on every tenant-bound table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- 2. SELECT policy — only rows where org_id matches the session variable
CREATE POLICY orders_org_select ON orders
  FOR SELECT
  USING (org_id = current_setting('app.current_org_id', true)::uuid);

-- 3. INSERT policy — only allow inserts that match the session variable
CREATE POLICY orders_org_insert ON orders
  FOR INSERT
  WITH CHECK (org_id = current_setting('app.current_org_id', true)::uuid);

-- 4. UPDATE policy — USING (read filter) AND WITH CHECK (write filter)
CREATE POLICY orders_org_update ON orders
  FOR UPDATE
  USING (org_id = current_setting('app.current_org_id', true)::uuid)
  WITH CHECK (org_id = current_setting('app.current_org_id', true)::uuid);

-- 5. DELETE policy
CREATE POLICY orders_org_delete ON orders
  FOR DELETE
  USING (org_id = current_setting('app.current_org_id', true)::uuid);

Application Wrapper Pattern

// lib/db.ts
import postgres from "postgres";

const sql = postgres(process.env.DATABASE_URL!);

/** Tenant-scoped query: sets app.current_org_id for every statement. */
export async function orgQuery<T>(
  orgId: string,
  fn: (sql: postgres.Sql) => Promise<T>
): Promise<T> {
  return sql.begin(async (tx) => {
    await tx`SELECT set_config('app.current_org_id', ${orgId}, true)`;
    return fn(tx);
  });
}

/** System query: bypasses RLS. Use ONLY for cron jobs, migrations, and admin. */
export async function systemQuery<T>(fn: (sql: postgres.Sql) => Promise<T>): Promise<T> {
  return fn(sql);
}

Usage in a Server Action:

import { orgQuery } from "@/lib/db";

export async function getOrders(orgId: string) {
  return orgQuery(orgId, (tx) => tx`SELECT * FROM orders ORDER BY created_at DESC`);
}

Verification

• Every tenant-bound table has ENABLE ROW LEVEL SECURITY AND FORCE ROW LEVEL SECURITY
• Every DML operation (SELECT, INSERT, UPDATE, DELETE) has a corresponding policy on each table
• WITH CHECK is present on INSERT and UPDATE policies (not just USING)
• orgQuery sets the variable inside a transaction, not at session start
• No application code calls systemQuery (grep for systemQuery in apps/ and lib/ — any hit is a finding)
• Every new migration that adds a table includes the RLS policy triple in the same migration file

Do NOT Use When

Use instead	When
systemQuery wrapper	The query legitimately crosses org boundaries (billing cron, migration backfill, admin panel)
migrate-orders-to-canonical-schema	The task is a schema migration that also needs to update RLS policies
(a database skill without multi-tenancy scope)	The application is single-tenant and org_id isolation is not a requirement