# ADR-128: PostgREST Access Rides on a Shared web_anon/authenticator Role Model — Per-PWA Postgres Roles Do Not Exist

## Status

Proposed, 2026-08-01.

## Status History

```yaml
status_history:
  - date: 2026-08-01
    status: Proposed
    changed_by: hkl (via DL debt-cleanup plan, Haiku 4.5, executed per pre-written text authored on Fable 5)
    reason: |
      ADR-106's wording implies per-PWA Postgres roles exist to gate cross-schema access.
      Four separate build chunks (2A/3A/4A of the Tour-Job plan) each independently
      discovered via `\du` that no such per-PWA roles exist and had to rediscover the real
      convention: all access rides on shared web_anon/authenticator roles. This ADR clarifies
      the platform's actual grant model so the four chunks and future builders do not
      re-derive the same fact in isolation.
    changed_via: adr-kit (360lm), authored directly per hkl's explicit instruction + 
      the pre-written text in the debt-cleanup plan (2026-08-01).
```

## Context

**Observation from live schema audits (2026-08):**

Multiple build chunks audited Postgres role grants (`\du` and `\dp` queries) during the Tour-Job work and found:

- Only TWO roles hold table grants: `web_anon` (the PostgREST unauthenticated/default role) and `authenticator` (the JWT-switching role, per ADR-105).
- No per-PWA roles exist — e.g., no `role_hub`, `role_tour_planner`, `role_rentveh`, etc. that might gate `hub`, `tour_planner`, `rentveh` schema access independently.
- `web_anon` holds broad CRUD grants across every schema (`hub`, `sales`, `tour_planner`, `rentveh`, `counters`, `expense`, `installation`, etc.).
- `authenticator` is primarily a login-switching role (SET ROLE to `web_anon`); it holds very few direct table grants, except where a table's RPC function is marked SECURITY DEFINER and needs both roles.

ADR-106's decision text reads "...grant-gated by convention..." and mentions "consuming role(s)" and "the role(s) that will eventually consume it" — language that could imply per-PWA role identities exist. They do not. PWA isolation comes from:
- Per-table grants at the `web_anon` level (e.g., `counters` schema gets explicit CRUD grants; a PWA in the `hub` schema cannot see counters data unless hub's queries explicitly JOIN across schemas, which PostgREST ACL + table grants control).
- PostgREST `PGRST_DB_SCHEMAS` environment variable (each PWA deployment exposes only its own schema, e.g., `tour-planner` deployment sees only `tour_planner` and shared read-only views).
- Hub-level `employee_pwa_access` table (which PWAs a user can see), not database-level role boundaries.

**Consequence of the confusion:**

When a new table is added and the builder audits grant patterns, there is no per-PWA role to grant to. The builder must grant to `web_anon` (and sometimes `authenticator`) directly, mirroring the closest sibling table's grant set. Discovering this via trial-and-error (`\dp` queries, failed PostgREST ACL errors, re-reading ADR-106) costs 15-30 minutes per table addition across four independent build chunks — wasted rediscovery.

## Decision

### 1. Shared-role model IS the platform convention

PostgREST access isolation on this platform rides on:
- **Shared roles:** `web_anon` and `authenticator` (per ADR-105). No per-PWA roles exist or are planned.
- **Table-level grants:** Each table explicitly grants `web_anon` (always) and `authenticator` (conditionally, if needed). Grants are scoped to the minimum required operation (SELECT/INSERT/UPDATE/DELETE) and checked via `\dp table_name` in psql.
- **Per-table sequences:** If a table has `SERIAL` or `BIGSERIAL` PK, grant `USAGE, SELECT` on the sequence to `web_anon` so the INSERT can auto-increment.

### 2. How to grant a new table (canonical pattern)

When adding a new table:

1. Identify the **closest sibling table** (same schema, same use case) that already has grants and is in production use.
2. Query `\dp <schema>.<sibling_table>` to see what grants the sibling has.
3. Mirror that exact set to the new table: `GRANT SELECT/INSERT/UPDATE/DELETE ON <new_table> TO web_anon;` and `GRANT USAGE, SELECT ON <sequence_name> TO web_anon;` if a sequence exists.
4. Add `authenticator` only if the sibling has it (rare; examples: `rentveh.expenses`, `tourexp.expenses` grant only `web_anon`; `sales.job_tours` historically granted both, but check current state).
5. Verify via `\dp <new_table>` that the grants match.

### 3. ADR-106's grant-gating decision stands unchanged

This ADR only corrects the **unit of granting** (shared roles, not per-PWA roles) and does NOT revise ADR-106's decision to gate cross-schema access via explicit grants. ADR-106 §1 is accurate: cross-schema READS and WRITES must have explicit table-level grants; there is no RLS filtering today. This ADR clarifies what "role" means in that context.

## Alternatives Considered

- **Introduce real per-PWA roles and migrate all grants.** Rejected: large, multi-chunk migration; zero additional isolation benefit (PWAs are same-origin behind hub SSO; PostgREST `PGRST_DB_SCHEMAS` already isolates schema visibility per deployment); would make future table additions require role-creation boilerplate instead of simple grant statements.
- **Silently keep the folklore and accept the rediscovery cost.** Rejected: four independent chunks already rediscovered it; it is not folklore, it is fact; documenting it prevents the fifth chunk from rediscovering it again.

## Consequences

**Positive:**
- Builders adding new tables know exactly what to do: find the sibling, mirror its grants, done. No ambiguity about "which role do I grant to?"
- Reduces rediscovery time from 15-30 min/table to 2-3 min (copy sibling's `\dp` output, paste into new GRANT statements).
- Makes ADR-106 self-consistent: it says grant-gated, this ADR explains the grant is to shared roles, not fictional per-PWA roles.

**Negative / Trade-offs:**
- Shared roles mean a compromised PostgREST runtime can see/touch all tables `web_anon` has grants on. Accepted because (1) PostgREST is internal-only (behind hub SSO); (2) the real isolation boundary is schema-visibility (`PGRST_DB_SCHEMAS`), not role identity; (3) if/when an untrusted external client is added, the full RLS/per-tenant JWT-roles migration mentioned in ADR-105/106 will be mandatory, not optional.

**Risks and mitigations:**

| Risk | Mitigation |
|---|---|
| Builder forgets to check the sibling's grants and grants too much (e.g., TRUNCATE/ALTER) | Canonical pattern §2 step 1: "find the sibling"; diff the `\dp` outputs before applying; future ADRs will cite this pattern. |
| Builder adds a new schema without understanding the shared-role model and creates a per-schema role | The pattern only works if the builder is adding a TABLE; ADRs for NEW SCHEMAS (rare) can explicitly call out the shared-role requirement. This ADR covers the common case. |
| A future native client or external API surface needs true row-level isolation | Already documented in ADR-105/106 as the RLS/JWT-role path for a future ADR — this ADR's shared-role model is interim. |

## Related Decisions

- **ADR-106** (2026-07-03) — clarified by this ADR's correction of the grant unit; the grant-gating decision itself stands unchanged. When ADR-106 says "grant-gated by convention," the grants are to `web_anon`/`authenticator`, not per-PWA roles.
- **ADR-105** — signed JWT switches to `web_anon` role; `authenticator` is the login bridge. No per-PWA role identity.
- **ADR-014** — PostgREST as API layer; this ADR clarifies what roles PostgREST uses.
- **ADR-009** — each PWA owns a schema; this ADR clarifies that schema isolation (code ownership, schema visibility) is separate from role identity (shared roles).
- **ADR-125, ADR-126, ADR-127** — all three apply this shared-role pattern when adding new tables; this ADR is their grant-pattern reference.

## References

- ADR-106 (§Context) — "grant-gated by convention" clause.
- Tour-Job build plan chunks 2A, 3A, 4A (VPS `/var/www/360lm/docs/adr/` + DL `C:\Users\Lenovo\Documents\tour-job-architecture\BUILD_PLAN.md` and `DEBT_CLEANUP_PLAN.md`).
- Postgres role audit queries (`\du`, `\dp`) run during 2026-08 Tour-Job build; confirmed only `web_anon` and `authenticator` hold table grants.
- Master table grant patterns: `counters.counter`, `sales.jobs`, `expense.employees`, `rentveh.expenses` — canonical examples for new tables to mirror.