Postgres Rls

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

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.

Overview

Row Level Security (RLS) provides defense-in-depth for data isolation. When implemented correctly, it prevents data leaks even if application code misses a filter. When implemented incorrectly, it creates false security confidence while data bleeds between tenants.

Core principle: RLS is your last line of defense, not your only one. Get it wrong and you have a data breach.

Announce at start: "I'm applying postgres-rls to verify Row Level Security implementation."

When This Skill Applies

This skill is MANDATORY when ANY of these patterns are touched:

Check with:

git diff --name-only HEAD~1 | grep -iE '(tenant|rls|policy|auth.*sql|multi.?tenant)'

Sales Hub Specifics

Sales Hub uses org_id (not tenant_id) as the tenant column. Session variable is app.organization_id.

Current state (2026-03-14): 22 tables with RLS. Policies use:

CREATE POLICY {table}_org_isolation ON {table}
  FOR ALL
  USING (org_id IS NOT NULL AND org_id = (current_setting('app.organization_id', true))::uuid)
  WITH CHECK (org_id IS NOT NULL AND org_id = (current_setting('app.organization_id', true))::uuid);

Query wrappers (in apps/web/src/lib/db.ts — orgQuery, query, withAppSession):

• orgQuery(orgId, sql, params) — auto-sets session variable + validates (preferred)
• query(sql, params) — raw query without validation (system operations only)
• withAppSession(settings, fn) — manual session variable injection

Views: All views use ALTER VIEW ... SET (security_invoker = true) (PG15+).

The Critical Vulnerabilities

1. Superuser Bypass (CRITICAL)

Superusers and roles with BYPASSRLS ignore ALL policies.

-- DANGEROUS: Testing as superuser shows RLS "working" when it's bypassed
SET ROLE postgres;
SELECT * FROM orders;  -- Returns ALL rows, RLS ignored

-- CORRECT: Test as application role
SET ROLE app_user;
SELECT * FROM orders;  -- Returns only permitted rows

Checklist:

• Application connects as non-superuser role
• No roles have BYPASSRLS attribute
• Tests run as application role, NOT superuser

2. Table Owner Bypass (CRITICAL)

Table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set.

-- INCOMPLETE: Owners bypass this
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- COMPLETE: Everyone including owners must obey policies
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

Checklist:

• All RLS tables have both ENABLE and FORCE
• Migration includes both statements

3. View Bypass (CRITICAL)

Views run with creator's privileges by default. Views owned by superusers bypass RLS entirely.

-- DANGEROUS: View owned by superuser bypasses RLS
CREATE VIEW all_orders AS SELECT * FROM orders;

-- SAFE (PostgreSQL 15+): Security invoker respects caller's RLS
CREATE VIEW user_orders
WITH (security_invoker = true)
AS SELECT * FROM orders;

-- Or retrofit existing views:
ALTER VIEW user_orders SET (security_invoker = true);

Checklist:

• All views on RLS tables use security_invoker = true (PG15+)
• Views not owned by superuser roles
• Materialized views documented as bypassing RLS

4. USING vs WITH CHECK Mismatch (HIGH)

USING filters reads; WITH CHECK validates writes. Missing WITH CHECK allows inserting data you can't see.

-- INCOMPLETE: User can INSERT rows they can't SELECT
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- COMPLETE: Both read and write protected
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

Checklist:

• All policies have both USING and WITH CHECK
• WITH CHECK logic matches security intent

5. Thread-Local Context Leakage (HIGH)

Connection pooling can leak tenant context between requests.

-- DANGEROUS: Context persists across pooled connections
SET app.tenant_id = 'tenant-123';

-- SAFE: Use SET LOCAL inside transaction (auto-resets)
BEGIN;
SET LOCAL app.tenant_id = 'tenant-123';
-- ... queries ...
COMMIT;  -- Context automatically cleared

Application pattern:

// DANGEROUS: Leaks between requests
await db.query(`SET app.tenant_id = '${tenantId}'`);

// SAFE: Transaction-scoped context (Sales Hub pattern)
await client.query("SELECT set_config('app.organization_id', $1, true)", [orgId]);
// The `true` parameter = transaction-local, equivalent to SET LOCAL

Checklist:

• Always use SET LOCAL or set_config(key, val, true)
• Context set inside transactions
• Post-request handler resets context (defense in depth)

6. SQL Injection in Policy Functions (HIGH)

Functions used in policies can be injection vectors.

-- The function must be injection-safe:
CREATE OR REPLACE FUNCTION current_tenant()
RETURNS uuid AS $$
BEGIN
  -- SAFE: Casts to UUID, not string concatenation
  RETURN current_setting('app.tenant_id')::uuid;
END;
$$ LANGUAGE plpgsql STABLE;

7. Materialized Views and Data Export (MEDIUM)

Materialized views don't respect source table RLS. Data exports may bypass policies.

Checklist:

• Materialized views documented as security-sensitive
• Export jobs run as application role
• Audit log for bulk data access

Migration Pattern

Safe RLS Migration

-- Step 1: Add column (if needed)
ALTER TABLE orders ADD COLUMN IF NOT EXISTS org_id uuid REFERENCES organizations(id);

-- Step 2: Backfill data (batched for large tables)
UPDATE orders SET org_id = (...) WHERE org_id IS NULL;

-- Step 3: Create index
CREATE INDEX IF NOT EXISTS idx_orders_org_id ON orders(org_id);

-- Step 4: Enable RLS (both statements!)
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Step 5: Create policies with USING and WITH CHECK
DROP POLICY IF EXISTS orders_org_isolation ON orders;
CREATE POLICY orders_org_isolation ON orders
  FOR ALL
  USING (org_id IS NOT NULL AND org_id = (current_setting('app.organization_id', true))::uuid)
  WITH CHECK (org_id IS NOT NULL AND org_id = (current_setting('app.organization_id', true))::uuid);

-- Step 6: Verify
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename = 'orders' AND policyname = 'orders_org_isolation') THEN
    RAISE EXCEPTION 'ASSERTION FAILED: RLS policy not found on orders';
  END IF;
END; $$;

Verification

Before completing RLS implementation:

• All tables have ENABLE and FORCE ROW LEVEL SECURITY
• All policies have both USING and WITH CHECK
• Application connects as non-superuser, non-BYPASSRLS role
• Context set with SET LOCAL / set_config(key, val, true) inside transactions
• Views use security_invoker = true (PG15+)
• Policy columns indexed
• Cross-tenant isolation tests passing

References

• PostgreSQL RLS Documentation
• Common RLS Footguns
• RLS Performance Optimization

Do NOT Use When