feat(postgres): add single-port 5432 gateway with go proxy

[elitan]

Title

Single external Postgres port (5432) for all branches via built-in Go gateway (no Traefik)

Why

Today we have:

• Internal branch access on *.frost.internal:5432 (works)
• External direct access on random host ports (10000-20000)

Goal:

• One external port (5432)
• Branch selected by hostname
• Keep current internal flow unchanged
• Avoid Traefik; use our own Go proxy

Decision

Build a Frost-managed Go TCP gateway for Postgres on host port 5432.

Value vs effort

This can be worth it if it becomes a clear user-facing feature (simpler external DB access).

Work is medium for MVP, high for production-hardening.
So we use phase gates. If gate fails, stop early.

Scope (locked)

• No Traefik
• Require TLS for external path
• Wildcard DNS required
• Client mode target: classic Postgres clients (sslmode=require)
• Expose all active Postgres branches externally
• Reserve host :5432 for gateway

Architecture (MVP)

Client -> :5432 Go gateway -> branch runtime hostPort

Routing key:

• Hostname via TLS SNI
• Example:
  • main.pg.myproj.example.com:5432
  • dev.pg.myproj.example.com:5432

Classic Postgres SSL flow handling:

1. Client sends Postgres SSLRequest preface
2. Gateway replies S
3. TLS handshake happens at gateway
4. Gateway reads SNI hostname
5. Gateway maps hostname -> backend target hostPort
6. Gateway proxies Postgres bytes

Phase plan

Phase 0: spike + gate (must pass)

Deliverables:

• Tiny PoC Go server that handles Postgres SSLRequest + TLS + SNI routing to fixed backend
• Validate with:
  • psql
  • one Node pg client
  • one Prisma or JDBC sample (pick one)

Acceptance:

• 3 clients connect with sslmode=require
• Query round-trip works through proxy
• Wrong hostname fails cleanly

Gate:

• If client compatibility is unstable, stop and keep current model

Phase 1: gateway service in Frost

Deliverables:

• New component: postgres-gateway (Go)
• systemd unit managed by install/update
• Binds :5432 only
• Health endpoint/health check for gateway process
• Structured logs

Acceptance:

• Service starts reliably
• Clear startup error if 5432 already in use
• Restart policy works

Phase 2: dynamic route sync from app -> gateway

Deliverables:

• App generates route map file (hostname -> hostPort, target metadata)
• Route map updates on Postgres target lifecycle events:
  • create/start/stop/deploy/reset/delete/rename
• Gateway hot-reloads route map atomically

Acceptance:

• Route changes reflected without full restart
• Stopped targets removed quickly
• No broken config window during updates

Phase 3: API/UI integration

Deliverables:

• Extend target runtime payload with external connection fields
• Branch panel shows new external string on :5432
• Keep current direct host-port string during rollout

Acceptance:

• UI shows exact hostname and sslmode=require
• Null/disabled state messages are clear (gateway disabled, wildcard missing, target stopped)

Phase 4: hardening

Deliverables:

• Connection limits + timeouts
• Backpressure handling
• Safe reload behavior under load
• Metrics:
  • active connections
  • route misses
  • handshake errors
  • backend dial errors
• Security review (TLS config baseline)

Acceptance:

• Soak test passes under concurrent load
• No crashes during repeated route updates

Public interface changes

• databases.getTargetRuntime adds:
  • externalHost: string | null
  • externalPort: number | null (default 5432)
  • externalSslMode: "require" | null

New config/env

• FROST_POSTGRES_GATEWAY_ENABLED (default false)
• FROST_POSTGRES_GATEWAY_PORT (default 5432)
• FROST_POSTGRES_GATEWAY_ROUTE_FILE (path to generated route map)
• FROST_POSTGRES_GATEWAY_CERT_FILE / ..._KEY_FILE (or use existing cert path strategy)

DNS/hostname strategy

Use wildcard base already managed in settings.

Hostname format:

• <target-hostname>.pg.<project-hostname>.<wildcard-base>

Example:

• dev.pg.shop.example.net

Rules:

• lowercase + kebab-safe
• length-safe
• collision-safe with short hash suffix when needed

Tests

Unit:

• SSLRequest parser
• SNI hostname extraction
• hostname builder and collision handling
• route file parser + atomic reload

Integration:

• gateway routes two branches on same :5432
• route removed when branch stops
• rename updates route

E2E:

• create branch, connect externally, run query
• second branch same DB, data isolation check
• delete branch, old hostname fails

Assumptions

• External clients can use sslmode=require
• Wildcard DNS is configured
• Host :5432 can be reserved for gateway
• Internal .frost.internal:5432 path remains unchanged

Key risks

• Postgres STARTTLS edge cases across drivers
• Cert lifecycle handling if we move to verify-full later
• Operating a critical always-on gateway on :5432

Rollout

1. Dark launch behind FROST_POSTGRES_GATEWAY_ENABLED=false
2. Enable on demo/staging host
3. Run smoke + soak
4. Enable by default after stability window

Out of scope (first release)

• verify-full certificate validation UX
• Per-branch external opt-in toggle
• Multi-node/distributed gateway

Done when

• Any active Postgres branch is reachable externally at unique hostname on shared host port 5432
• Internal behavior unchanged
• Failure modes are explicit and observable