Row-level security - tenant walls inside one table

Worked first (Chen’s multi-tenant notes): Chen grants SELECT, INSERT on notes to every app role. Alice’s session can read Bob’s rows. No bug in the ORM filter — the filter is optional application code. A junior ships WHERE id = $1 without tenant_id. Postgres still returns Bob’s note. Row-level security (RLS) attaches policies to the table so the database itself refuses rows outside the policy, for every query path.

Worked breach timeline

t	App-only filter	With RLS
T0	GRANT SELECT on notes to app role	same GRANT + ENABLE ROW LEVEL SECURITY
T1	Happy path: WHERE id AND tenant_id	same query (policy still holds)
T2	Junior ships WHERE id = $1 only	same buggy SQL
T3	Bob’s note returned to Alice	0 rows / not found — policy strips Bob
T4	Support incident / GDPR	Bug is a 404, not a breach

RLS does not make bad SQL “correct.” It makes the worst common failure mode fail closed.

GRANT is not tenant isolation

GRANT SELECT, INSERT ON notes TO app_user;
-- app_user can read EVERY row in notes
-- "we'll always add WHERE tenant_id = … in the app" is a hope, not a wall

Table privileges answer “may this role touch this relation?” Policies answer “which rows?” You usually need both.

Enable RLS + a tenant policy

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON notes
FOR ALL
TO app_user
USING (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
)
WITH CHECK (
  tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid
);

USING filters existing rows for SELECT/UPDATE/DELETE. WITH CHECK validates new row images for INSERT/UPDATE — so Alice cannot insert a row stamped as Bob. The second current_setting argument returns NULL instead of throwing when the GUC is missing; NULLIF also turns an empty string into NULL. The comparison is then not true, so reads expose no rows and writes fail the policy check. A malformed non-empty value still fails the UUID cast loudly at the request boundary.

Lab numbers (Podman, 30-run median)

Container til-postgres. Table owned by non-superuser rls_app. 40 alice rows + 60 bob rows. Roles rls_alice / rls_bob.

Mode	median alice visible	median bob visible	owner / empty GUC	cross-tenant INSERT rows
Open (GRANT only)	100	100	100	1 (leak)
RLS + policy	40	60	100 (owner bypass)	0
FORCE RLS + app.tenant	40	60	0 (empty GUC)	—

The recorded harness and standalone results are not included in this repository. Product claim: isolation counts + blocked leak — not a latency speedup.

Diagram: same table, different glasses

Owner bypass, FORCE, BYPASSRLS

By default the table owner bypasses RLS (so migrations work). Superusers and roles with attribute BYPASSRLS also bypass — always. If the app pool connects as owner or superuser, policies do not protect normal traffic (lab “owner 100”). FORCE ROW LEVEL SECURITY subjects the table owner to policies; it does not override superuser or BYPASSRLS.

• ALTER TABLE … FORCE ROW LEVEL SECURITY — owners become subject to policies
• App role ≠ table owner; owner only for migrations
• Audit: SELECT rolname, rolsuper, rolbypassrls FROM pg_roles on pool roles

Production request sketch (Day 8 pool)

// transaction-pooled connection checkout
await db.tx(async (t) => {
await t.none(`SELECT set_config('app.tenant_id', $1, true)`, [tenantFromJwt]);
// true = local to this transaction — resets on COMMIT/ROLLBACK
return t.any(`SELECT * FROM notes WHERE id = $1`, [id]);
// policy still applied even if WHERE forgets tenant_id
});

Under transaction pooling, never set a session-lifetime GUC and assume the next checkout is clean. Prefer set_config(…, true) inside the transaction, or reset explicitly on release. A stale non-empty GUC can expose the wrong tenant slice; a missing/empty GUC with this policy safely exposes zero rows.

Session tenant context

-- at request start (after authn)
SELECT set_config('app.tenant_id', $jwt_tenant, true);  -- local to txn
-- policy uses NULLIF(current_setting('app.tenant_id', true), '')::uuid

Empty / missing setting under this policy fails closed (lab FORCE empty → 0 rows), not open the floodgates. Invalid non-empty tenant text is different: the UUID cast errors, surfacing a broken authentication/request boundary instead of silently treating corrupt identity as “no tenant.”

PERMISSIVE vs RESTRICTIVE · command scope

Knob	Default / meaning	Watch-out
PERMISSIVE (default)	OR across permissive policies for the role/command	One wide policy undoes a tight one
RESTRICTIVE	AND with other policies — further narrows	Use to add hard denials (e.g. lock frozen tenants)
FOR ALL vs FOR SELECT…	Policies apply only to their command(s)	No applicable policy means default-deny, not wide access

Indexes: isolation then speed (Day 22)

A correct policy that seq-scans a 50M-row table on every request is still a production incident. After isolation works:

-- typical tenant + id lookup
CREATE INDEX ON notes (tenant_id, id);
-- or partial if you only serve “active” tenants
CREATE INDEX ON notes (id) WHERE tenant_id IS NOT NULL;

Policy expressions that match indexable columns help the planner. Expressions that call heavy functions per row will not. Check with EXPLAIN (Day 3) on a representative role — not only as superuser/owner who bypasses RLS.

Policies vs views vs app filters

Tool	Strength	Gap
App WHERE tenant_id	Flexible	One missed query = breach
Security barrier views	Classic; can hide columns	Easy to query the bare base table as owner
RLS on base table	Applies to subject roles on the real relation	Owner/superuser bypass; policy bugs remain bugs

Security barrier views still help for column projection and legacy apps, but they are not a substitute for RLS if clients can touch the base table. Prefer: RLS on base + narrow GRANTs + views as a convenience API.

What breaks? — Anti-patterns

• App role = table owner without FORCE — policies never bind production traffic.
• Superuser / BYPASSRLS pool — tenant walls are theater; audit pg_roles.
• A permissive write policy — WITH CHECK (true) or the wrong tenant expression lets new row images cross boundaries. If a policy has USING but omits WITH CHECK, Postgres reuses USING as the check; write it explicitly when clarity or different write rules matter.
• Assuming a SELECT policy authorizes writes — it does not. With RLS enabled, commands with no applicable policy are default-deny; add narrow INSERT/UPDATE/DELETE policies intentionally.
• One permissive “allow all” policy OR-ed with a tight one — the wide one wins.
• Session GUC under transaction pooling without reset — cross-tenant bleed or empty results.
• Assuming RLS is authn — still need login, TLS, JWT verification.
• Leaky subquery policies under concurrency — docs warn when policies read other tables; keep predicates simple or lock carefully.
• EXPLAIN only as owner — you never see the policy qual your users hit.

How it connects

• Day 8: pool role identity + txn-local GUC are the security principal policies see.
• Day 10 / GRANT: relation privileges + row policies stack.
• Days 29–34: put invariants in the database — RLS is the multi-tenant cousin of CHECK.
• Day 22 / 3: partial/composite indexes + EXPLAIN after isolation is correct.
• Day 50: OCC still matters inside a tenant’s hot row; RLS does not replace version columns.

Transfer questions

1. Alice’s role can SELECT the table and RLS is enabled, but she still sees Bob’s rows. Name two configuration mistakes.
2. You set app.tenant_id, then the pool reuses the connection for another tenant without reset. What happens under FORCE RLS?
3. What does WITH CHECK constrain on INSERT/UPDATE, and when can Postgres derive it from USING?
4. Lab open mode: alice sees 100. What single Postgres feature change drops that to 40 without rewriting every query?
5. Two PERMISSIVE policies exist: one tight tenant match, one USING (true) for “support.” What does alice see, and how do you fix it with RESTRICTIVE or role separation?

What you should be able to do

• Explain GRANT vs RLS in one sentence each.
• Write ENABLE + CREATE POLICY with USING and WITH CHECK.
• Quote the lab: open 100/100 · RLS 40/60 · cross INSERT 0 · FORCE empty 0.
• Name owner bypass, FORCE, BYPASSRLS, and txn-local GUC under pooling.
• Sketch an index that keeps tenant policies cheap at scale.

Interview version (60s)

“Table GRANT is not multi-tenant isolation — any SELECT privilege can read every row. I ENABLE ROW LEVEL SECURITY and add USING/WITH CHECK policies that pin tenant_id to a txn-local GUC. current_setting(…, true) plus NULLIF makes missing/empty identity return no rows, while malformed non-empty UUIDs still error loudly. Lab: without RLS both tenants see 100 rows; with RLS alice sees 40, bob 60, cross insert 0. FORCE applies policies to the owner, but never to superusers/BYPASSRLS. Commands without an applicable policy default-deny.”

Quiz

Questions? Ask the agent — prep inventory fills through Day 52; Day 53 Trapping remains parked. You live from Day 24 (MVCC).