thermograph/infra/ops

ops/ — operator + agent query tooling

Read-only query access to Thermograph data, uniform across environments.

dbq.sh — Postgres, any environment

infra/ops/dbq.sh dev  "select count(*) from climate_history"
infra/ops/dbq.sh prod -tA -c "select max(date) from climate_history"
infra/ops/dbq.sh beta -c '\dt'
echo "select 1" | infra/ops/dbq.sh prod -f -

Environments: dev (LAN compose stack on the dev machine), beta (75.119.132.91), prod (169.58.46.181). Extra args pass straight through to psql (-tA, -x, --csv, -f, …); stdin is forwarded.

Why it execs into the container instead of using a connection string

None of the databases are exposed over TCP — each listens only on its private docker network. Prod's is a Swarm overlay (10.0.2.0/24) that the host cannot route to, so ssh -L works for beta but is impossible for prod; publishing 5432 would mean new ufw rules plus a Swarm endpoint change on production. Running psql inside the db container works identically in all three environments with no ports, no tunnels, and no infra changes.

The prod container name is a Swarm task name that changes on every redeploy, so it is resolved at call time via docker ps --filter name=…, never hardcoded.

Safety

Queries connect as thermograph_ro — a NOSUPERUSER role granted only pg_read_all_data. Read-only is enforced by Postgres, not by convention:

$ infra/ops/dbq.sh prod -c "create table t(i int)"
ERROR:  permission denied for schema public

The app's own thermograph role is a superuser and is deliberately not used by this tool. If the role is ever missing (fresh database), recreate it with:

CREATE ROLE thermograph_ro LOGIN;
GRANT CONNECT ON DATABASE thermograph TO thermograph_ro;
GRANT pg_read_all_data TO thermograph_ro;

iceberg.sh — the Iceberg lake, any environment

infra/ops/iceberg.sh dev  -c "show tables"
infra/ops/iceberg.sh prod -c "select count(*) from era5_daily"
infra/ops/iceberg.sh beta -json -c "select * from era5_daily limit 3"
echo "select 1" | infra/ops/iceberg.sh prod -f -

Same environments and argument shape as dbq.sh (spec: ICEBERG-HANDOFF.md). Extra args pass straight through to the duckdb CLI (-json, -csv, -line, -c, -f, …); with -f -, stdin is forwarded and read as piped SQL; a single bare argument is treated as -c SQL.

Why an ephemeral DuckDB container instead of a query service

The lake is one shared warehouse in Contabo object storage (s3://era5-thermograph/iceberg), reachable from every environment with the same credentials — so unlike Postgres there is no per-env database to reach, and the environments differ only in where the engine runs: a throwaway docker run of a small DuckDB image (duckdb CLI + httpfs/iceberg extensions pre-installed) on the target box — locally for LAN dev, over SSH for beta/prod. The image is built on the host the first time it's needed from a Dockerfile embedded in the script; its tag is a hash of that Dockerfile, so editing the script rolls every host forward on the next call.

A container per query means no daemon, no listening port, no tunnel (prod's overlay cannot be tunnelled), no firewall or Swarm changes — sudo ss -ltnp is identical before and after a query. It also never execs into an app container and resolves no container names, so prod redeploys can't break it.

There is no catalog service to run or expose: an Iceberg table's current state is fully described by the newest metadata JSON under <warehouse>/<table>/metadata/. iceberg.sh resolves that at call time (numeric metadata version, newest wins) and exposes each table directory as a view of the same name — which is also how it keeps reading fresh snapshots of a table that is still being loaded.

Credentials are resolved at call time and never stored in the repo: the target host's /etc/thermograph.env (THERMOGRAPH_LAKE_S3_*) wins if present; otherwise the calling machine decrypts the SOPS vault (infra/deploy/secrets/prod.yaml) and hands the two values to the remote shell over stdin — never argv, so never visible in ps, and never written to a file on the target.

Safety

Every session locks itself down before user SQL runs:

SET allowed_directories=['s3://era5-thermograph/iceberg/', 's3://era5-thermograph/era5/'];
SET enable_external_access=false;
SET lock_configuration=true;

Both prefixes are required for reads: era5_daily was built with pyiceberg add_files over the existing hive parquet, so its metadata lives under iceberg/ while its data files stay in place under era5/daily/. DuckDB itself then refuses everything else — the live backups/ prefix, all local files — and the lockdown cannot be SET away by query text:

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

Known gap: the only provisioned keypair for the bucket is read-write, so a raw COPY TO targeting a path inside the two allowed prefixes is not refused — the DuckDB iceberg extension cannot write Iceberg tables, but it could still clobber raw objects under iceberg/ or era5/. Path-based restriction cannot both allow reading a prefix and forbid writing it; closing the gap needs a scoped read-only keypair (the object-storage equivalent of thermograph_ro). When the operator mints one, render it as THERMOGRAPH_LAKE_S3_* on the hosts or swap it into the vault — iceberg.sh needs no code change.