Production hardening and RLS backstop

Overview

Close the roadmap: add Postgres row-level security as a defence-in-depth backstop under the scoped store (never instead of it), run a security review against the OWASP API risks, prove behaviour under load and soak, add GitHub Actions CI with a Postgres service, produce a stamped release build, and write the deployment guide plus the final architecture document.

Requirements

Functional

• RLS enabled and forced on every tenant-owned table, with policies USING (tenant_id = current_setting('hango.tenant_id')::uuid).
• A non-owner application role (hango_app) that owns no tables, so policies cannot be bypassed; FORCE ROW LEVEL SECURITY on every table so even a mistaken owner connection is covered.
• A second role, `hango_system`, with `BYPASSRLS`, used by a **separate small pool** (`HANGO_SYSTEM_DB_MAX_CONNS`, default 4). Exactly five call sites may use it, enumerated in code and asserted by a test that no other package holds a reference: `auth.ResolveKey` (runs before a tenant is known), the run reclaim sweep, the queued-run poller, the approval-expiry sweeper, and migrations/readiness. Everything else uses the `hango_app` pool.
• `store.WithTenantTx(ctx, fn)`: begins a transaction on the `hango_app` pool, issues `SET LOCAL hango.tenant_id = $1` from the context scope, runs the callback with a scoped store over the transaction, commits. **Every tenant-scoped store call runs inside one**; there is no "plain pool query keeps working" path, because under `FORCE` RLS with an unset GUC there is none. The scoped store's method bodies are unchanged — the refactor moves where they are called from, so their explicit `tenant_id` predicates remain as the first isolation layer.
• Hard rule, tested: a `WithTenantTx` never wraps a provider call, a tool call or a subagent run. The helper takes a callback whose only job is persistence; a lint-style test fails if `WithTenantTx` appears in the same function as `provider.` or `Chain.ToolCall`.
• `audit_events` is in the RLS enumeration like every other tenant-owned table, and its policy grants the `hango_app` role `SELECT` and `INSERT` only — no `UPDATE`, no `DELETE` — so the append-only property is enforced by the database and not only by the store package. No audit write needs the system pool: tenant creation inserts the `tenants` row (not tenant-owned, so not under RLS), then writes the owner user, the first key and the `tenant.create` audit row inside `WithTenantTx(newTenantID)`. Bootstrap writes the platform user and key inside `WithTenantTx(platformTenantID)`, and platform routes that act on tenant X (`GET /v1/admin/usage?tenant_id=`, quota updates) run inside `WithTenantTx(X)`. That is what keeps the system pool at exactly five callers.
• Retention: v1 deletes nothing from `audit_events`. `docs/deployment-guide.md` states the growth rate per mutating request and records the follow-up — monthly range partitioning or an archive-then-detach job — as the intended path once volume justifies it. Silently truncating an audit trail is the one operation this design refuses to make easy.
• A verification test proving RLS actually blocks: with the scoped WHERE tenant_id predicate deliberately removed via a test-only raw query, cross-tenant reads still return zero rows.
• Security review checklist covering the OWASP API Security Top 10 as it applies to hango, with each item marked addressed, accepted or out of scope, and evidence per item.
• Load and soak are **Go tests**, not bespoke driver programs: `TestConcurrentRuns` (in `internal/run`) drives N concurrent runs against the fake provider and runs twice, with RLS policies off and on, comparing p95 against a **budget written down before the first measurement** in `docs/deployment-guide.md` (initial budget: p95 run latency under 2s at concurrency 32 with the fake provider, RLS adding no more than 10%). The soak is the same test body driven by duration (`-count` or `HANGO_SOAK_DURATION`), running the mixed workload (compaction, subagents, approvals). No `scripts/loadtest`, no `scripts/soaktest`, no bespoke metrics exporter — the tests use the ordinary test assertions and the existing telemetry.
• The Go 1.27 `goroutineleak` profile assertion lives **only** in the soak test, where a long-lived process makes it meaningful. Unit and integration suites use before/after goroutine-count and broadcaster-topic-count assertions.
• GitHub Actions CI: build, vet, golangci-lint, unit tests, and integration tests against a Postgres service container, plus a Docker image build.
• Release build: -trimpath, stripped, version/commit/date stamped, reproducible flags documented; hango -version prints all three.
• docs/deployment-guide.md and a final, accurate docs/system-architecture.md.

Non-functional

• Enabling RLS must not regress latency materially: every tenant-owned index already leads with tenant_id, and the load test compares p95 before and after enabling policies.
• The pool must never hold a connection across a provider call, which is re-verified by the load test with a deliberately small pool.
• No secret is present in the image, the compose file, the CI logs or any telemetry attribute; a test and a CI step check for the obvious patterns.

Architecture

// internal/store/pg/rls.go
func (s *pgStore) WithTenantTx(ctx context.Context, fn func(store.Scoped) error) error
// BEGIN; SET LOCAL hango.tenant_id = $1; ...; COMMIT   (SET LOCAL only, never SET)
// Red team: F1 — never spans a provider call, a tool call or a subagent run.

// Red team: F1 — the BYPASSRLS pool, with a deliberately tiny max size and five allowed callers.
func (s *pgStore) System() SystemQuerier // auth lookup, reclaim sweep, queued poller,
                                         // approval sweeper, migrations/readiness

// internal/store/pg/rls_test.go
// test-only raw querier that omits the tenant predicate, proving the policy is load-bearing

-- migrations/00011_rls.sql
-- one block per tenant-owned table: tenants' children, users, api_keys, agents, sessions,
-- messages, runs, run_events, agent_files, subagent_calls,
-- usage_records, tenant_quotas, pending_approvals   -- Red team: D1 — no provider_credentials in v1
-- audit_events                                      -- Updated: Validation Session 1 - audit everything
alter table agents enable row level security;
alter table agents force row level security;
create policy agents_tenant_isolation on agents
  using (tenant_id = current_setting('hango.tenant_id', true)::uuid)
  with check (tenant_id = current_setting('hango.tenant_id', true)::uuid);
-- repeated for every tenant-owned table; `true` in current_setting means a missing GUC
-- yields NULL, and NULL = uuid is false, so the default is deny.

-- application roles: hango_app (no ownership, RLS applies) and hango_system (BYPASSRLS)
-- Red team: F1 — both documented in docs/deployment-guide.md and created by an operator, not by
-- a migration, because role management needs superuser and differs per environment.
-- The compose file creates both so development matches production.

Instrumentation

• store.tenant_tx INTERNAL span with hango.tenant.id and hango.rls.enabled, so it is visible which reads ran under RLS.
• Metrics: hango.rls.denials counter incremented when a query unexpectedly returns zero rows under a set GUC (a canary for policy misconfiguration), hango.db.pool.acquire_duration histogram and hango.db.pool.in_use gauge from the pgx pool stats, hango.build.info gauge carrying version, commit and date as attributes.
• The load and soak tests emit nothing of their own: they run against a server whose ordinary telemetry is already on, so the phase-14 dashboards are the readout and the test assertions are the gate.
• `hango.rls.denials` gets an alert rule and a panel added to `hango-api` in this phase, so a misconfigured GUC is visible rather than silently returning empty lists.
• Alert rules from phase 14 are exercised deliberately during the soak test to confirm they fire.

Related Code Files

Create

• migrations/00011_rls.sql.
• internal/store/pg/rls.go, internal/store/pg/rls_test.go.
• internal/store/pg/poolstats.go — pool metrics exporter.
• internal/telemetry/buildinfo.go — hango.build.info gauge.
• internal/run/load_test.go — TestConcurrentRuns (RLS off/on) and TestSoak (duration-driven, mixed workload, goroutineleak profile assertion).
• .github/workflows/ci.yaml, .github/workflows/release.yaml.
• docs/security-review.md — the OWASP API checklist with evidence.
• docs/deployment-guide.md.
• docs/learning/16-hardening-and-rls.md.

Modify

• internal/store/pg/store.go — expose WithTenantTx and the system pool; the app pool connects as hango_app.
• Every tenant-scoped call site in `internal/server/handlers_*.go`, `internal/run/worker.go`, `internal/agent/mw*.go` and `internal/fs/pgbackend.go` — wrapped in `WithTenantTx`. This is the bulk of the phase's work and the reason its effort is higher than a migration-only phase would be.
• internal/auth, internal/run/reconcile.go, internal/run/poller.go, internal/run/approval_sweeper.go — switched to the system pool.
• internal/config/config.go — HANGO_DB_RLS_ENABLED, HANGO_SYSTEM_DATABASE_URL, HANGO_SYSTEM_DB_MAX_CONNS, HANGO_VERSION/build stamps.
• internal/server/health.go — a readiness check asserting RLS is enabled on every expected table when HANGO_DB_RLS_ENABLED=true, run on the system pool.
• deploy/grafana/provisioning/alerting.yaml, deploy/grafana/dashboards/hango-api.json — RLS denial alert and panel.
• Makefile — loadtest and soaktest become thin wrappers over go test -run TestConcurrentRuns / -run TestSoak; plus release, security-scan.
• Dockerfile — final stamping flags; verify nonroot and no shell.
• compose.yaml — app role documentation and a non-owner DSN example.
• docs/system-architecture.md — final pass covering every phase; docs/learning/README.md; README.md.

Implementation Steps

1. Write migrations/00011_rls.sql enabling and forcing RLS with a policy per tenant-owned table, using current_setting('hango.tenant_id', true) so a missing GUC denies rather than errors. Enumerate every table added in phases 2-15 and cross-check against the cross-tenant test table so none is missed.
2. Write internal/store/pg/rls.go with WithTenantTx using SET LOCAL inside an explicit transaction, plus the hango_system pool and its five-caller allowlist. Add comments stating why plain SET is forbidden (it persists for the pooled connection’s life and leaks the previous tenant’s context) and why the system pool must stay tiny.
3. Perform the refactor: move **every** tenant-scoped store call inside a `WithTenantTx`, and move the five system paths onto the system pool. Do it package by package with the integration suite green after each, because a half-converted call site under `FORCE` RLS returns empty results rather than erroring, which is the failure mode that hides. Add the lint-style test forbidding `WithTenantTx` in the same function as a provider or tool call.
4. Write internal/store/pg/rls_test.go: a test-only querier that issues raw SQL without the tenant predicate, run once with the GUC set to tenant A and once unset, asserting zero cross-tenant rows in both cases. This test is the proof that RLS is load-bearing rather than decorative.
5. Document both roles in docs/deployment-guide.md (hango_app: no ownership, RLS applies; hango_system: BYPASSRLS, used only by the five enumerated paths, with its own DSN and a tiny pool) and update compose.yaml to create and use both so development matches production.
6. Write internal/store/pg/poolstats.go and internal/telemetry/buildinfo.go.
7. Write docs/security-review.md against the OWASP API Security Top 10, with hango-specific evidence per item: broken object-level authorization (scoped store plus per-table cross-tenant tests plus RLS), broken authentication (SHA-256 keys, constant-time compare, revocation, expiry), broken object property-level authorization (DTO layer, creator-scoped visibility for member, no secret ever in a response), unrestricted resource consumption (quotas, rate limits, the pre-auth limiter, iteration cap, tool timeouts, run deadline, file size caps, SSE buffer and replay-stage bounds), broken function-level authorization (the per-route authorization matrix, including no self-approval of one’s own run), unrestricted access to sensitive business flows (approval gating, privileged /memories/ and /skills/ writes), SSRF (http_fetch allowlist plus post-resolution private-IP rejection, and the https-or-loopback validation of the operator-set provider base URL), security misconfiguration (distroless nonroot, no shell, HANGO_TRACE_LLM_CONTENT default off, collector stripping), inventory management (versioned /v1 routes, hango -version, migration list), unsafe consumption of third-party APIs (provider timeouts, retry caps, typed errors, schema validation of tool arguments). Each item’s evidence cites the audit_events actions that make it observable after the fact, and the checklist records that the trail is append-only at the database level with no v1 retention job.
8. Write down the performance budget in `docs/deployment-guide.md` **before** measuring, then write `TestConcurrentRuns`: create a tenant, agent and N sessions; drive `HANGO_LOAD_CONCURRENCY` concurrent runs through the fake provider with tool calls; assert p50/p95/p99 against the budget, zero errors, no goroutine growth and bounded pool usage; run the whole body twice, with RLS policies disabled and enabled, and assert the RLS overhead is within the documented allowance.
9. Write `TestSoak` as the same harness driven by `HANGO_SOAK_DURATION` with the mixed workload (long sessions triggering compaction, subagent delegation, approvals decided by the test), asserting heap and goroutine stability and no lost or stuck runs. It is skipped under `-short` and in ordinary CI.
10. Put the Go 1.27 `goroutineleak` profile assertion in `TestSoak` only; `internal/run`, `internal/agent` and `internal/server` keep the before/after goroutine-count and topic-count assertions introduced in phase 6.
11. Write .github/workflows/ci.yaml: matrix-free single job on ubuntu-latest with a postgres:17 service, steps go build ./..., go vet ./..., golangci-lint-action v2, go test ./... -race, and a docker build. Write .github/workflows/release.yaml building the stamped binary and image on a tag.
12. Finalize Dockerfile stamping and add make release producing hango with version, commit and date; hango -version prints all three.
13. Write docs/deployment-guide.md: required and optional environment variables (pointing at .env.example rather than duplicating), the hango_app and hango_system roles and RLS setup, migration policy, the performance budget, the single-replica constraint and what to change before scaling out (noting that the run ownership lease from phase 5 is already multi-replica-safe), telemetry backend choice (managed, not lgtm), backup and restore, and the shutdown/drain behaviour operators must configure timeouts around — including the rule that HANGO_DRAIN_TIMEOUT must exceed one turn.
14. Final pass on docs/system-architecture.md: packages, data model with every table, request and run flows, the middleware stack order with rationale, tenancy model with both layers, observability pipeline, deployment topology, and the documented limitations.
15. Add the RLS-denial alert rule and panel to the phase-14 provisioning files.
16. Run everything: full gate, -race, integration tests with RLS forced, make loadtest, make soaktest, make obs-verify, a clean docker compose up from an empty volume, and a tagged release build.

Learning Doc Outline

docs/learning/16-hardening-and-rls.md must contain:

• Concept: defence in depth — why RLS is added under the scoped store rather than replacing it, and what class of bug each layer catches.
• RLS mechanics and the sharp edges: SET versus SET LOCAL and the pooled-connection leak, table-owner bypass and FORCE ROW LEVEL SECURITY, current_setting(..., true) to make a missing GUC deny, every index needing tenant_id first or policies degrade to sequential scans, and why holding a transaction across an LLM call is unacceptable.
• Design choices: policies on every tenant-owned table, a non-owner app role, a tiny BYPASSRLS system role for the five paths that legitimately precede a tenant scope, WithTenantTx as the only GUC-setting path, and the deliberately adversarial test that removes the predicate to prove the policy works.
• Why "turn on RLS" is a refactor and not a migration: under `FORCE` RLS an unconverted query returns zero rows instead of failing, so the whole integration suite green under forced policies is the only honest acceptance test, and the auth path — which runs before any tenant is known — must be designed for explicitly.
• Trade-offs: RLS everywhere vs only on high-risk tables; transaction-scoped reads vs pool reads; schema-per-tenant and database-per-tenant as the alternatives hango rejected and why.
• Why the load and soak drivers are Go tests: a bespoke driver is code nobody runs, and a budget invented after the first measurement is not a budget.
• Production readiness: what the load and soak tests actually prove and what they cannot prove at single replica.
• goclaw comparison: zero RLS across 160 migrations, relying entirely on QueryScope plus a visibility_filter.go to patch leaks, which is the concrete evidence for hango’s two-layer approach.
• deepagents comparison: no database, no tenancy, no deployment story — the whole of this phase is server work the harness research cannot inform.
• How to debug this layer: hango.rls.denials is the canary for a misconfigured GUC or policy; the store.tenant_tx span shows which reads ran under RLS; pool metrics plus the load-test dashboards show whether a regression is contention or policy cost; CI logs plus hango -version on a running instance answer “what exactly is deployed”.
• Exercises: run the RLS test with policies dropped and watch it fail; connect as the table owner without FORCE and observe the bypass; point one converted call site back at the plain pool and watch it return empty results rather than an error; compare load-test p95 with and without policies.
• Time budget: 13h implementation + 3h learning doc (it doubles as the RLS operations note).

Success Criteria

• go build ./... && go vet ./... && go test ./... -race pass; golangci-lint run clean.
• RLS is enabled and forced on every tenant-owned table; a test enumerates the tables from the migration list and asserts none is missing.
• The full integration suite is green with RLS enabled and forced. This is the criterion that proves the refactor landed: under FORCE RLS an unconverted call site returns empty results instead of erroring, so nothing short of the whole suite catches it.
• The authentication path is exercised post-RLS: a request with a valid API key authenticates successfully with policies forced (it runs on the hango_system pool), and a test asserts ResolveKey is the only auth-path query using that pool.
• A test asserts that only the five enumerated call sites reference the system pool, and that no function containing a WithTenantTx also performs a provider or tool call.
• audit_events carries an RLS policy like every other tenant-owned table, and a test asserts the hango_app role’s UPDATE and DELETE on it are rejected by the database; docs/deployment-guide.md states the retention follow-up.
• The adversarial RLS test (raw query without the tenant predicate) returns zero cross-tenant rows both with the GUC set to another tenant and with it unset.
• /readyz fails when HANGO_DB_RLS_ENABLED=true and a policy is missing.
• go test -run TestConcurrentRuns at the documented concurrency shows no goroutine growth, bounded pool usage, zero lost runs, and p95 within the budget written down beforehand, both with and without RLS.
• go test -run TestSoak shows stable heap and goroutine counts for the full duration and no stuck runs.
• The goroutineleak profile assertion runs in TestSoak; the other suites assert goroutine and topic counts return to baseline.
• CI is green on a pull request: build, vet, lint, unit and integration tests with Postgres, Docker build.
• hango -version prints version, commit and date from the release build; hango.build.info carries the same values.
• docs/security-review.md has a decision and evidence for all ten OWASP API items.
• docs/deployment-guide.md and docs/system-architecture.md are accurate against the final code, and docs/learning/README.md indexes all sixteen learning docs.
• Every acceptance criterion in plan.md is checked off with evidence.

Risk Assessment

Rollback

goose down one step disables and drops the RLS policies, leaving the scoped store as the sole (already tested) isolation layer. The WithTenantTx refactor stays in place after a policy rollback — it is correct with or without RLS — so the rollback is a single migration step with no code revert. CI, release, load and soak artifacts are additive and can be removed without touching runtime code.