ops/iceberg.sh: read-only Iceberg lake queries across all environments #23

Merged

admin_emi merged 4 commits from feat/iceberg-ops into dev

2026-07-24 19:34:32 +00:00

admin_emi commented

2026-07-23 22:49:55 +00:00

Owner

Implements infra/ops/ICEBERG-HANDOFF.md: infra/ops/iceberg.sh, a sibling of dbq.sh with the same env-keyed dispatch, plus the README rationale section. Base is feature/db-query-path (PR #18), which carries the handoff and the dbq.sh reference this mirrors.

Decisions (as the handoff demands)

Catalog: none at query time. The lake writer keeps its sqlite catalog on the writer side; the readable contract is each table's newest metadata JSON under <warehouse>/<table>/metadata/. iceberg.sh resolves that at call time (numeric metadata version, newest wins — handles both vN.metadata.json and pyiceberg's 0000N-<uuid>.metadata.json) and exposes one view per table directory. Nothing to host, nothing to expose, and reads automatically track a table that is still being loaded.
Engine: DuckDB CLI + httpfs/iceberg extensions, the lightest engine satisfying the contract. It runs as a throwaway docker run per query; the image is built on the target host on first use from a Dockerfile embedded in the script (extensions installed at build time, so queries need no downloads). The image tag is a hash of the Dockerfile, so editing the script rolls every host forward on its next call.
Warehouse: s3://era5-thermograph/iceberg — the single shared bucket, one warehouse for every environment. Environments are not isolated by prefix/namespace/bucket; they see the same lake and differ only in where the engine container runs. Note era5_daily's data files live under era5/daily/ (adopted in place by add_files); only its metadata is under iceberg/.
Read-only mechanism: engine lockdown before user SQL runs — SET allowed_directories=['s3://era5-thermograph/iceberg/', 's3://era5-thermograph/era5/']; SET enable_external_access=false; SET lock_configuration=true;. Writes outside the lake prefixes (notably backups/), all local-file access, and any attempt to lift the lockdown are refused by DuckDB itself (pasted below). Known gap: see the operator ask.
Where the engine runs: dev = local docker run on the dev machine; beta/prod = docker run on the box over SSH, same dispatch as dbq.sh. No published ports, no tunnels, nothing persistent, no exec into app containers, no container names resolved.

Credentials resolve at call time: the target host's /etc/thermograph.env (THERMOGRAPH_LAKE_S3_*) wins if present (it is not rendered on either box today); otherwise the calling machine decrypts the SOPS vault and feeds the two values to the remote shell over stdin — never argv (so never visible in ps), never a file on the target. This branch's vault predates the THERMOGRAPH_LAKE_S3_* keys (they are in dev's vault); until the chain merges, point THERMOGRAPH_LAKE_VAULT at a dev-branch vault copy or export the two keys directly.

Acceptance

Rows in each environment. The lake currently has one table, era5_daily, mid-load by the lake builder — counts below are snapshots of a moving table, which is the call-time metadata resolution working as intended (4,536,288 → 27,217,728 → 49,899,168 → 163,306,368 across the test session):

$ infra/ops/iceberg.sh dev -c "show tables"
┌────────────┐
│    name    │
├────────────┤
│ era5_daily │
└────────────┘
$ infra/ops/iceberg.sh prod -c "select count(*) as rows from era5_daily"
┌───────────┐
│   rows    │
├───────────┤
│ 163306368 │
└───────────┘
$ infra/ops/iceberg.sh beta -c "select date, tmax, tmin from era5_daily limit 3"
┌────────────┬───────────┬───────────┐
│    date    │   tmax    │   tmin    │
├────────────┼───────────┼───────────┤
│ 1940-01-01 │  43.89023 │ 40.943806 │
│ 1940-01-02 │ 43.418365 │  38.97077 │
│ 1940-01-03 │  40.90052 │  38.02221 │
└────────────┴───────────┴───────────┘
$ infra/ops/iceberg.sh prod -c "select date, tmax, tmin from main.era5_daily limit 2"   # namespace form
(2 rows)

Early acceptance ran against a throwaway 3-row table iceberg/_ops_test/ (created while era5_daily did not exist yet); it has been deleted from the warehouse since.

Write refused by the system, on every environment (prod shown; dev/beta byte-identical):

$ infra/ops/iceberg.sh prod -c "COPY (SELECT 1 AS i) TO 's3://era5-thermograph/backups/.iceberg-refusal-probe'"
Permission Error: Cannot access file "s3://era5-thermograph/backups/.iceberg-refusal-probe" - file system operations are disabled by configuration
(exit 1)
$ infra/ops/iceberg.sh dev -c "COPY (SELECT 1 AS i) TO '/tmp/x.csv'"
Permission Error: Cannot access file "/tmp/x.csv" - file system operations are disabled by configuration
(exit 1)
$ infra/ops/iceberg.sh prod -c "SET enable_external_access=true"
Invalid Input Error: Cannot change configuration option "enable_external_access" - the configuration has been locked
(exit 1)

The refusal is path-based and fires in the engine before any request is made (verified: identical refusal with dummy credentials). DDL against the lake is impossible by construction — no writable catalog is attached and the DuckDB iceberg extension has no write path here; CREATE TABLE only ever creates an in-memory scratch table that dies with the container.

Honest residual, demonstrated: the only provisioned keypair is read-write, and a path filter cannot allow reading a prefix while forbidding writes to it, so a raw COPY TO inside the two lake prefixes is not refused:

$ infra/ops/iceberg.sh dev -c "COPY (SELECT 1 AS i) TO 's3://era5-thermograph/iceberg/_ops_test/_write_probe.csv'"
(exit 0 — succeeded; probe object deleted afterwards)

This is stated in the README as a known gap rather than silently accepted — see the operator ask below.

No new exposure. sudo ss -ltnp captured on prod and beta before and after the full test session: diff is empty on both. No ufw changes, no Swarm service or EndpointSpec changes, no new units — the only host-side artifact is the local engine image (thermograph-iceberg:<hash>), which listens on nothing.
Non-interactive; flags and stdin pass through.

$ infra/ops/iceberg.sh dev -json -c "select 9 as nine"
[{"nine":9}]
$ echo "select count(*) as rows from era5_daily;" | infra/ops/iceberg.sh beta -f -
┌───────────┐
│   rows    │
├───────────┤
│ 140624928 │
└───────────┘
$ infra/ops/iceberg.sh dev "select 1"          # bare-arg convenience, as dbq.sh documents
$ tail -f /dev/null | infra/ops/iceberg.sh dev -c "select 1 as ok"   # never-closing stdin: returns, no hang

Prod resolution survives redeploys — trivially: nothing execs into app containers and no container names exist to resolve; the engine is an ephemeral container created per query.
README updated with usage and the why, same shape as the Postgres section.

Not demonstrable now, stated explicitly:

A permission denied-style refusal of in-prefix writes (the Postgres thermograph_ro bar) — blocked on the scoped read-only keypair below.
Host-env credential path (THERMOGRAPH_LAKE_S3_* in /etc/thermograph.env) — the deploy renderer does not put lake keys on the hosts yet, so all runs used the caller-side sops path.

Operator asks

Mint a scoped read-only keypair for era5-thermograph (the object-storage equivalent of thermograph_ro). With the current single RW keypair, read-only inside the lake prefixes is enforced convention-plus-lockdown, not identity. Once it exists, render it as THERMOGRAPH_LAKE_S3_* on the hosts or swap it into the vault — iceberg.sh needs no code change.
Optionally have render-secrets.sh render THERMOGRAPH_LAKE_S3_* into /etc/thermograph.env on beta/prod, so remote queries stop depending on a caller-side sops decrypt.

Shellcheck-clean (relevant to the open shell-lint CI PR).

Implements `infra/ops/ICEBERG-HANDOFF.md`: `infra/ops/iceberg.sh`, a sibling of `dbq.sh` with the same env-keyed dispatch, plus the README rationale section. Base is `feature/db-query-path` (PR #18), which carries the handoff and the `dbq.sh` reference this mirrors. ## Decisions (as the handoff demands) 1. **Catalog: none at query time.** The lake writer keeps its sqlite catalog on the writer side; the readable contract is each table's newest metadata JSON under `<warehouse>/<table>/metadata/`. `iceberg.sh` resolves that at call time (numeric metadata version, newest wins — handles both `vN.metadata.json` and pyiceberg's `0000N-<uuid>.metadata.json`) and exposes one view per table directory. Nothing to host, nothing to expose, and reads automatically track a table that is still being loaded. 2. **Engine: DuckDB CLI + httpfs/iceberg extensions**, the lightest engine satisfying the contract. It runs as a throwaway `docker run` per query; the image is built on the target host on first use from a Dockerfile embedded in the script (extensions installed at build time, so queries need no downloads). The image tag is a hash of the Dockerfile, so editing the script rolls every host forward on its next call. 3. **Warehouse: `s3://era5-thermograph/iceberg`** — the single shared bucket, one warehouse for every environment. Environments are not isolated by prefix/namespace/bucket; they see the same lake and differ only in where the engine container runs. Note `era5_daily`'s data files live under `era5/daily/` (adopted in place by `add_files`); only its metadata is under `iceberg/`. 4. **Read-only mechanism: engine lockdown before user SQL runs** — `SET allowed_directories=['s3://era5-thermograph/iceberg/', 's3://era5-thermograph/era5/']; SET enable_external_access=false; SET lock_configuration=true;`. Writes outside the lake prefixes (notably `backups/`), all local-file access, and any attempt to lift the lockdown are refused by DuckDB itself (pasted below). Known gap: see the operator ask. 5. **Where the engine runs:** dev = local `docker run` on the dev machine; beta/prod = `docker run` on the box over SSH, same dispatch as `dbq.sh`. No published ports, no tunnels, nothing persistent, no exec into app containers, no container names resolved. **Credentials** resolve at call time: the target host's `/etc/thermograph.env` (`THERMOGRAPH_LAKE_S3_*`) wins if present (it is not rendered on either box today); otherwise the calling machine decrypts the SOPS vault and feeds the two values to the remote shell over stdin — never argv (so never visible in `ps`), never a file on the target. This branch's vault predates the `THERMOGRAPH_LAKE_S3_*` keys (they are in `dev`'s vault); until the chain merges, point `THERMOGRAPH_LAKE_VAULT` at a dev-branch vault copy or export the two keys directly. ## Acceptance - [x] **Rows in each environment.** The lake currently has one table, `era5_daily`, mid-load by the lake builder — counts below are snapshots of a moving table, which is the call-time metadata resolution working as intended (4,536,288 → 27,217,728 → 49,899,168 → 163,306,368 across the test session): ``` $ infra/ops/iceberg.sh dev -c "show tables" ┌────────────┐ │ name │ ├────────────┤ │ era5_daily │ └────────────┘ $ infra/ops/iceberg.sh prod -c "select count(*) as rows from era5_daily" ┌───────────┐ │ rows │ ├───────────┤ │ 163306368 │ └───────────┘ $ infra/ops/iceberg.sh beta -c "select date, tmax, tmin from era5_daily limit 3" ┌────────────┬───────────┬───────────┐ │ date │ tmax │ tmin │ ├────────────┼───────────┼───────────┤ │ 1940-01-01 │ 43.89023 │ 40.943806 │ │ 1940-01-02 │ 43.418365 │ 38.97077 │ │ 1940-01-03 │ 40.90052 │ 38.02221 │ └────────────┴───────────┴───────────┘ $ infra/ops/iceberg.sh prod -c "select date, tmax, tmin from main.era5_daily limit 2" # namespace form (2 rows) ``` Early acceptance ran against a throwaway 3-row table `iceberg/_ops_test/` (created while `era5_daily` did not exist yet); it has been deleted from the warehouse since. - [x] **Write refused by the system**, on every environment (prod shown; dev/beta byte-identical): ``` $ infra/ops/iceberg.sh prod -c "COPY (SELECT 1 AS i) TO 's3://era5-thermograph/backups/.iceberg-refusal-probe'" Permission Error: Cannot access file "s3://era5-thermograph/backups/.iceberg-refusal-probe" - file system operations are disabled by configuration (exit 1) $ infra/ops/iceberg.sh dev -c "COPY (SELECT 1 AS i) TO '/tmp/x.csv'" Permission Error: Cannot access file "/tmp/x.csv" - file system operations are disabled by configuration (exit 1) $ infra/ops/iceberg.sh prod -c "SET enable_external_access=true" Invalid Input Error: Cannot change configuration option "enable_external_access" - the configuration has been locked (exit 1) ``` The refusal is path-based and fires in the engine before any request is made (verified: identical refusal with dummy credentials). DDL against the lake is impossible by construction — no writable catalog is attached and the DuckDB iceberg extension has no write path here; `CREATE TABLE` only ever creates an in-memory scratch table that dies with the container. - [x] **Honest residual, demonstrated:** the only provisioned keypair is read-write, and a path filter cannot allow reading a prefix while forbidding writes to it, so a raw `COPY TO` *inside* the two lake prefixes is not refused: ``` $ infra/ops/iceberg.sh dev -c "COPY (SELECT 1 AS i) TO 's3://era5-thermograph/iceberg/_ops_test/_write_probe.csv'" (exit 0 — succeeded; probe object deleted afterwards) ``` This is stated in the README as a known gap rather than silently accepted — see the operator ask below. - [x] **No new exposure.** `sudo ss -ltnp` captured on prod and beta before and after the full test session: `diff` is empty on both. No ufw changes, no Swarm service or `EndpointSpec` changes, no new units — the only host-side artifact is the local engine image (`thermograph-iceberg:<hash>`), which listens on nothing. - [x] **Non-interactive; flags and stdin pass through.** ``` $ infra/ops/iceberg.sh dev -json -c "select 9 as nine" [{"nine":9}] $ echo "select count(*) as rows from era5_daily;" | infra/ops/iceberg.sh beta -f - ┌───────────┐ │ rows │ ├───────────┤ │ 140624928 │ └───────────┘ $ infra/ops/iceberg.sh dev "select 1" # bare-arg convenience, as dbq.sh documents $ tail -f /dev/null | infra/ops/iceberg.sh dev -c "select 1 as ok" # never-closing stdin: returns, no hang ``` - [x] **Prod resolution survives redeploys** — trivially: nothing execs into app containers and no container names exist to resolve; the engine is an ephemeral container created per query. - [x] **README updated** with usage and the why, same shape as the Postgres section. Not demonstrable now, stated explicitly: - A `permission denied`-style refusal of in-prefix writes (the Postgres `thermograph_ro` bar) — blocked on the scoped read-only keypair below. - Host-env credential path (`THERMOGRAPH_LAKE_S3_*` in `/etc/thermograph.env`) — the deploy renderer does not put lake keys on the hosts yet, so all runs used the caller-side sops path. ## Operator asks 1. **Mint a scoped read-only keypair for `era5-thermograph`** (the object-storage equivalent of `thermograph_ro`). With the current single RW keypair, read-only inside the lake prefixes is enforced convention-plus-lockdown, not identity. Once it exists, render it as `THERMOGRAPH_LAKE_S3_*` on the hosts or swap it into the vault — `iceberg.sh` needs no code change. 2. Optionally have `render-secrets.sh` render `THERMOGRAPH_LAKE_S3_*` into `/etc/thermograph.env` on beta/prod, so remote queries stop depending on a caller-side sops decrypt. Shellcheck-clean (relevant to the open shell-lint CI PR).

admin_emi added 1 commit 2026-07-23 22:49:56 +00:00

Add ops/iceberg.sh: read-only Iceberg lake queries across all environments

secrets-guard / encrypted (pull_request) Successful in 9s

Details

a7a8d9ae41

The lake is one shared warehouse (s3://era5-thermograph/iceberg), so the
environments differ only in where the engine runs: a throwaway docker run
of a small DuckDB image (httpfs + iceberg extensions baked in) on the
target box, locally for LAN dev and over SSH for beta/prod. No daemon, no
listening port, no tunnel, and no container names to resolve.

No catalog service: each table's newest metadata JSON is resolved at call
time and exposed as a view named after its table directory, so reads track
a table that is still being loaded.

Sessions lock themselves down before user SQL runs (allowed_directories
pinned to the lake prefixes, external access disabled, configuration
locked), so writes to backups/ or local files are refused by the engine
itself. Credentials resolve at call time from the target host's env file
or a caller-side sops decrypt, handed over via stdin, never argv.