Discovery (live) corrected the design: webhook_receiver, ingest_worker, and worker
all run run_migrations.py (DDL) and write telemetry — worker is the same image as
ingest_worker, not a reader. Because they ALTER objects they must own them, so all
three connect as the shared non-superuser tracksolid_owner (the role the repo already
intends to own these schemas). dashboard_api backend stays a reader (dashboard_app).
- app_roles_tracksolid_db.sql rewritten: tracksolid_owner LOGIN + CONNECTION LIMIT 30
+ GUCs + USAGE/CREATE; Timescale-aware ownership reassignment (skips table-linked
sequences, ALTER MATERIALIZED VIEW for continuous aggregates, leaves reporting.v_trips
with reporting_refresher, reassigns functions); dashboard_app read role.
- Reassignment validated in a rolled-back transaction on the live DB: reassigns the
31-chunk position_history hypertable + the v_mileage_daily_cagg continuous aggregate,
and as tracksolid_owner can ALTER the hypertable and create/drop tables.
- Runbook updated: discovery marked done, ownership folded into the apply (safe while
apps still run as postgres — superuser bypasses ownership), corrected cutover order.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Six service connections run as the postgres SUPERUSER across two databases on the
shared 100-connection server — the root of the "too many connections" peaks and a
standing least-privilege risk. Superuser sessions ignore per-role CONNECTION LIMIT
and can consume the superuser-reserved slots.
Drafts (apply as postgres; nothing applied here):
- scripts/app_roles_tracksolid_db.sql — webhook_app, ingest_app, worker_app,
dashboard_app. Capability groups (ts_app_read / ts_app_write), per-app NOSUPERUSER
login roles with hard CONNECTION LIMIT + bounded GUCs (statement_timeout,
idle_session_timeout, idle_in_transaction, lock_timeout).
- scripts/app_roles_fleet_platform.sql — gateway_app, cron_app (the apps on the
separate fleet_platform DB), fp_app_rw group over its schemas.
- scripts/MIGRATE_APPS_OFF_SUPERUSER.md — runbook: discovery (what each app actually
writes / whether it runs DDL), connection-budget table (sum ≈ 81 < 100), the
object-ownership step for migration-running apps (reassign app schemas to the
existing tracksolid_owner — scoped, never REASSIGN OWNED globally), one-at-a-time
cutover, and instant rollback (DATABASE_URL only).
Grants are best-effort by app function and explicitly call out where to verify before
cutover; all objects are postgres-owned, so row DML works but DDL needs the ownership
step. See the runbook.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
The analytics_ro role only had USAGE/SELECT on reporting + tracksolid, so
the tickets schema (INC/CRQ, 8 tables + 1 view + 7 fns) and fuel schema
were invisible to the MCP server — queries failed with permission denied.
- analytics_ro_role.sql: GRANT USAGE/SELECT/EXECUTE on tickets + fuel.
Default privileges for these are keyed to postgres (their owner), not
tracksolid_owner, so future objects auto-grant correctly.
- analytics_mcp.py: READABLE_SCHEMAS now includes tickets + fuel and is
overridable via MCP_READABLE_SCHEMAS, so the introspection helpers
(list_tables/describe_table/sample_table) work for them too.
- deploy.sh: reuse existing analyst tokens from the running container when
MCP_AUTH_TOKENS is unset, so a code-only redeploy needs no secret.
Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
