ops/dbq.sh: read-only Postgres queries across all environments #18

Merged

admin_emi merged 2 commits from feature/db-query-path into dev

2026-07-24 19:29:00 +00:00

admin_emi commented

2026-07-23 21:54:39 +00:00

Owner

Paves the query path (Postgres first; Iceberg follows the same conventions).

Already applied + verified

Created a thermograph_ro role (NOSUPERUSER, pg_read_all_data only) in dev, beta and prod.
Verified reads on all three: climate_history = 16,434,392 (dev) / 16,487,073 (beta) / 16,541,351 (prod), latest 2026-07-17.
Verified read-only is enforced by Postgres, not convention: create table against prod returns ERROR: permission denied for schema public.

Why exec-into-container, not a connection string
No database is exposed over TCP. Each listens only on its private docker network, and prod's is a Swarm overlay (10.0.2.0/24) the host cannot route to — so ssh -L works for beta but is impossible for prod, and publishing 5432 would require new ufw rules plus a Swarm endpoint change on production. Running psql inside the container works identically in all three with zero infra change. The prod container is a Swarm task whose name changes every redeploy, so it's resolved at call time via docker ps --filter, never hardcoded.

Usage

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 dev -f -

Extra args pass through to psql; stdin is forwarded.

Iceberg path — ops/README.md records the three conventions a sibling iceberg.sh should follow: env-keyed dispatch with the same argument shape, run the engine where the data is reachable (no new network endpoints), and a dedicated read-only identity so the same enforced-not-assumed property holds.

Note: the app's thermograph role is a superuser and is deliberately not used by this tool.

Paves the query path (Postgres first; Iceberg follows the same conventions). **Already applied + verified** - Created a `thermograph_ro` role (NOSUPERUSER, `pg_read_all_data` only) in **dev, beta and prod**. - Verified reads on all three: `climate_history` = 16,434,392 (dev) / 16,487,073 (beta) / 16,541,351 (prod), latest 2026-07-17. - Verified read-only is **enforced by Postgres**, not convention: `create table` against prod returns `ERROR: permission denied for schema public`. **Why exec-into-container, not a connection string** No database is exposed over TCP. Each listens only on its private docker network, and prod's is a Swarm **overlay** (10.0.2.0/24) the host cannot route to — so `ssh -L` works for beta but is *impossible* for prod, and publishing 5432 would require new ufw rules plus a Swarm endpoint change on production. Running `psql` inside the container works identically in all three with zero infra change. The prod container is a Swarm task whose name changes every redeploy, so it's resolved at call time via `docker ps --filter`, never hardcoded. **Usage** ```sh 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 dev -f - ``` Extra args pass through to psql; stdin is forwarded. **Iceberg path** — `ops/README.md` records the three conventions a sibling `iceberg.sh` should follow: env-keyed dispatch with the same argument shape, run the engine where the data is reachable (no new network endpoints), and a dedicated read-only identity so the same enforced-not-assumed property holds. Note: the app's `thermograph` role is a superuser and is deliberately not used by this tool.

admin_emi added 1 commit 2026-07-23 21:54:41 +00:00

Add ops/dbq.sh: read-only Postgres queries across all environments

PR build (required check) / changes (pull_request) Successful in 7s

Details

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

Details

PR build (required check) / build-frontend (pull_request) Has been skipped

Details

PR build (required check) / validate-observability (pull_request) Has been skipped

Details

PR build (required check) / build-backend (pull_request) Successful in 48s

Details

PR build (required check) / gate (pull_request) Successful in 3s

Details

151cf37dfa

Uniform read-only query access to the LAN dev, beta and prod databases, plus a
thermograph_ro role in each to enforce it.

None of the databases are exposed over TCP: each listens only on its private
docker network, and prod's is a Swarm overlay the host cannot route to -- so
ssh -L works for beta but is impossible for prod, and publishing 5432 would mean
new ufw rules and a Swarm endpoint change on production. Running psql inside the
db container works identically everywhere with no ports, tunnels or infra
changes, so dbq.sh does that: local docker exec for dev, ssh for beta/prod. The
prod container is a Swarm task whose name changes each redeploy, so it is
resolved at call time rather than hardcoded.

Queries connect as thermograph_ro (NOSUPERUSER, granted only pg_read_all_data),
so a stray write fails with "permission denied" even against prod; the app's own
superuser role is deliberately unused here. Extra args pass through to psql and
stdin is forwarded.

ops/README.md documents usage and the conventions Iceberg will follow when it
lands as a sibling (env-keyed dispatch, run the engine where the data is
reachable, dedicated read-only identity).

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

Add ICEBERG-HANDOFF.md: implementation spec for the Iceberg query service

PR build (required check) / changes (pull_request) Successful in 16s

Details

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

Details

PR build (required check) / build-frontend (pull_request) Has been skipped

Details

PR build (required check) / validate-observability (pull_request) Has been skipped

Details

PR build (required check) / build-backend (pull_request) Successful in 1m2s

Details

PR build (required check) / gate (pull_request) Successful in 3s

Details

bb7ce43902

Handoff for the Iceberg worker: the contract iceberg.sh must satisfy (same
interface shape as dbq.sh, read-only enforced by the system, no new network
endpoints, call-time container resolution), the environment constraints that
will otherwise bite -- prod's Swarm overlay is unroutable from its own host so
tunnels are impossible, Contabo object storage requires path-style addressing,
the backups/ prefix is live, and beta's /etc/thermograph.env is unreadable by
the deploy user -- plus the decisions to report back, acceptance criteria with
a demonstrated refused write, and anti-goals.