RLS emitter: generate and drift-gate Postgres row-security policies from metadata (post-1.0)

[dmealing]

Summary

Emit and drift-gate Postgres Row-Level Security policies from metadata: CREATE POLICY + ALTER TABLE ... ENABLE ROW LEVEL SECURITY + FORCE ROW LEVEL SECURITY as generated DDL, owned by migrate-ts and drift-checked by meta verify --db.

This is the post-1.0 shape we should converge on for row-level isolation, and the honest alternative to #196's scope.row (declined for core — see that thread for the full reasoning).

Why this is in charter when app-level scoping is not

CREATE POLICY is DDL. migrate-ts owns DDL. verify --db already drift-gates DDL. So the claim becomes bounded and true:

"MetaObjects generates and drift-gates your Postgres row-security policies."

Not "MetaObjects enforces tenant isolation in five languages." The difference matters enormously:

• Postgres enforces it, not us. It survives raw SQL, background jobs, an ORM bypass, a hand-written query, and a port whose generated repository is only an interface (Java, Python). No port can weaken it.
• It is single-dialect and honest about it. RLS does not exist on SQLite or D1; the docs say so rather than pretending.
• It plays to the actual strength of the project. RLS is a multi-artifact invariant — policy + ENABLE + FORCE + role topology — where every artifact fails silently and in a different direction. That is precisely the class of problem a drift-detecting metadata standard exists to own, and precisely the class humans get wrong.

The traps this must handle (all verified against the PG docs)

These are the reasons hand-written RLS fails, and they define what the emitter and the drift gate have to check:

1. Policies without ENABLE are silently ignored. "Policies can exist for a table even if row-level security is disabled. In this case, the policies will not be applied." So the emitter must always pair them, and verify --db must flag a policy whose table has RLS disabled.

2. The table-owner exemption — and this one is fatal for a migration tool. Table owners bypass RLS unless FORCE ROW LEVEL SECURITY is set. The role that runs migrations owns the tables. If migration-role == runtime-role, every policy we emit is dead code — and the tests pass, because you tested as the owner. The emitter should default to FORCE, and we should document (loudly) that the runtime role must not be the table owner.

3. Views bypass RLS by default. A view executes as its owner, and owners bypass. We generate CREATE VIEW for every projection. On PG < 15 (no security_invoker) every generated projection view is an RLS hole. The emitter must set security_invoker = true on generated views where the base table has RLS, and refuse/warn below PG 15.

4. SET vs SET LOCAL under connection pooling. PgBouncer transaction mode does not support session-level SET, so a session-scoped app.tenant_id survives on a recycled backend and the next client inherits it. Only BEGIN; SET LOCAL ...; is safe.

5. Referential integrity always bypasses RLS. Unique/PK/FK checks are exempt, so a duplicate-key error can leak the existence of another tenant's row. Worth documenting as a known limit — perfect RLS still has a covert channel.

6. Performance is a footgun with a huge multiplier. An unwrapped current_setting() in a policy is evaluated per-row; wrapping it as (SELECT current_setting(...)) turns it into an InitPlan. Reported Supabase numbers on 100k rows: 179ms → 9ms from that change alone, and 178,000ms → 12ms when a policy join was replaced with a helper. The emitter should generate the InitPlan-wrapped form by default and ensure the policy column is indexed.

Sketch

The declaration of which column partitions the rows is not derivable — that's the one irreducible bit (same conclusion as #196). Whether it lives in core vocabulary or an adopter's own provider is exactly the open question, and it should be settled by evidence, not by this issue.

Rough shape, to be designed properly:

• Metadata declares the partition column and the session setting it binds to.
• migrate-ts emits ENABLE + FORCE + CREATE POLICY ... USING (tenant_id = (SELECT current_setting('app.tenant_id')::uuid)), plus security_invoker on dependent generated views.
• meta verify --db gates the whole invariant: policy present, RLS enabled, FORCE set, view invoker mode correct, policy column indexed.
• Docs state plainly what is not covered (SQLite/D1; the RI covert channel; the fact that the app still has to set the value per transaction).

Gating

Post-1.0, and blocked on evidence. Per docs/features/downstream-metadata-decisions.md, one consumer isn't enough to add core vocabulary — a second independent consumer is the promotion trigger. We're at n=1 (#196). This issue exists to record the right target so downstream vocabulary can be shaped to fold into it later, not to schedule it now.

Related: #196 (declined for core), #198 (full-row update(model) will write a client-supplied tenant column — a write-side hole that exists regardless of RLS).