jacob-balslev/skill-graph / examples/projects/saas-stripe-postgres/skills/postgres-rls-pattern
postgres-rls-pattern
UnverifiedUse when writing or reviewing Postgres queries in a multi-tenant SaaS where every table row must be scoped to a single organization. Enforces the FORCE ROW LEVEL SECURITY + USING + WITH CHECK triple on every tenant-bound table, and wraps application queries in an `orgQuery(orgId)` helper that sets `app.current_org_id` before each statement. Do NOT use for cross-org system queries such as billing cron jobs or admin panels (those bypass RLS intentionally via the service role); use a service-role query wrapper instead.
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), andWITH 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, setsapp.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 precedingSET app.current_org_idas 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 SECURITYANDFORCE ROW LEVEL SECURITY - Every DML operation (SELECT, INSERT, UPDATE, DELETE) has a corresponding policy on each table
-
WITH CHECKis present on INSERT and UPDATE policies (not justUSING) -
orgQuerysets the variable inside a transaction, not at session start - No application code calls
systemQuery(grep forsystemQueryinapps/andlib/— 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 |
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| rea-analysis-optimizationmorluto/reaOptimize REA providers and shared analysis workflows for agent context, latency, CPU, and memory while preserving correctness and Evidence. Use for analysis bottlenecks and cache or query design; CI/test-suite optimization belongs to rea-ci-tests-audit. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| rea-ci-tests-auditmorluto/reaAudit REA CI and test suites for correctness, fixture drift, false passes, and measured performance; use for suite or workflow investigations and efficiency changes, rather than ordinary feature testing or a single test failure. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| rea-tool-designmorluto/reaAudit, investigate, design, or change REA investigation tools, CLI/MCP contracts, provider capabilities, and Evidence semantics. | Stars: 464k | Updated | Claude CodeCodex | Verified | Install |
| ponytail-auditDietrichGebert/ponytailQuality audit of the whole repo: bugs, security, real load, missing tests, speed, and what to delete. Most important first. | Stars: 192k | Updated | Claude CodeCodex | Verified | Install |