the-custodian/research/2026-08-17-adr008-plane-e-enforcement.md

# Plane E — Enforcement: prior knowledge and best practices

> Research artefact — 2026-08-17. Supports `canon/architecture/adr-008-multi-tenancy-model.md`
> (draft-2), plane E of four. Surveys external practice for where the tenant
> boundary is mechanically enforced, and holds our `E0–E4` ladder against it.
> **This digest contains a correction to the ADR's own definition of E3.**
> Companions: plane I (identity), plane A (authorization), plane P (placement).

## 1. What the plane covers

Where the tenant boundary actually holds, and what defeats it. Our ladder:

| Level | Mechanism | ADR claims it is defeated by |
|---|---|---|
| E0 | None | Anything |
| E1 | Tenant-keyed, filtered per query | One missing predicate |
| E2 | Single service-side choke point | Code bypassing the choke point |
| E3 | RLS keyed on a session GUC set at pool checkout | `SECURITY DEFINER`, missing policy, wrong GUC |
| E4 | Per-tenant credential and substrate | Platform-level compromise |

## 2. Correction: E3's description is wrong, and the error matters

ADR-008 draft-2 describes E3 as "platform-assisted: row-level security keyed on
a session GUC set at pool checkout, or an equivalent data-access layer **the
application cannot trivially route around**".

That last clause is false for the GUC mechanism, and the literature is blunt
about it:

> "Any session can `SET` any custom parameter. RLS policies that rely on
> `current_setting('app.tenant_id')` are only as secure as the application's
> control over SQL execution. Users with direct access can trivially
> `SET app.tenant_id = '999'` and bypass the policy."
> — [kmoppel — Yes, Postgres can do session vars, but should you use them?](https://kmoppel.github.io/2025-06-03-yes-postgres-can-do-session-variables-but-should-you-use-them/)

And the corollary: "SQL injection could enable an attacker to issue their own
`SET` command, therefore accessing other customer's data. The session variable
based approach is only safe when you protect yourself against SQL injections"
(ibid.).

**What this means for the framework.** Draft-1's instinct was not baseless
after all — but neither was draft-2's reversal. The honest resolution is that
E3's strength is **threat-model dependent**, and the ladder must say so:

| Threat | Does E3 help? |
|---|---|
| Developer forgets a `WHERE tenant_id = ?` | **Yes, decisively.** This is the common case and the reason E3 exists. |
| A new code path bypasses the repository/choke point | **Yes.** The database still filters. |
| SQL injection reaching the connection | **No.** The attacker can re-`SET` the GUC. |
| Compromised application process | **No.** It holds a credential that can address all tenants. |

So E3 is a strong control against **accident** and a weak one against
**compromise**. E4 is the only rung that holds against both, because the
credential itself cannot address another tenant's data.

**Proposed amendment to §4.3 of the ADR:** replace "the application cannot
trivially route around" with an explicit threat-model row. The current wording
overclaims in precisely the way §6 of the ADR prohibits — an irony worth fixing
before reviewers find it.

## 3. Three PostgreSQL mechanics that will bite our specific role model

These are not generic warnings; each interacts with `rapp-postgres` ADR-0001 as
built.

### 3.1 The table owner bypasses RLS silently — and our migration role owns tables

> "By default, the table owner bypasses RLS policies unless the table is
> altered with `FORCE ROW LEVEL SECURITY`."
> — [pganalyze — RLS, security invoker views and LEAKPROOF functions](https://pganalyze.com/blog/5mins-postgres-row-level-security-bypassrls-security-invoker-views-leakproof-functions)

> "The `FORCE` keyword is critical — without it, the table owner bypasses
> policies silently."
> — [Kawshik — securing pgvector with PostgreSQL RLS](https://kawshik.dev/blog/multi-tenant-rag-pgvector-postgres-rls.html)

ADR-0001 already discovered — via `isolation-test.sh`, not review — that
**objects created by `<consumer>_migrate` are owned by `_migrate`**, not by
`_owner`, because default privileges key on the creating role. That finding was
about grants. It has a second consequence nobody has drawn: under RLS, the
`_migrate` role would silently bypass every policy on every table it created.

Add `BYPASSRLS` to the picture — roles with that attribute are exempt
([oneuptime — securing multi-tenant data with RLS](https://oneuptime.com/blog/post/2026-01-25-row-level-security-postgresql/view))
— and the E3 provisioning contract needs to state explicitly: `FORCE ROW LEVEL
SECURITY` on every tenant-owned table, no `BYPASSRLS` on any leased role, and
the runtime role must not own the objects (which ADR-0001 already guarantees,
for a different reason).

### 3.2 Connection pooling can silently serve the wrong tenant's rows

This is the failure mode most likely to reach production undetected.

> "RLS with `SET` or `SET LOCAL` will not work properly with pgbouncer in
> statement pooling mode; you will likely return rows for the wrong users and
> it may only happen in production when multiple people are hitting the app at
> once." — [Daniel Imfeld — PostgreSQL row level security](https://imfeld.dev/notes/postgresql_row_level_security)

> "In highly concurrent environments with connection pooling (like pgbouncer),
> managing this context correctly is a major source of subtle, hard-to-debug
> bugs. If the context leaks between connections, so might the data."
> — [ShiftAsia — an intro to PostgreSQL's RLS](https://shiftasia.com/community/why-your-database-needs-boundaries-an-intro-to-postgresqls-row-level-security-rls/)

The mitigation is consistent across sources: set the context **transaction-locally**
(`SET LOCAL` inside an explicit transaction), never session-locally, so pooling
mode cannot leak it. Our ADR says "set at pool checkout", which is the
session-scoped phrasing and the wrong one.

**Against our model:** §16 of the ADR notes that E3 "forecloses aggressive
transaction-level pooling". That is backwards — transaction-scoped context is
what makes E3 *safe* under pooling; it is **statement**-level pooling that is
incompatible. Correct the scaling section as well as §4.3.

This also sharpens the ADR's own §12 point about designing guarding for
invisible failure: an RLS context leak produces correct-looking rows for the
wrong tenant. No error, no log line, no crash.

### 3.3 RLS silently disables some indexes

> "Due to security concerns, functional indices using leakproof functions
> cannot be used for RLS-secured queries. Functions such as `lower` or
> `enum_eq` are not leakproof. This means indices such as
> `some_table(lower(email))` will not be used when RLS is active."
> — [sigmoid.at — notes on PostgreSQL row level security](https://sigmoid.at/post/2021/09/23/postgresql_row_level_security/)

A service adopting E3 may see specific queries fall off an index with no schema
change. Relevant to `tenant-engine` in particular, which sits on `flex-auth`'s
synchronous authorization path and just requested a 5s statement timeout on
exactly that basis.

**Proposed addition:** the E3 provisioning contract (ADR §18.6) should require
an EXPLAIN comparison before and after enabling RLS, not just a correctness
probe.

## 4. The verification gap has off-the-shelf answers

ADR §13.2 calls the missing E2/E3 evidence artifacts the framework's largest
live gap and asks who should build them. The research says: less building than
expected.

- **pgTAP** is the established way to assert RLS behaviour in CI. "RLS policies
  should be tested in CI with pgTAP, not just in dev, since policy regressions
  are silent (no query error, just wrong data returned), and automated
  cross-tenant isolation tests are the only reliable guard"
  ([Blair Jordan — testing RLS policies with pgTAP](https://blair-devmode.medium.com/testing-row-level-security-rls-policies-in-postgresql-with-pgtap-a-supabase-example-b435c1852602),
  [MakerKit — database testing with pgTAP](https://makerkit.dev/docs/next-supabase-turbo/development/database-tests)).
- **pgrls** is a purpose-built static analyzer, **verified directly 2026-08-17**
  rather than taken from a search snippet
  ([pgrls on GitHub](https://github.com/pgrls/pgrls)). Confirmed: MIT licence;
  67 rules across SEC (54), PERF (5), HYG (4) and VIEW (4); 19 auto-fixable;
  Z3 SMT-solver verification of isolation properties; a policy-diff command
  classifying changes SAFE / BREAKING / REQUIRES_REVIEW / DANGEROUS; a
  `pgrls.testing` pytest plugin with role-switching and per-test transactions;
  SARIF, JUnit XML and GitHub-annotation output; tested against PostgreSQL
  15–17. Its SEC rules cover exactly the traps in §3 — BYPASSRLS, LEAKPROOF,
  `SECURITY DEFINER`, cross-tenant leaks.

  **Maturity caveat, stated because this digest recommends adoption.** It is
  26 stars, 2 forks, 527 commits, self-described "Beta — actively maintained".
  The engineering is serious (Z3 proofs, a precision corpus) but this is a
  small project, not an established dependency. Adopt the *approach* with
  confidence; treat the *tool* as a supply-chain decision requiring the estate's
  normal SBOM and pinning discipline, and be prepared to reimplement its
  handful of load-bearing rules if it goes unmaintained. pgTAP, by contrast, is
  long-established and carries no such caveat.

The named common failures match ours exactly: "a forgotten role, a pool reusing
connections with the wrong tenant context, or an admin path bypassing RLS
entirely" ([Kawshik](https://kawshik.dev/blog/multi-tenant-rag-pgvector-postgres-rls.html)).

**Against our model:** the E3 evidence artifact in §13 ("RLS policies present on
every tenant-owned table; probe that a session without the GUC reads nothing;
probe that a wrong GUC reads nothing") is well specified and is roughly what
pgrls lints for plus what pgTAP asserts. `rapp-postgres` already runs
`scripts/isolation-test.sh` with 15 probes against a throwaway PostgreSQL 16 in
Docker — the same harness shape, pointed at the consumer boundary. Extending it
to the tenant boundary is incremental, not novel.

**This materially changes the answer to open question §18.3.** The E3 half is
tooling-assisted and cheap. The E2 half remains semantic and human (see plane A
§7). Splitting the question that way makes it assignable.

## 5. The layered consensus: RLS as defence in depth, not as the only layer

Vendor and community guidance converges on the same layering our ADR describes,
which is reassuring for the ladder's shape even where the details need fixing.

- AWS documents RLS as the mechanism for pooled multi-tenant isolation and
  pairs it with a non-owner application role
  ([AWS — multi-tenant data isolation with PostgreSQL RLS](https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/)).
- OWASP lists RLS under database-level enforcement and application-layer ORM
  filters as *complementary*, recommending "database-level isolation as
  defense-in-depth" rather than as a replacement
  ([OWASP Multi-Tenant Security Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/Multi_Tenant_Security_Cheat_Sheet.html)).
- The application-layer half is a custom ORM/repository that automatically
  appends tenant filters and injects `tenant_id` on write (ibid.) — which is
  precisely our E2.
- "Use `SECURITY INVOKER` for typical multi-tenant logic so that the caller's
  RLS context applies"; reserve `SECURITY DEFINER` for deliberate, controlled
  cross-tenant access
  ([oneuptime](https://oneuptime.com/blog/post/2026-01-25-row-level-security-postgresql/view)).

**Against our model:** the E ladder is ordinal — each rung replacing the last —
but practice treats E2 and E3 as **layers that coexist**. A service at E3 that
dropped its E2 choke point would be worse off, not better, because E3 alone
fails open under SQL injection (§2).

**Proposed amendment:** state that E3 *presupposes* E2 rather than superseding
it, and that a claim of E3 requires the E2 evidence artifact as well. That is a
small change with real consequences for how the ladder is read — it converts
"move up a rung" into "add a layer", which is what the sources actually
describe.

## 6. Summary against our ladder

| Finding | Verdict |
|---|---|
| Any session can re-`SET` the GUC; E3 fails against SQL injection and app compromise | **Correction.** E3's "cannot trivially route around" overclaims. Add a threat-model table. |
| E3 is decisive against forgotten predicates — the common case | **Confirms the reversal.** Draft-2's direction is right, its wording is not. |
| Table owner bypasses RLS silently without `FORCE ROW LEVEL SECURITY` | **Specific risk.** Our `_migrate` role owns the tables it creates (ADR-0001). |
| `BYPASSRLS` roles are exempt | Provisioning contract must forbid it on leased roles. |
| Statement-pooling + `SET` returns other tenants' rows, only under production concurrency | **Correction.** Use `SET LOCAL` in-transaction; ADR §16's pooling claim is backwards. |
| RLS disables functional indexes using non-leakproof functions | **New.** Require an EXPLAIN comparison in the E3 contract. |
| pgTAP + pgrls (67 lint rules, policy-diff CI gate, pytest plugin) exist | **Adopt.** §18.3's E3 half is far cheaper than assumed. |
| Policy regressions are silent — wrong data, no error | **Confirms §12.** Guarding must target invisible failure. |
| Practice treats E2 and E3 as coexisting layers, not successive rungs | **Amend.** E3 should presuppose E2, not replace it. |

## Sources

- [kmoppel — Yes, Postgres can do session vars, but should you use them?](https://kmoppel.github.io/2025-06-03-yes-postgres-can-do-session-variables-but-should-you-use-them/)
- [Daniel Imfeld — PostgreSQL row level security (notes)](https://imfeld.dev/notes/postgresql_row_level_security)
- [pganalyze — RLS in Postgres, security invoker views and why LEAKPROOF functions matter](https://pganalyze.com/blog/5mins-postgres-row-level-security-bypassrls-security-invoker-views-leakproof-functions)
- [AWS Database Blog — Multi-tenant data isolation with PostgreSQL Row Level Security](https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/)
- [oneuptime — How to secure multi-tenant data with RLS in PostgreSQL](https://oneuptime.com/blog/post/2026-01-25-row-level-security-postgresql/view)
- [sigmoid.at — Notes on PostgreSQL row level security](https://sigmoid.at/post/2021/09/23/postgresql_row_level_security/)
- [ShiftAsia — Why your database needs boundaries: an intro to PostgreSQL's RLS](https://shiftasia.com/community/why-your-database-needs-boundaries-an-intro-to-postgresqls-row-level-security-rls/)
- [pgEdge — It depends: using session variables in Postgres](https://www.pgedge.com/blog/it-depends-using-session-variables-in-postgres)
- [Kawshik — The multi-tenant RAG nightmare: securing pgvector with PostgreSQL RLS](https://kawshik.dev/blog/multi-tenant-rag-pgvector-postgres-rls.html)
- [pgrls — static analyzer for Postgres Row-Level Security](https://github.com/pgrls/pgrls)
- [Blair Jordan — Testing RLS policies in PostgreSQL with pgTAP](https://blair-devmode.medium.com/testing-row-level-security-rls-policies-in-postgresql-with-pgtap-a-supabase-example-b435c1852602)
- [MakerKit — Database testing with pgTAP](https://makerkit.dev/docs/next-supabase-turbo/development/database-tests)
- [Rico Fritzsche — Mastering PostgreSQL RLS for rock-solid multi-tenancy](https://ricofritzsche.me/mastering-postgresql-row-level-security-rls-for-rock-solid-multi-tenancy/)
- [pganalyze — Using Postgres row-level security in Ruby on Rails](https://pganalyze.com/blog/postgres-row-level-security-ruby-rails)
- [OWASP — Multi-Tenant Security Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/Multi_Tenant_Security_Cheat_Sheet.html)