Remove Postgres credentials from CU Master and CU Worker
- Dominant language
- Scala
- Stars
- 314
- Forks
- 187
- Avg merge
- 1d 21h
- Merged PRs (30d)
- 214
Description
### Task Summary
## Motivation
CU Master and CU Worker run user-supplied UDF code. Today the CU pod
ships with the database credentials (`STORAGE_JDBC_URL`,
`STORAGE_JDBC_USERNAME`, `STORAGE_JDBC_PASSWORD`) in its environment so
the engine can read and write the metadata tables directly. Anything
that escapes the UDF sandbox can read those env vars and run arbitrary
SQL against the shared Postgres instance — read other users' workflows,
modify execution rows, drop data.
Removing the credentials from the executor closes that exposure. Web-app
should remain the only writer to the metadata DB; the executor should
hold no credentials.
## Current Usage
On `main`, `ComputingUnitMaster.run` opens a JDBC pool at startup:
```scala
SqlServer.initConnection(
StorageConfig.jdbcUrl,
StorageConfig.jdbcUsername,
StorageConfig.jdbcPassword
)
```
Once the pool is open, several engine and service code paths reach
Postgres directly via `SqlServer`:
| Area | File | What it does |
|---|---|---|
| Execution lifecycle | `web/service/WorkflowService.scala` | `ExecutionsMetadataPersistService.insertNewExecution` (INSERT new row), `tryUpdateExistingExecution` (UPDATE status, log_location, runtime_stats_uri, result, etc.). |
| State transitions | `web/storage/ExecutionStateStore.scala` (`updateWorkflowState`) | Persists every workflow state change (READY/RUNNING/COMPLETED/FAILED/…). Called from many sites in `WorkflowService` / `WorkflowExecutionService`. |
| Operator/port URI registry | `web/resource/.../WorkflowExecutionsResource.scala` | `insertOperatorPortResultUri`, `insertOperatorConsoleUri`, `getResultUriByLogicalPortId`, `getLatestExecutionID`, etc. Called by engine code (e.g. `RegionExecutionCoordinator`) and by `SyncExecutionResource`. |
| Result export | `web/resource/.../WorkflowExecutionsResource.scala` (`exportResultToDataset`, `exportResultToLocal`) → `web/service/ResultExportService.scala` | Endpoints hosted on CU Master. `ResultExportService` calls `getLatestExecutionId` + `getResultUriByLogicalPortId` (DB-backed) before opening the Iceberg document and either streaming a download or POSTing to file-service. |
| Result/log cleanup | `web/ComputingUnitMaster.scala` (`cleanExecutions`, `recurringCheckExpiredResults`) | On startup and on a recurring schedule, queries `workflow_executions` for expired rows and updates their status. |
| Cost-based scheduling | `engine/architecture/scheduling/CostEstimator.scala` | `getOperatorExecutionTimeInSeconds` reads the latest successful `workflow_executions.runtime_stats_uri` for a `wid`. |
| Dataset path resolution | `common/workflow-core/.../FileResolver.scala` | `datasetResolveFunc` joins `USER × DATASET × DATASET_VERSION` to translate `/owner/dataset/version/file` into a `dataset://///` URI. Hit during workflow compile. |
`ComputingUnitWorker.scala` itself is trivial (only calls
`AmberRuntime.startActorWorker`), but a Worker process shares the engine
code with the Master, so any of the engine-side call sites above
(notably `RegionExecutionCoordinator` and `CostEstimator`) execute
inside the Worker process when the corresponding actor is hosted there.
That is why Worker pods are also deployed with `STORAGE_JDBC_*` today.
## Proposed Design
Move every direct DB access reachable from CU Master / CU Worker behind
an HTTP service that owns the credentials. The executor holds no JDBC
config and authenticates each call by forwarding the originating user's
JWT.
```
┌─ web-app ──────────────┐
CU Master/Worker ──▶ │ (execution metadata) │ ──▶ Postgres
(JWT only) │ file-service (datasets)│
└─────────────────────────┘
```
## Roadmap
The work splits into two independent tracks — each tracked by its own
sub-issue:
1. **Move execution-related operations out of CU Master / CU Worker.**
Covers everything in the Current Usage table that touches
`workflow_executions` and the operator/port URI registry — execution
lifecycle, state transitions, operator/port URI registry, result
export, result/log cleanup, cost-based scheduling.
2. **Move dataset file path resolution out of CU Master / CU Worker.**
Covers `FileResolver.datasetResolveFunc` (the only DB call site that
isn't about execution metadata).
Each sub-issue defines its own HTTP contract, JWT-forwarding plumbing,
migration order, and removal step. Once both are merged, drop
`SqlServer.initConnection` from `ComputingUnitMaster.run`, strip
`STORAGE_JDBC_*` from the CU pod templates, and add a CI smoke test
that boots CU Master with `unset STORAGE_JDBC_*` and runs an e2e
workflow.
### Task Type
- [x] Refactor / Cleanup
- [ ] DevOps / Deployment / CI
- [ ] Testing / QA
- [ ] Documentation
- [ ] Performance
- [ ] Other
Contributor guide
Assessment
This issue has not been assessed yet.