Status: Phase 1 built (2026-10-05), under test. The endpoint, profiles
(credentials and rules files), Basic Auth, per-dataset contexts, global limits, tests and a local test script
exist (v4/crates/server/src/sql_endpoint.rs, v4/scripts/sql-try.sh); phase 2
(per-profile limits and the rest) is not started. Earlier status follows.
Decided (2026-10-05). The
section 7 decisions are made (HTTP only, per-dataset, vectors hidden, off by
default behind a config flag, caps configurable). Added the same day:
credentialed access only, strict for mediumroast.io, and the feature is
declared experimental (section 5a). Section 5 is rewritten to match.
Owner: michael.hay@mediumroast.io
Scope: add a V4 endpoint that takes a SQL statement as input and returns rows
from the data V4 already holds (the SIC-family feather tables and the EDGAR
catalog). Preference stated up front: reuse an existing Rust crate rather
than write our own. This document says what exists, what is actually
reusable, and what is left for us to build whichever way we go.
v4/Cargo.lock), already the
engine behind every V4 query. There is no database server; each catalog is
an in-process SessionContext over .feather (Arrow IPC) files.SicCatalog (crates/sic/src/lib.rs): tables sic_data (US SIC) plus
one table per extra system registered with register_system (Japan SIC,
NACE Rev. 2, ISIC Rev. 4). Includes the vector_all_minilm_l6_v2
columns, so array_distance queries work.EdgarCatalog (crates/edgar/src/catalog.rs): table edgar_catalog.ctx.sql(..). User text reaches SQL only through escaping/validation in
those builders. No endpoint accepts SQL from a caller.So the engine, the data and the HTTP layer exist. What is missing is a controlled way to let a caller’s SQL reach the engine.
Three different shapes. They need different crates and I would not conflate them:
| Shape | Caller | Transport | Typical use |
|---|---|---|---|
A. HTTP JSON: POST /V4.0/sql with {"sql": "..."}, rows back as JSON (envelope) |
curl, notebooks, the web UI, anything | HTTP/JSON | ad-hoc exploration, scripts, the Explorer UI |
| B. Arrow Flight SQL (gRPC) | ADBC / JDBC / ODBC Flight drivers, DuckDB/Polars/pandas via ADBC | gRPC, Arrow batches | bulk, typed, fast |
| C. Postgres wire protocol | psql, DBeaver, Tableau, Metabase, any Postgres driver |
TCP :5432 style | BI tools and SQL clients people already own |
A fits “an endpoint” in the sense the rest of V4 uses the word (a route in the OpenAPI doc, behind the same rate limiter and envelope). B and C are separate listeners, not routes.
Versions and dates are from docs.rs / lib.rs on 2026-10-05; I did not build any of them.
datafusion-contrib, built on
pgwire ^0.40. Serves a SessionContext directly: serve(),
serve_with_handlers(), and serve_with_hooks() with a QueryHook trait
for custom query processing; has pluggable authentication and permission
control, and a pg_catalog companion crate so BI tools can introspect.SessionContext from ours cannot be
passed to theirs (different crate types). We would have to pin V4 back to 54
or wait for a 55 release. Documentation coverage is about 40%.flight-sql feature (shape B)arrow-flight crate. The flight-sql feature gives a
FlightSqlService trait to implement; DataFusion ships an example Flight
server that runs SQL. We would still write the service glue, auth and limits
ourselves, but it is mostly glue. Brings tonic/gRPC into the build and a
second port. Third-party wrappers exist (sagitta, rhei-flight) but are
small and young; I would not depend on them.SessionContext::sql_with_options(sql, SQLOptions) with
with_allow_ddl(false), with_allow_dml(false),
with_allow_statements(false); SQLOptions::verify_plan checks an existing
logical plan the same way. This is the supported mechanism for read-only
SQL and is exactly the guard a public endpoint needs. Disallowed statements
include INSERT/UPDATE/DELETE, CREATE/DROP, COPY, CREATE EXTERNAL TABLE and
SET. (A known historic concern is that CREATE EXTERNAL TABLE can reach the
filesystem, which is why it must be off.)RuntimeEnvBuilder (memory pool, e.g.
FairSpillPool) and SessionConfig. I did not confirm the exact 55 builder
method names; that is a first-day check in the build phase.enable_url_table is opt-in, so SELECT * FROM 'file.csv' is not available
unless we turn it on. We will not.There is no crate that gives us shape A as a library. The two HTTP-SQL
projects are standalone servers on old DataFusion versions. But shape A does
not need one: DataFusion already does the hard parts (parsing, planning,
execution, the read-only guard). What we write is a thin axum handler of
roughly 100 to 200 lines plus safety limits. That is the “use the existing
module” answer for A: the module is DataFusion’s sql_with_options; we do
not need another crate.
For B and C real crates exist and are worth using, but each is its own listener, its own auth story and its own deployment surface, and C is blocked on the DataFusion version mismatch today.
POST /V4.0/sql, body {"dataset": "sic" | "edgar", "sql": "SELECT ...", "limit": 1000}.
dataset is required and selects
one query context:
sic: the US SIC table plus the other registered systems (Japan SIC,
NACE, ISIC). They share one context today, so joins across systems
work inside this dataset.edgar: edgar_catalog.
Each dataset gets its own dedicated read-only query context, separate
from the one the existing endpoints use, so exposure rules cannot affect
them.vector_* column removed (for example by dropping columns on the
DataFrame before into_view; the exact call is a build-time check). The
existing contexts keep the vectors for the similarity endpoints. A
consequence: SQL callers cannot run array_distance; semantic search stays
on the dedicated endpoints.sql_with_options with DDL, DML and statements all
off, one statement per request (reject ;-separated batches). URL/file
tables stay disabled (enable_url_table is never called).COMPANY_DNS_SQL_ENABLED
(default false). When off, the route is not registered, so it returns
404 and does not appear in the OpenAPI document; when on, startup logs the
effective limits. Matches how the other settings are read
(COMPANY_DNS_DATA_DIR etc.).COMPANY_DNS_SQL_DEFAULT_ROWS | 1,000 |
| Hard row ceiling (per-request limit is clamped to it) | COMPANY_DNS_SQL_MAX_ROWS | 10,000 |
| Statement timeout | COMPANY_DNS_SQL_TIMEOUT_SECS | 10 |
| Query memory pool | COMPANY_DNS_SQL_MEMORY_MB | 256 |
| Concurrent SQL requests | COMPANY_DNS_SQL_MAX_CONCURRENT | 4 (my addition; say if unwanted) |
These are the server-wide defaults and ceilings (section 5a); a profile may
be given lower values, or higher up to the ceilings. Invalid values fail
startup with a clear message rather than silently falling back. Row cap is enforced server-side (stop reading the stream at
the cap) and truncation is reported as a limitation entry, as the match
endpoint does. Timeout wraps collection with tokio::time::timeout. The
memory pool is set on the query context’s runtime. Max SQL text length
comes from the existing request-body limit layer.User-Agent tiers and a profile’s rate_limit
grant, the same buckets) but without the trusted-Origin bypass, and the
profile’s own SQL limits apply on top. See section 5a.columns (name and Arrow type), rows,
row_count, truncated, and a limitation naming the DataFusion SQL
dialect/version.SELECT * FROM 'file' all rejected); vector columns absent from both
datasets; row cap and clamp; timeout; flag off gives 404; env parsing
(defaults, overrides, invalid).Principle: this endpoint can consume real CPU and memory, so unlike the lookup routes it is closed by default and credential-only. Nobody gets in because of who they appear to be; they get in because they hold a credential the operator issued.
An earlier draft of this section read as if it introduced a new secret
store. It does not. V4 already has the pattern: a runtime secret
(MEDIUMROAST_SHARED_SECRET) sourced from each environment’s own sealed K8s
Secret, never baked into the image, never in the repo, distinct per tier
(v4-deployment.md sections 2 and 3.4; v4-security-hardening.md 3.5).
What is new is that more than one party needs credentials, each with its own
limits and permissions. Decided 2026-10-05: this is a general access
mechanism for V4, not an SQL-specific one. SQL is its first new consumer;
mediumroast.io’s existing rate-limit trust is expressed in the same mechanism.
| Today | General mechanism |
|---|---|
| one secret, one identity (mediumroast.io), trust = skip the limiter | one file with a section per profile: secret plus what that profile may do |
| env var from a sealed K8s Secret | the same secret store, delivered as a mounted file (the credentials file) |
| a bypass only | per-feature grants: rate-limit treatment, SQL access, and whatever comes later |
mediumroast.io is one profile. Open-source users define their own.
Access is a ladder, and each rung buys a better experience:
| Level | Who | What they get |
|---|---|---|
| 1. Anonymous | no User-Agent, or one that fails the format check |
the draconian rate-limit tier (a nudge to identify yourself) |
| 2. Self-identifying | a valid User-Agent such as YourApp/1.0 (contact@example.com) |
the normal rate-limit tier. This existing mechanism stays: it is how a well-behaved caller gets a better experience without any credential. |
| 3. Authenticated | HTTP Basic Auth with a profile from the credentials file | the profile’s grants: lower or no rate limits, and enhanced access such as SQL over REST |
What the plan replaces is only the secret carried in the User-Agent
(the rolling hourly HMAC, secret_ua.rs), because the credentials file with
Basic Auth does that job better. The User-Agent format check and the
normal/draconian tiering are unchanged. The profile’s rate_limit grant (phase 2)
is what moves an authenticated caller up from level 2: a higher quota, or none,
set per profile.
The data routes are gated by a self-identifying User-Agent, a tiered per-IP
limiter, and two bypasses that skip the limiter (rate_limit.rs,
trusted_origin.rs, secret_ua.rs):
Origin/Referer (localhost, *.mediumroast.io). The client
sets these headers; curl -H "Origin: https://mediumroast.io" is accepted
as readily as a browser. Fine for skipping a limit on cheap lookups, not
acceptable as proof of identity for SQL. (Worth a note in
v4-security-hardening.md 3.4: today it means any caller willing to send
that header is unlimited on every data route.)User-Agent. A real credential, but being
replaced by the profiles mechanism below (nothing is implemented against it on the
mediumroast.io side yet).For SQL: Origin/Referer are ignored entirely. The route is inside the same rate
limiter as the lookups (so the User-Agent tiers and a profile’s rate_limit grant
apply, sharing the same buckets) with the trusted-Origin bypass switched off for
it, and the profile’s own SQL limits apply on top. An earlier build put the route
outside the limiter to avoid the Origin bypass; that left SQL with no request-rate
limit and no User-Agent tiering, and was corrected 2026-10-06.
An earlier build kept everything for a profile (hash, grants, limits) in one mixed JSON file. Decided 2026-10-06 to split by what each file holds, so the secret file is tiny and the policy file is not secret and can be reviewed, versioned and shipped like any config:
/etc/passwd: COMPANY_DNS_CREDENTIALS_FILE, one
profile:sha256-of-token line per profile, # comments allowed
(v4/credentials.example). The only secret file, and it holds only hashes.
/run/secrets/). Local development: a gitignored file. Never in the image or the repo.COMPANY_DNS_RULES_FILE, optional JSON with no secrets, so it can live in
a ConfigMap or in git (v4/rules.example.json): a defaults section and per-profile
overrides. A profile starts from the defaults; an override changes only the settings it
names. Inheritance: objects merge key by key, a value or a list replaces, null removes an
inherited setting, and bypass and a quota (requests_per_minute/burst) are alternatives,
so naming one replaces an inherited other. A profile with no entry gets the defaults; an entry
with no credential is a startup error.
{"defaults": {"rate_limit": {"requests_per_minute": 300, "burst": 30}},
"profiles": {
"mediumroast.io": {"rate_limit": {"bypass": true},
"sql": {"datasets": ["sic", "edgar"], "limits": {"max_rows": 500}}},
"partner": {"rate_limit": {"requests_per_minute": 600}}}}
(Fake values, shape only; real numbers are the operator’s.)
Order of precedence for any number: server-wide limits (env, protect the host) clamp the rules (defaults, then the profile’s overrides), which apply to each profile. Both files are read once at startup; a change means a restart, and a bad file stops the server.
Authorization: Basic base64(<profile id>:<token>). The password is a high-entropy generated
token (never a human password). The credentials file stores only a SHA-256 hash of each
token, compared in constant time, so a leaked or dumped file does not hand out working
credentials. A fast hash is right for random 256-bit tokens. Operators make a token and its hash
with standard tools (openssl rand -hex 32, shasum -a 256), documented in the README.
curl -u id:token, every HTTP client library and Swagger UI’s “Authorize” button;
the OpenAPI document declares an HTTP Basic security scheme.401 with
WWW-Authenticate: Basic, never silently anonymous. SQL from an anonymous caller: 401; a
profile without sql: 403; dataset not granted: 403; a limit hit: 429 with Retry-After.User-Agent secret, so Basic Auth replaced it outright. Done 2026-10-05: secret_ua.rs,
MEDIUMROAST_SHARED_SECRET and the hmac dependency are removed (the self-identifying
User-Agent tiering and the trusted-origin check are untouched, with tests). mediumroast.io is
a profile.k8s/prod/deployment.yaml), shared with every other route. So:
rate_limit.rs (per_pod, REPLICA_COUNT), including its
known drift if the replica count changes without updating the constant.sql.limits may override: rows, timeout,
concurrency, requests per minute, memory share. A profile override is always
clamped by the server-wide limits, so a mistake in the rules can
restrict a profile but can never exceed what the hosting system can take.Enforcement order per request: authenticate the profile, check it is granted SQL and the dataset, check its rate and concurrency, check the server-wide budget, then run with the smaller of the profile and server-wide caps.
One structured log line per request: profile id, dataset, hash and length of the SQL (text only at debug), rows, duration, outcome. Failed authentication is logged with source IP and counted. Secrets and tokens are never logged.
v4/README.md and this plan carry an Experimental notice: the SQL
endpoint may change or be removed without a deprecation period; the dialect
is whatever DataFusion version V4 uses; it is off by default; the security and
resource assumptions here are the contract, not a compatibility promise.experimental, summary starting “Experimental:”,
description repeating the notice and the access rules.{"code": "experimental", ...}.V4.0.0’s stability promise until it has run in production for a
period to be defined with the roadmap.v4-deployment.md: section 2 (runtime secrets: credentials and rules files, may be
mounted; Docker secrets named as the equivalent of the sealed K8s Secret);
section 3.4 (per-tier credentials file, rules ConfigMap and per-tier
server-wide limits in the ConfigMap; staging capacity test); Step G;
open questions. Updated.v4-security-hardening.md: 3.5 and 3.4 notes; the profiles mechanism
replaces 3.5: HTTP Basic Auth against hashed profile tokens becomes the single
credential mechanism, with no legacy secret-in-User-Agent path; the self-identifying User-Agent tiering stays. Updated.access module that loads the
credentials and rules and answers “who is this request, and what are they granted”;
the tiered limiter and the SQL handler both ask it (the limiter’s old
single-secret check is already gone). The origin-based trust in
trusted_origin.rs stays for browser-driven lookups for now (browsers cannot
hold a secret); tightening it is the hardening doc’s open question.Yes, this is a test, not a guess. The limits protect the pod, so they should come from measuring a real pod. On staging (2 replicas, same image):
SQLOptions) is sound but we should add the tests above and review
v4-security-hardening.md against it before enabling in production.array_distance).sic, edgar), no combined context.Caps: 1,000 default / 10,000 max rows, 10 s, 256 MB, all configurable.
General solution (2026-10-05): one credentials/rules pair serves every feature, and mediumroast.io’s
rate-limit trust becomes a profile grant. No legacy secret-in-User-Agent path (the self-identifying User-Agent tier stays),
because nothing depends on it yet.
<NAME>_FILE convention (2026-10-06): dropped. It was designed when the one secret was
an env var; now the only secret is the credentials file, which is already a mounted file,
and no secret env var remains. Revisit only if one appears.Still open: only the real server-wide limit values, which come from running the capacity test on staging (section 5c).
Decided 2026-10-05: the endpoint first, per-profile limits afterwards.
Phase 1: the SQL endpoint, with one global set of limits.
COMPANY_DNS_SQL_ENABLED; experimental labelling
(section 5a) from day one.profile:hash) and the
datasets it may query. HTTP Basic Auth,
constant-time hash compare, failed-attempt throttling, audit log line. Flag on
with no valid file fails startup. This is the minimum that keeps the endpoint
closed; there is no anonymous SQL, even in phase 1.sic, edgar) without vector columns,
read-only guard (sql_with_options), one statement per request.Origin is accepted for
nothing), env parsing; OpenAPI entry; README with the experimental notice and
how to create a profile.Phase 2: profiles and limits.
rate_limit grant (v4/crates/server/src/access.rs,
rate_limit.rs). Profiles are now general: COMPANY_DNS_CREDENTIALS_FILE (and optional
COMPANY_DNS_RULES_FILE) load with or without SQL, HTTP Basic Auth identifies a profile on every route, and a
profile may carry rate_limit: {"bypass": true} (no limit) or
{"requests_per_minute": N, "burst": M} (its own quota, fleet-wide, divided across
replicas like the tiers). A profile with no grant is an identified caller on the
normal tier; a wrong credential is a 401. mediumroast.io is one profile (see
v4/credentials.example and v4/rules.example.json). The User-Agent tiers are unchanged for anonymous
callers. Origin/Referer are still never identity.access.rs), replacing the single mixed file.sql.limits: max_rows, timeout_secs,
concurrency, requests_per_minute, burst), each clamped by the server-wide value,
with the SQL route moved inside the rate limiter (no Origin bypass). Not built: a
per-profile memory share (the pool is per query context, so concurrency is the control).v4-deployment.md Step G): per-tier sealed credentials file and rules ConfigMap
and ConfigMap limits.v4-security-hardening.md; define what “experimental” must prove before it
is promoted. Default stays off.Not planned: Flight SQL (3.2) and Postgres wire (3.1), kept for reference if bulk or BI-tool users appear.