apache / apache/texera

Remove Postgres credentials from CU Master and CU Worker

Open
#5,011 0 comments 0 reactions 1 assignee Claimed by @bobbai00 View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.