# DESIGN.md — proxy-monster The one-page map of how proxy-monster fits together. The authoritative per-workstream designs live in [docs/](./docs/README.md); [AGENTS.md](./AGENTS.md) covers intent and target-engine priority. ## Summary A self-hosted OSS proxy that speaks the native MySQL and PostgreSQL wire protocols and enforces column-level access control per role: it parses each query, resolves column lineage (which source columns every output and predicate derives from), and masks or denies accordingly. Native clients (psql, mysql, JDBC) work unchanged. Fail-closed: anything it cannot prove safe is denied. The target databases it protects are MySQL — the primary, fully-enforced target — and PostgreSQL, which is experimental. Its own control-plane store is a separate matter: PostgreSQL only. See [AGENTS.md](./AGENTS.md#two-independent-engine-axes). ## Goals and non-goals Goals - Transparent proxy in front of MySQL and PostgreSQL target databases; native clients unchanged. - Deterministic, role-based column masking (`datasource/catalog/schema/table/column → role → action`). - Lineage-aware enforcement (through functions, expressions, subqueries, joins, `SELECT *`). - Fail-closed on un-resolvable queries; a policy grant is the only way to widen access. - Full audit — hash-chained on the decision path (principal, query, decision, result volume). Tamper-_evidence_ needs the separately-deployed `auditmon` to verify the chain and anchor it off-box ([docs/audit-trail-hardening.md](docs/audit-trail-hardening.md)). - Self-hosted, in-VPC, OSS. Non-goals - Sharding / read-write-splitting / large-scale pooling. - NoSQL / warehouses as target databases (MySQL and PostgreSQL first). - Content-based PII detection as the _primary_ mechanism — deterministic column policy is the boundary. A local PII-NER content backstop over unclassified columns is a planned defense-in-depth, never the boundary (see [Enforcement: mask, deny, allow](#enforcement-mask-deny-allow)). ## Architecture A split control plane (Kotlin/JVM) and data plane (Go) talking over gRPC. The proxy terminates the SQL wire connection and can relay and mask but never decides; the control plane decides every statement and never opens a connection to a target database. Around them sit the web console, the PostgreSQL control-plane store, auditmon, and pmon on the client side. The component roster, the topology diagram, the port/protocol matrix, and where each step of enforcement happens are in [ARCHITECTURE.md](./ARCHITECTURE.md). The rest of this document is the design on top of that shape. ## Policy catalog (data model) Authoritative model: [docs/authz-model.md](docs/authz-model.md). In brief, the control-plane store — PostgreSQL, always — holds: - `policy` — grants held as Cedar policy text (roles/tags/resources referenced as entities inside the source); there are no generated `role_statement` rows, so a role's grants are the enabled policies that mention it, and system policies live in a reserved negative id space. Schema: [docs/policy-store.md](docs/policy-store.md#target-schema). - `column_classification(datasource, schema, table, column, tags[], mask_fn_id)` — per-column tags (`pii`, `system:*`, `preset:*`) + the mask function; these tags are what Cedar conditions on. - `mask_fn(name, kind)` — masking transforms (`FIXED` / `LAST_N` / `FORMAT_PRESERVING` / `NULL`; unknown kinds fall back to a fixed `****` redaction). The transform is fixed by kind. - Roles + identity — `app_role`, `principal_role`, `app_group`, `group_member`, `group_role`, `app_user`. - JIT elevation — `access_request` + `access_grant(role, expires_at, revoked_at)`: a time-boxed, revocable grant of a role that widens the effective role set for its window. Who may approve is Cedar (`task.approve`), not a separate approval-config table. - `audit_event` — the chained, heterogeneous event log (`decision` / `completion` / `approval_lifecycle`); the single audit chokepoint. ## Enforcement: mask, deny, allow Per statement, the analyzer emits `StatementFacts` and the control-plane walks it through Cedar: 1. Parse + lineage (sqlglot-go). A statement the analyzer cannot safely resolve is `resolved=false` → routes through the `exception.unanalyzable` Cedar gate (fail-closed by default, grant-overridable). 2. Required grants — the analyzer emits, per statement: the `sql.` grant; a `result.read` grant per output column (with a mask vs deny disposition); a `result.read` grant per scanned table with zero traced columns (closes the zero-column existence oracle); and function / utility grants for dangerous calls and session-critical commands. 3. Cedar grant-walk — each grant is a Cedar `authorize` verdict over the principal's roles + resource tags + request context. A maskable column deny → mask that output column; a non-maskable deny (a sensitive column in a predicate / join / aggregate / non-whitelisted derivation, where masking cannot preserve correctness) → DENY the whole statement. Session-privilege / lexer-mutation commands ride a `system:critical` Cedar forbid. 4. Enforce — goproxy applies the decision: star-expansion rewrite so ordinals bind by construction, inline result-stream masking of flagged output columns by ordinal, forward an `ALLOW`, or return a protocol error on `DENY`. (Row-level `WHERE` injection is not implemented; there is no row-level policy table.) 5. Audit the decision. The guarantee lives in mask + deny (fail-closed): the analyzer's star-expansion rewrite binds mask ordinals, the proxy masks classified columns on the result stream, and the cases masking cannot make safe — a sensitive value in a predicate / aggregate / derived expression, an unparseable or unresolvable statement — are exactly the DENY cases. Content-detection backstop (planned defense-in-depth, not the boundary). A local PII-NER model (GLiNER-class, ONNX on the JVM — no hosted model ever sees a value) over unclassified / free-text columns that carry no `column_classification`, catching residual leaks the deterministic engine cannot see. It is not a boundary against inference (a WHERE/aggregate already ran on real data); the guarantee stays in mask + deny. Hits double as classification suggestions. Local-only by construction — this is a PII proxy, so no result value leaves the trust boundary ([docs/backlog.md](docs/backlog.md)). ## Identity and broker Separate **who you are** (identity source) from **how you prove it over the wire** (auth mechanism). Authoritative: `docs/auth-model.md`. - **Identity source — OIDC (Okta), canonical:** the source of truth for principal + group membership; groups map to proxy **roles** and approval groups (§7). Roles are resolved server-side on every decision. - **Wire auth — SSO login + short-lived token.** A `pmon` daemon (single static **Go** binary) runs the OIDC device-auth flow and the control-plane mints a **short-lived opaque, revocable credential** bound to principal + roles. - **Default — background daemon + local broker:** `pmon` runs stable loopback listeners, opened as soon as credentials exist; saved psql/DataGrip connections use a never-changing localhost password while the daemon injects the current token on the upstream hop and silently renews it (no terminal, no browser day-to-day). - **One daemon, two front ends:** the daemon owns all state and exposes a **local control socket** (a unix socket in a 0700 dir — filesystem permissions are the authentication, and it is not a loopback TCP port because the API can start a login). The CLI and the menu-bar app are **symmetric peers** over it: neither is privileged, both start and stop the daemon, both work when it is down. The daemon runs the login itself, so there is one implementation and no two-device-flow race. It is spawned detached and outlives whichever peer started it, so stopping is always explicit — and a peer that crashes leaves it brokering, to be adopted by the next one. - **One-shot token:** the web **Tokens** page mints a short-lived password the user pastes into any client (headless/CI, or a client the broker daemon does not front). - **Expiring-only** — every credential has a server-set expiry (TTL clamped `[60s, 24h]`); there are no persistent tokens. - The token rides in the password field (PG `AuthenticationCleartextPassword`, MySQL `mysql_clear_password`), so the hop needs wire TLS or a private transport. Wire TLS is optional (`PM_TLS_CERT` + `PM_TLS_KEY`, both or neither): with it set the proxy rejects a client that does not upgrade, and without it the proxy listens in plaintext and the token crosses in the clear — acceptable only on a trusted network. The cert needs no publicly-trusted CA, because the proxy distributes its own trust material (see below). - **Cert distribution (no MDM CA):** the proxy advertises the certificate CHAIN a client should trust — leaf first, plus any intermediates and root — to the control plane on register, along with a separate boolean saying whether it serves TLS at all. `pmon` fetches `{datasource → address, chain, wire-TLS}` at login (over the CP's own trusted HTTPS, scoped to `?connectable=true`) and verifies the proxy the ORDINARY way: that chain as its root pool, with the advertised host checked against the certificate. A self-signed cert is simply its own anchor, so nothing has to be hand-distributed or enter a system trust store. Direct clients get the same bytes as a download (`psql`/`mysql`/DataGrip via `sslrootcert` / `--ssl-ca` with `verify-full`), so there is one trust mechanism rather than a separate pinning path. A datasource whose proxy serves TLS but greets without it is refused rather than downgraded to plaintext — keyed on the TLS boolean, not on whether a chain happens to be published, because a publicly-trusted proxy publishes none. **Trust anchor:** the chain is only as trustworthy as the register credential and the pmon↔CP channel — a holder of the gRPC register secret can overwrite a datasource's advertised address and chain together (see `KNOWN_LIMITATIONS.md`; registrar-identity hardening is backlogged). A rotated leaf re-advertises on the next register/reconnect resync (immediate rotation-refresh is a follow-up). - **Broker:** goproxy reaches the target DB with a per-datasource **service account**; the token only proves _who you are_, never _what you can see_ — enforcement (§5) and JIT (§7) are independent of how it was obtained. Separate who you are (identity source) from how you prove it over the wire (auth mechanism). Authoritative: [docs/auth-model.md](docs/auth-model.md). - Identity source — OIDC (Okta), canonical: the source of truth for principal + group membership; groups map to proxy roles. Roles are resolved server-side on every decision. - Wire auth — SSO login + short-lived token. A `pmon` daemon (single static Go binary) runs the OIDC device-auth flow and the control-plane mints a short-lived opaque, revocable credential bound to principal + roles. - Default — background daemon + local broker: the daemon opens one loopback listener per datasource on a sticky port as soon as credentials exist (`pmon login` is the only step), so a saved native or JDBC connection keeps a fixed `127.0.0.1:` and a localhost password that never rotates, while the daemon injects the current token on the upstream hop. The daemon renews the wire token before expiry until the server-side session window closes, then requires a fresh `pmon login`. - One-shot token: the console's Access page (`/access`) mints a short-lived password (`POST /api/tokens`) the user pastes into any client. - Expiring-only — every credential has a server-set expiry (TTL clamped `[60s, 24h]`); there are no persistent tokens. - The token rides in the password field (PG `AuthenticationCleartextPassword`, MySQL `mysql_clear_password`). Wire TLS is optional (`PM_TLS_CERT` + `PM_TLS_KEY`, both or neither): with it set the proxy rejects a client that does not upgrade, and unset the proxy listens in plaintext, so the token crosses the network in the clear. Plaintext is therefore only acceptable on a trusted network — a private transport such as a peered VPC, PrivateLink, or a VPN/tailnet. Configuration: [INSTALL.md](INSTALL.md#proxy--one-set-per-datasource). The remaining trust limits are documented in [KNOWN_LIMITATIONS.md](KNOWN_LIMITATIONS.md#wire-cert-distribution-direct-clients-and-pmon). - Broker: goproxy reaches the target DB with a per-datasource service account; the token only proves _who you are_, never _what you can see_ — enforcement and JIT elevation are independent of how it was obtained. Web session lifetime, IdP revalidation, and device-binding: [docs/session-lifetime.md](docs/session-lifetime.md). ## JIT elevation and approval Baseline roles are static (OIDC group → role). Beyond baseline, a principal can request time-boxed elevation subject to approval — the only human-in-the-loop path; the per-query engine stays deterministic and never blocks on a human. Authoritative: [docs/approval-workflow.md](docs/approval-workflow.md) + [docs/authz-model.md](docs/authz-model.md). Elevation is an identity-layer mutation. The engine is a pure function of `(statement-facts, effectiveRoles, catalog, policy)`; a grant only widens the effective role set it sees for the grant's window, so the same per-query decision applies with no separate elevation path in the engine. An approved grant takes effect on the principal's next query (roles re-resolved per decision), and expiry/revocation reverts subsequent queries with no reconnect. Query approval runs a query under a role `R`, once, via an approver (`task.approve`, requester ≠ approver): the approver executes the approved statement under `R` and releases the result, masked exactly as `R` would see it. A single execution path; who may approve is Cedar, not a config table. Control / data plane split — control-plane (Kotlin: catalog + policy + grants + REST/console + the FFM analyzer) and data-plane (Go goproxy) are logically separate; the proxy reads each decision over gRPC. See [docs/datasource-registration.md](docs/datasource-registration.md). Console (Next.js + Tailwind + shadcn/ui, `web/`) — requester (request elevation / my grants), approver (inbox; approve/reject; execute-under-`R`), admin (classifications, Cedar policies, mask fns, datasources, audit). See [docs/web-console.md](docs/web-console.md). ## Risks - Analyzer parse/lineage coverage drives the DENY rate — a construct sqlglot-go can't resolve fails closed. Mitigated by golden-parity tests and a grant-overridable `exception.unanalyzable`. - Rewrite correctness — masking mutates the result stream. Mitigated: prefer deny over a risky rewrite; ordinal-bound masking with fail-closed `SELECT *` expansion; large DB-backed test suites. - Performance — analysis per statement adds latency. Mitigated: per-connection catalog caching; the Go proxy relays hot bytes. - Prepared statements / binary protocol — PG extended-query is enforced. MySQL `COM_STMT_*` is decided and relayed, but binary-protocol results cannot be masked: a MASK verdict without `exception.unmaskable` fails closed ([KNOWN_LIMITATIONS.md](KNOWN_LIMITATIONS.md)). - Coverage gaps = security gaps — anything not analyzed defaults to DENY. ## Roadmap Planned and deferred work — including the content-detection masking backstop and the agentic "auto mode" stretch — lives in [docs/backlog.md](docs/backlog.md).