Postgres Row-Level Security skill for Claude Code

RLS is done when the application connects as a role that cannot bypass it, every tenant-owned table has RLS enabled and forced with policies for each command, a missing tenant context returns zero rows instead of all rows, and an automated test proves that tenant A can neither read nor write tenant B’s rows through every path (table, view, function). RLS is a backstop under application-level filtering, not a replacement for thinking about authorization.

Workflow

- [ ] 1. Inventory tables: tenant-owned, shared/global, cross-tenant (admin)
- [ ] 2. Set up roles: owner (migrations) vs app runtime role (no BYPASSRLS, not owner)
- [ ] 3. Decide how tenant context reaches the session
- [ ] 4. Enable + force RLS, write policies per command
- [ ] 5. Fix leaks around RLS: views, functions, FKs, unique constraints
- [ ] 6. Index for policy predicates; check plans
- [ ] 7. Write isolation tests; run them in CI

1-2. Inventory and roles

-- Tables without RLS in the app schema
SELECT c.relname, c.relrowsecurity AS enabled, c.relforcerowsecurity AS forced
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
ORDER BY enabled, c.relname;

-- Roles that bypass RLS
SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolsuper OR rolbypassrls;

Who bypasses RLS:

Role	Bypasses?
Superuser	Always
Role with BYPASSRLS	Always
Table owner	Yes, unless FORCE ROW LEVEL SECURITY is set on the table
Anyone else	No

So: migrations run as an owner role; the application connects as a separate role with only DML grants.

CREATE ROLE app_user LOGIN PASSWORD '...' NOSUPERUSER NOBYPASSRLS;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

3. Tenant context

Pass the tenant per transaction with a custom setting. This works with transaction-mode poolers (PgBouncer, Supabase/Neon poolers) because it resets at commit:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);   -- true = local to this transaction
-- queries...
COMMIT;

Application wrapper (pattern, any driver/ORM):

export async function withTenant<T>(tenantId: string, fn: (tx: Tx) => Promise<T>) {
  return db.transaction(async (tx) => {
    await tx.execute(sql`select set_config('app.tenant_id', ${tenantId}, true)`);
    return fn(tx);
  });
}

• Never use session-level SET app.tenant_id with a pool: the next request on that connection inherits the previous tenant.
• Take the tenant ID from the verified session/JWT on the server, never from a request parameter the client controls.
• Read the setting defensively. After a custom setting has been set once in a session, current_setting(..., true) returns an empty string (not NULL) when unset, and ''::uuid raises an error. Wrap it once:

CREATE SCHEMA IF NOT EXISTS app;
CREATE OR REPLACE FUNCTION app.current_tenant_id() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.tenant_id', true), '')::uuid
$$;

If the tenant is unset, the function returns NULL, tenant_id = NULL is never true, and queries see zero rows: fail closed.

Supabase: tenant/user identity comes from the JWT via auth.uid() / auth.jwt(); the same rules apply (see supabase-backend for its role setup).

4. Enable, force, and write policies

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant_isolation ON projects
  AS PERMISSIVE
  FOR ALL
  TO app_user
  USING (tenant_id = (SELECT app.current_tenant_id()))
  WITH CHECK (tenant_id = (SELECT app.current_tenant_id()));

Semantics to get right:

Clause	Applies to
USING	Rows visible for SELECT, and rows that UPDATE/DELETE can target
WITH CHECK	New row values for INSERT and UPDATE (blocks moving a row to another tenant)
FOR ALL	One policy for all commands; split per command when rules differ
AS PERMISSIVE (default)	Policies OR together: any permissive policy can grant access
AS RESTRICTIVE	ANDed on top of permissive ones: every restrictive policy must pass

• RLS enabled with no policies = deny all (for non-bypassing roles).
• Omitting WITH CHECK on an ALL/UPDATE policy reuses USING; be explicit anyway.
• Role-based rules inside a tenant (only admins delete):

CREATE POLICY projects_delete_admins ON projects
  AS RESTRICTIVE FOR DELETE TO app_user
  USING (current_setting('app.role', true) = 'admin');

• Users who belong to several tenants: store memberships in a table and check with a SECURITY DEFINER function that returns the allowed tenant IDs for the current user, owned by a role that can read memberships, with SET search_path = '' and fully-qualified names.

CREATE OR REPLACE FUNCTION app.my_tenant_ids() RETURNS uuid[]
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = '' AS $$
  SELECT coalesce(array_agg(m.tenant_id), '{}')
  FROM public.memberships m
  WHERE m.user_id = nullif(current_setting('app.user_id', true), '')::uuid
$$;
-- policy: USING (tenant_id = ANY ((SELECT app.my_tenant_ids())))

5. Leaks around RLS

Path	Risk	Fix
Views	Run with the view owner’s rights; owner bypasses RLS, so the view leaks all tenants	CREATE VIEW ... WITH (security_invoker = true) (PG15+), or revoke the view from app_user
SECURITY DEFINER functions	Run as their owner, bypassing RLS if the owner does	Filter by tenant inside, or make them SECURITY INVOKER
Foreign keys	FK checks ignore RLS; tenant A can reference tenant B’s row ID	Composite FKs: FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id) with UNIQUE (tenant_id, id) on the parent
Unique constraints	A duplicate-key error reveals that a value exists in another tenant	Scope uniqueness: UNIQUE (tenant_id, slug)
Materialized views	No RLS on matviews	Do not expose to app_user, or include tenant filter in every query on it
Tables added later	New table ships without RLS	CI check (query in step 1) fails when a table in the schema lacks enabled + forced RLS
Backups / admin tools	Run as owner or superuser	Expected; restrict who has those credentials
Sequences / counts	count(*) is filtered; IDs from shared sequences reveal volume	Use UUIDs for externally visible IDs if volume is sensitive

6. Performance

• Policies add predicates to every query. Index tenant_id as the leading column of the indexes your queries use: (tenant_id, created_at DESC), (tenant_id, id).
• Wrap function calls in a scalar subquery, (SELECT app.current_tenant_id()), so the planner evaluates it once per statement (an InitPlan) instead of once per row. This matters a lot for auth.uid() on Supabase too.
• Mark helper functions STABLE (not VOLATILE); volatile functions prevent index use.
• Avoid per-row subqueries joining membership tables in USING; precompute an array via a function as above, or a tenant_id IN (SELECT ...) that the planner can hash.
• Check plans as the app role with the context set:

BEGIN;
SET LOCAL ROLE app_user;
SELECT set_config('app.tenant_id', '6f1c...-...', true);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM projects WHERE status = 'active' ORDER BY created_at DESC LIMIT 20;
ROLLBACK;

• Functions and operators that are not LEAKPROOF in a user’s WHERE clause cannot be pushed below the RLS predicate, which can change plans. Most built-in comparison operators are leakproof; custom functions are not unless declared so (superuser only).

7. Isolation tests

Plain SQL test, runnable in CI against a scratch database seeded with two tenants:

BEGIN;
SET LOCAL ROLE app_user;

-- Tenant A sees only its rows
SELECT set_config('app.tenant_id', '00000000-0000-0000-0000-00000000000a', true);
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM projects WHERE tenant_id <> '00000000-0000-0000-0000-00000000000a') THEN
    RAISE EXCEPTION 'RLS leak: tenant A sees foreign rows';
  END IF;
  IF NOT EXISTS (SELECT 1 FROM projects) THEN
    RAISE EXCEPTION 'tenant A sees nothing: fixture or policy broken';
  END IF;
END $$;

-- Tenant A cannot write into tenant B
DO $$ BEGIN
  BEGIN
    INSERT INTO projects (tenant_id, name) VALUES ('00000000-0000-0000-0000-00000000000b', 'x');
    RAISE EXCEPTION 'RLS leak: cross-tenant insert succeeded';
  EXCEPTION WHEN insufficient_privilege THEN NULL;   -- 42501: new row violates row-level security policy
  END;
END $$;

-- Cannot update or delete B's rows (silently affects 0 rows)
DO $$ DECLARE n int; BEGIN
  UPDATE projects SET name = 'pwned' WHERE tenant_id = '00000000-0000-0000-0000-00000000000b';
  GET DIAGNOSTICS n = ROW_COUNT;
  IF n <> 0 THEN RAISE EXCEPTION 'RLS leak: updated % foreign rows', n; END IF;
END $$;

-- No tenant set: zero rows, no error
SELECT set_config('app.tenant_id', '', true);
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM projects) THEN RAISE EXCEPTION 'RLS fails open without tenant'; END IF;
END $$;

ROLLBACK;

Repeat for every tenant-owned table, every view exposed to the app, and every function the app calls (pgTAP or the app’s integration test framework can generate these per table). Also run the app’s own integration tests through its normal connection path, confirming the runtime role is app_user (SELECT current_user; from the app).

Gotchas

• Testing as a superuser or the table owner without FORCE makes every policy look like it works, because RLS is skipped entirely.
• UPDATE / DELETE blocked by USING affect zero rows silently; only WITH CHECK failures raise errors. Do not rely on errors to detect isolation bugs.
• pg_dump by a non-bypassing role errors out (or with --enable-row-security dumps only visible rows); run backups as the owner.
• TRUNCATE is not subject to RLS; do not grant it to app_user.
• Logical replication and some ETL tools read as privileged roles and see everything.
• Changing a policy is DDL (ALTER POLICY / DROP POLICY + CREATE POLICY); do it in one transaction so there is no window with the policy missing.

Output: RLS change summary

**Tables covered:** projects, tasks, comments, attachments (enabled + forced)
**Runtime role:** app_user (NOBYPASSRLS, not owner); migrations run as app_owner
**Context:** set_config('app.tenant_id', ..., true) per transaction in withTenant()
**Leak fixes:** projects_summary view -> security_invoker; tasks FK now (tenant_id, project_id)
**Performance:** policies use (SELECT app.current_tenant_id()); task list p95 4.1 ms -> 4.3 ms
**Tests:** tests/rls/*.sql run in CI for all 4 tables and 2 views; CI fails on tables without RLS