# ADR-050: Client Auth Tables Have No Direct web_anon Grants — RPC-Only Access

## Status

Accepted, 2026-06-22.

## Status History

```yaml
status_history:
  - date: 2026-06-22
    status: Proposed
    changed_by: hkl
    reason: Formalising RPC-only access pattern for external client identity tables
    changed_via: adr-kit (360lm)
  - date: 2026-06-22
    status: Accepted
    changed_by: hkl
    reason: No web_anon grants on client_user, client_session, client_invite; verified by tests (401/403/406)
    changed_via: adr-kit (360lm)
```

## Context

The client portal auth tables (`client_user`, `client_session`, `client_invite`) contain external client identity, active sessions, and invite tokens. In the standard 360lm PostgREST setup, `web_anon` has SELECT/INSERT/UPDATE grants on most tables — this is appropriate for employee-facing PWA data where session auth is the access gate at the application layer. For external client identity tables, PostgREST row-level access would require client-specific RLS policies, and any grant misconfiguration could expose session tokens or invite tokens to unauthenticated requests. The decision was made to close the PostgREST path entirely and require all access via SECURITY DEFINER RPCs.

## Decision

`client_user`, `client_session`, and `client_invite` tables have **NO direct web_anon grants** (no SELECT, INSERT, UPDATE, DELETE). PostgREST returns 401/403/406 on any direct table access attempt.

All operations go through `SECURITY DEFINER` RPCs:
- `validate_invite_token(token)` → creates session, returns session payload
- `get_client_session(session_key)` → validates and returns session
- `invalidate_client_session(session_key)` → logs out
- `admin_create_invite(email, brand_codes, created_by)` → creates invite, queues email

This is a stricter access control than the default pattern: instead of RLS (row-level security) policies that restrict which rows web_anon can see, we prevent web_anon from even addressing the table.

**Decision Maker:** hkl

## Alternatives Considered

- **Standard web_anon grants + Row-Level Security policies.** Rejected: RLS policies for session tokens require the session token itself to be in the request header — a circular dependency (need the session to verify the session); RLS misconfiguration silently opens all rows; RPC-only is a hard structural guard.
- **Store client identity in a separate database (not lm360).** Rejected: cross-database joins are complex in PostgREST; the client identity is tightly coupled to recce.submissions (which is in lm360); isolation via separate DB adds operational overhead.
- **web_anon SELECT only (read-only PostgREST access).** Rejected: even read-only SELECT on client_session exposes all active session tokens to unauthenticated PostgREST queries — any network observer could enumerate sessions.

## Consequences

**Positive:**
- Client session tokens and invite tokens are never directly queryable via PostgREST.
- Auth logic is centralised in RPCs — easier to audit than distributed RLS policies.
- Test suite can verify the 401/403/406 responses to catch accidental grant addition.

**Negative / Trade-offs:**
- Admin dashboard cannot use standard PostgREST queries to list client users — must call an RPC.
- Every auth operation requires a SECURITY DEFINER function — more PG function maintenance.
- Debugging auth issues requires checking RPC logic rather than querying tables directly.

**Risks and mitigations:**
- Developer accidentally GRANTs web_anon access to a client auth table: mitigated by Playwright test suite verifying 401/403/406 on direct table access; grant would be caught in automated tests.
- SECURITY DEFINER RPC has overly broad logic: mitigated by keeping each RPC single-purpose (validate_invite does only token validation, not session listing).

## Related Decisions

- ADR-007 (SECURITY DEFINER trigger functions) — same principle extended to RPCs for auth.
- ADR-048 (external client magic-link) — the auth flow that these RPCs implement.
- ADR-014 (PostgREST as API layer) — this ADR is a restriction on the PostgREST pattern for sensitive tables.

## References

- `memory/dbt_client.md` — "web_anon: NO direct grants on client_user, client_session, client_invite — RPCs only. Verified in tests (401/403/406)"
- `tests/recce_client.spec.js` — Playwright tests verifying 401/403/406 on direct table access