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