postgres-rls

name	postgres-rls
description	Guides agents implementing or auditing PostgreSQL Row Level Security in a multi-tenant SaaS codebase. Covers ENABLE+FORCE pairing, USING+WITH CHECK policies, view bypass via security_invoker, SET LOCAL for connection pool safety, superuser/owner bypass detection, and materialized view risks. Do NOT use for application-level authorization logic (use nextauth-patterns), non-PostgreSQL databases, or application query tier questions (use multi-tenancy-rls for orgQuery() usage).
license	MIT
compatibility	Targets PostgreSQL 15+ with Neon serverless driver. The security_invoker view pattern requires PG15+. The SET LOCAL / set_config(key, val, true) pattern applies to any connection-pooled PostgreSQL setup. Core ENABLE+FORCE+USING+WITH CHECK rules are PG version-independent.
allowed-tools	Read Grep Glob Bash
metadata	{"grounding":"{\"domain_object\":\"PostgreSQL Row Level Security implementation in Sales Hub\",\"grounding_mode\":\"repo_specific\",\"truth_sources\":[\"../sales-hub/apps/web/src/lib/db.ts\",\"../sales-hub/db/migrations/20260218_rls_hardening.sql\",\"../sales-hub/db/migrations/20260314_rls_expansion.sql\",\"../sales-hub/db/migrations/20260314_rls_with_check_and_security_invoker.sql\",\"../sales-hub/db/migrations/20260315_enable_rls_all.sql\"],\"failure_modes\":[\"security_leak\",\"incorrect_isolation\",\"missing_force_rls\",\"missing_with_check\",\"superuser_bypass\",\"connection_pool_context_leakage\",\"view_security_invoker_missing\"],\"evidence_priority\":\"repo_code_first\"}","drift_check":"{\"last_verified\":\"2026-05-18\",\"truth_source_hashes\":{\"../sales-hub/apps/web/src/lib/db.ts\":\"d1d9988d1da4545c58f76b5f44dbd12d5323409b777920d31d5f496db31ad7cb\",\"../sales-hub/db/migrations/20260218_rls_hardening.sql\":\"b5fb6dc74e78a3fb7e77c669af4e4b4303a4e995eaf4993cb6bdcbe7bdca424f\",\"../sales-hub/db/migrations/20260314_rls_expansion.sql\":\"406e2c419b09a6866f10c508e4ff6dc1f7a1ee82b788c5e2515186561c30ffb5\",\"../sales-hub/db/migrations/20260314_rls_with_check_and_security_invoker.sql\":\"2edcb51debd4ab4e7306eb6f8908c7a725217b19962e1429728e911fc57e88fb\",\"../sales-hub/db/migrations/20260315_enable_rls_all.sql\":\"c8de6823684f84b54376284a3a51bb6b52e38acd4f5c29664be26e89e5d39570\"}}","metadata":"{\"schema_version\":6,\"version\":\"1.1.0\",\"type\":\"capability\",\"category\":\"engineering\",\"domain\":\"engineering/database-security\",\"scope\":\"codebase\",\"owner\":\"skill-graph-maintainer\",\"freshness\":\"2026-05-18\",\"drift_check\":\"{\\\\\\\"last_verified\\\\\\\":\\\\\\\"2026-05-18\\\\\\\",\\\\\\\"truth_source_hashes\\\\\\\":{\\\\\\\"sales-hub/apps/web/src/lib/db.ts\\\\\\\":\\\\\\\"d1d9988d1da4545c58f76b5f44dbd12d5323409b777920d31d5f496db31ad7cb\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260218_rls_hardening.sql\\\\\\\":\\\\\\\"b5fb6dc74e78a3fb7e77c669af4e4b4303a4e995eaf4993cb6bdcbe7bdca424f\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260314_rls_expansion.sql\\\\\\\":\\\\\\\"406e2c419b09a6866f10c508e4ff6dc1f7a1ee82b788c5e2515186561c30ffb5\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260314_rls_with_check_and_security_invoker.sql\\\\\\\":\\\\\\\"2edcb51debd4ab4e7306eb6f8908c7a725217b19962e1429728e911fc57e88fb\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260315_enable_rls_all.sql\\\\\\\":\\\\\\\"c8de6823684f84b54376284a3a51bb6b52e38acd4f5c29664be26e89e5d39570\\\\\\\"}}\",\"eval_artifacts\":\"none\",\"eval_state\":\"unverified\",\"routing_eval\":\"absent\",\"stability\":\"stable\",\"keywords\":\"[\\\\\\\"rls\\\\\\\",\\\\\\\"row-level-security\\\\\\\",\\\\\\\"postgres\\\\\\\",\\\\\\\"tenant-isolation\\\\\\\",\\\\\\\"org-id\\\\\\\",\\\\\\\"security-policy\\\\\\\",\\\\\\\"set-local\\\\\\\",\\\\\\\"view-bypass\\\\\\\",\\\\\\\"migration\\\\\\\",\\\\\\\"multi-tenant\\\\\\\",\\\\\\\"ENABLE ROW LEVEL SECURITY\\\\\\\",\\\\\\\"FORCE ROW LEVEL SECURITY\\\\\\\",\\\\\\\"security_invoker\\\\\\\",\\\\\\\"USING clause\\\\\\\",\\\\\\\"WITH CHECK\\\\\\\",\\\\\\\"superuser bypass\\\\\\\",\\\\\\\"orgQuery\\\\\\\",\\\\\\\"app.organization_id\\\\\\\",\\\\\\\"connection pool safety\\\\\\\",\\\\\\\"materialized view RLS\\\\\\\"]\",\"examples\":\"[\\\\\\\"adding RLS to a new table — what statements are required and in what order?\\\\\\\",\\\\\\\"auditing our existing RLS migrations for missing FORCE or WITH CHECK\\\\\\\",\\\\\\\"implementing a view on an RLS-protected table in PostgreSQL 15+\\\\\\\",\\\\\\\"debugging a cross-tenant data leak in a multi-tenant SaaS system\\\\\\\",\\\\\\\"ensuring connection pool context (SET LOCAL) does not leak tenant state between requests\\\\\\\",\\\\\\\"reviewing a migration that enables RLS to check for superuser and owner bypass risks\\\\\\\",\\\\\\\"verifying that materialized views over RLS tables are documented as security-sensitive\\\\\\\"]\",\"anti_examples\":\"[\\\\\\\"implementing application-level auth guards (requireAuth, requireOrgAuth) — use nextauth-patterns\\\\\\\",\\\\\\\"choosing when to use orgQuery() vs query() in application code — use multi-tenancy-rls\\\\\\\",\\\\\\\"implementing CSRF protection or webhook HMAC verification — use security-scanning\\\\\\\",\\\\\\\"designing the overall multi-tenant data architecture — use data-architect\\\\\\\",\\\\\\\"designing row-level access control in non-PostgreSQL databases\\\\\\\"]\",\"relations\":\"{\\\\\\\"adjacent\\\\\\\":[\\\\\\\"database-migration\\\\\\\",\\\\\\\"security-scanning\\\\\\\",\\\\\\\"data-architect\\\\\\\",\\\\\\\"guardrails\\\\\\\"],\\\\\\\"boundary\\\\\\\":[{\\\\\\\"skill\\\\\\\":\\\\\\\"nextauth-patterns\\\\\\\",\\\\\\\"reason\\\\\\\":\\\\\\\"nextauth-patterns owns application-level authorization (requireAuth, requireOrgAuth, withOrgAuth); postgres-rls owns database-level isolation via PostgreSQL RLS policies\\\\\\\"},{\\\\\\\"skill\\\\\\\":\\\\\\\"multi-tenancy-rls\\\\\\\",\\\\\\\"reason\\\\\\\":\\\\\\\"multi-tenancy-rls owns the application query tier (orgQuery, query, withAppSession usage patterns); postgres-rls owns the PostgreSQL server-side RLS policy implementation\\\\\\\"}],\\\\\\\"verify_with\\\\\\\":[\\\\\\\"security-scanning\\\\\\\",\\\\\\\"database-migration\\\\\\\"]}\",\"grounding\":\"{\\\\\\\"domain_object\\\\\\\":\\\\\\\"PostgreSQL Row Level Security implementation in Sales Hub\\\\\\\",\\\\\\\"grounding_mode\\\\\\\":\\\\\\\"repo_specific\\\\\\\",\\\\\\\"truth_sources\\\\\\\":[\\\\\\\"sales-hub/apps/web/src/lib/db.ts\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260218_rls_hardening.sql\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260314_rls_expansion.sql\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260314_rls_with_check_and_security_invoker.sql\\\\\\\",\\\\\\\"sales-hub/db/migrations/20260315_enable_rls_all.sql\\\\\\\"],\\\\\\\"failure_modes\\\\\\\":[\\\\\\\"security_leak\\\\\\\",\\\\\\\"incorrect_isolation\\\\\\\",\\\\\\\"missing_force_rls\\\\\\\",\\\\\\\"missing_with_check\\\\\\\",\\\\\\\"superuser_bypass\\\\\\\",\\\\\\\"connection_pool_context_leakage\\\\\\\",\\\\\\\"view_security_invoker_missing\\\\\\\"],\\\\\\\"evidence_priority\\\\\\\":\\\\\\\"repo_code_first\\\\\\\"}\",\"portability\":\"{\\\\\\\"readiness\\\\\\\":\\\\\\\"declared\\\\\\\",\\\\\\…","skill_graph_source_repo":"https://github.com/jacob-balslev/skill-graph","skill_graph_protocol":"Skill Metadata Protocol v4","skill_graph_project":"Skill Graph","skill_graph_canonical_skill":"skills/postgres-rls/SKILL.md"}

PostgreSQL Row Level Security

Domain Context

What is postgres-rls? Guides agents implementing or auditing PostgreSQL Row Level Security in Sales Hub. Covers ENABLE+FORCE pairing, USING+WITH CHECK policies, view bypass via security_invoker, SET LOCAL for connection pool safety, superuser/owner bypass detection, and materialized view risks.

Key Files

File	Purpose
apps/web/src/lib/db.ts	orgQuery(), query(), withReportingCurrency(), withAppSession() wrapper pattern (lines 1-895)
db/migrations/20260218_rls_hardening.sql	RLS hardening migration — ENABLE+FORCE patterns
db/migrations/20260314_rls_expansion.sql	RLS expansion to additional tables
db/migrations/20260314_rls_with_check_and_security_invoker.sql	WITH CHECK and security_invoker additions
db/migrations/20260315_enable_rls_all.sql	Full RLS enablement across all tables

Project-Specific Rules

This skill captures project-local rules and constraints that narrow or refine a broader pattern. Treat the repo-specific guidance and key files below as authoritative for this project.

Sales Hub uses org_id (not tenant_id) as the tenant column. Session variable is app.organization_id. All application queries go through orgQuery() (auto-sets session variable) or query() (system-only, bypasses RLS). The db.ts module manages a connection pool via pg.Pool; RLS context is set per-transaction using set_config('app.organization_id', orgId, true) (the third parameter true makes it transaction-local, equivalent to SET LOCAL).

Coverage

This skill covers PostgreSQL Row Level Security implementation and auditing in Sales Hub: ENABLE+FORCE pairing, USING+WITH CHECK policies, view bypass via security_invoker, SET LOCAL for connection pool safety, superuser/owner bypass detection, materialized view risks, the orgQuery() wrapper pattern, and the app.organization_id session variable convention.

Philosophy

RLS is the last line of defense against cross-tenant data leaks. When agents implement it incorrectly (missing FORCE, missing WITH CHECK, using SET instead of SET LOCAL), the system appears to work in testing but silently leaks data in production. This skill captures the exact footguns that have caused or nearly caused data breaches in multi-tenant SaaS systems.