quack-on-demand · git:20261101.1a2d155 · 2026-11-01 · sha256 2ae917d19351d28c
quack-on-demand git:20261101.1a2d155B
Immutable. This exact content never changes and is served at /api/v1/blob/2ae917d19351d28c.
---
name: quack-on-demand
description: Operate a quack-on-demand FlightSQL gateway - boot/stop the manager, manage tenants/pools/ACLs, inspect nodes, run SQL
---
# Quack on Demand
Quack on Demand is a multi-tenant FlightSQL gateway in front of DuckDB Quack + DuckLake. The manager exposes a REST control plane (`/api/...`) and a React admin UI (`/ui/...`) on the same port (default `:20900`), a FlightSQL edge on a separate port (default `:31338`), and DuckDB's native Quack protocol on a third port (default `:9494`, for `ATTACH 'quack:host:9494'` from any DuckDB). Pools of Quack nodes are spawned as local subprocesses or K8s pods.
Use this skill when the user wants to:
- Boot, restart, or stop the manager
- Create / scale / delete tenants and pools
- Grant / revoke ACLs
- Inspect node health, throughput, latency
- See what SQL recently ran and where
- Run ad-hoc SQL against the FlightSQL edge
- Diagnose typical failure modes (dead nodes, expired sessions, ACL denials)
## Tooling
Everything here runs through the `qod` CLI (PyPI package `qod`) against a live
manager - no source checkout is needed. The pieces:
- `qod start` / `qod stop` / `qod status` - run a manager from the released
uber-jar (auto-downloaded and cached) against your Postgres; `qod setup`
persists the `QOD_*` settings it needs
- `qod serve --demo` - fully self-contained evaluation stack (embedded Postgres)
- `qod <noun> <verb>` - the REST control plane (tenants, databases, pools,
nodes, RBAC, policies, telemetry)
- `qod sql` - run SQL against the FlightSQL edge (one statement, a script, or a REPL)
- Docs: https://docs.starlake.ai/qod (guides, configuration reference, REST API)
Every manager config scalar has a `QOD_*` env-var override (or `PROXY_*` for
FlightSQL edge keys); prefer env vars - the bundled `application.conf` is baked
into the jar.
## CLI setup (do this before CLI-driven operations)
The `qod` CLI is the primary way to drive the manager. Before operating
through it, make sure it is installed, current, and logged in.
**1. Installed and current?**
```bash
qod --version # prints: qod X.Y.Z; command not found = not installed
curl -s https://pypi.org/pypi/qod/json | python3 -c "import sys,json; print(json.load(sys.stdin)['info']['version'])"
```
- Not installed - install it: `uv tool install qod` when `uv` is available,
else `pip install qod`. (`uvx qod@latest ...` also works for one-off runs with no
install.)
- Versions equal, or the installed one ends in `.dev0` (a source checkout) -
proceed.
- Installed older than PyPI - upgrade with the command matching the install
method: `uv tool upgrade qod` (uv tool installs), `pip install -U qod`
(pip); `uvx qod` users resolve the latest on a fresh cache (`uvx qod@latest`
forces it).
- PyPI unreachable (offline, proxy) - skip the check silently; never block
operations on it.
- After an upgrade, `qod skill install` refreshes the locally installed copy
of this skill; it prompts for the target LLM (claude, copilot, gemini, all)
and `--platform <name>` skips the prompt. No-op for plugin installs, which
update via `/plugin marketplace update`.
**2. Logged in?** `qod whoami` shows who the current credential signs in as (the
`qod login` session, or a personal access token in `QOD_TOKEN`). If it errors,
log in first - every non-public command needs a session:
```bash
qod login --username admin # prompts for the password; system realm
qod login --username alice --tenant acme # tenant-scoped principal
```
`qod login` mints a session and stores the token plus the FlightSQL edge
settings in the active CLI profile file (mode 0600; `QOD_CONFIG_FILE`
overrides the path), so subsequent commands need no flags. Non-interactive
alternatives: `qod login --password-stdin` reads the password from the first
line of stdin (CI; no flag or env var ever carries a password), or set
`QOD_TOKEN` to a personal access token (or a session token) in the
environment - each command sends it as `X-API-Key`. There is no static API
key; a 401 says "run qod login, or set QOD_TOKEN to a personal access token". Use
`--profile <name>` (or `QOD_PROFILE`) to keep several managers side by side,
`QOD_MANAGER_URL` for a non-default manager URL, and `qod --json ...`
anywhere you need raw JSON for scripting.
## Booting
Not sure which command you want?
| Command | What it is | Needs |
|---|---|---|
| `qod serve --demo` | throwaway showcase on sample data, insecure by design | nothing |
| `qod serve ./your-data` | persistent gateway over your own data, secure defaults | nothing |
| `qod start` | your deployment: your own Postgres, your config | Postgres + `qod setup` |
On Kubernetes, the Helm chart is published as an OCI artifact per release:
`helm install qod oci://ghcr.io/starlake-ai/charts/quack-on-demand --version <release>`
(external Postgres required, `postgres.user` must be set to a dedicated
non-superuser role, and `admin.password` or `admin.existingSecret` is required,
since nobody could log in without an admin; the nodes refuse tenant users every file outside their
authorized locations, so they cannot read a node's mounted catalog password; see https://docs.starlake.ai/qod). Locally,
`qod start` supervises the released uber-jar, downloaded and cached on first
use (Java 21+ required; the `duckdb` CLI and node spawn scripts are
provisioned automatically):
```bash
# One-time: persist Postgres coordinates and TLS prefs so a bare
# `qod start` works afterwards (a real env var still wins). The admin password
# is not stored: the first `qod start` asks for it
qod setup
# Default: TLS edge, DB auth on, Postgres state, admin user seeded
qod start
# Pin a release, or run a jar you already have
qod start --version 0.8.3
qod start --jar /path/to/quack-on-demand-assembly.jar
# QOD_AUTH_DB_ENABLED=false (no password login) boots only with another
# FlightSQL authenticator: a Keycloak or Azure password backend
# (QOD_AUTH_KEYCLOAK_ENABLED / QOD_AUTH_AZURE_ENABLED) or a bearer provider
# (JWT_SECRET_KEY / JWT_PUBLIC_KEY_PATH, QOD_AUTH_GOOGLE_ENABLED,
# QOD_AUTH_AWS_ENABLED), plus a way to sign in to the UI (Keycloak, Azure, or
# system-scope OIDC: QOD_MGMT_IDENTITY_SOURCE=oidc with QOD_MGMT_OIDC_ISSUER_URL
# and QOD_MGMT_OIDC_CLIENT_ID). System OIDC gates only the UI and REST, so on
# its own it is refused at boot
# Is anything running, and what is it serving? (pools need a qod login
# session or QOD_TOKEN)
qod status
# Stop everything (manager + quack nodes)
qod stop
```
**Starflow next to the manager.** `--with-starflow` on `qod start` or `qod serve`
also runs Starflow (the Starlake API + UI), installing it on first use (into
`STARLAKE_HOME`, else an existing `~/starlake`, else the qod cache). Starflow
stores its metadata in a `starlake` database on the manager's own Postgres
server, and qod pairs the two for SSO, REST and FlightSQL (a shared session
secret, generated once and kept in the CLI config; there is no shared API key:
Starflow calls the manager with the signed-in user's own session).
```bash
qod start --with-starflow
qod serve ./sales.duckdb --with-starflow --starflow-port 9000
# clients on other machines: give the browser-facing URLs
QOD_PUBLIC_BASE_URL=https://qod.example.com \
qod start --with-starflow --starflow-url https://starflow.example.com
# extra Starflow-only settings (OAuth client ids, mail, ...)
qod start --with-starflow --starflow-env-file ./starflow.env
```
Starflow logs to `logs/starflow.log` under the qod data dir; Ctrl-C and
`qod stop` stop it before the manager. Not supported on Windows yet, and not
with `--demo` or when `qod serve` would attach to a manager already running.
**Serve your own data in one command.** `qod serve <target>` provisions a
tenant, database, and pool around data the user already has, on a persistent
embedded Postgres, so there is no external prerequisite:
```bash
qod serve ./sales.duckdb # existing DuckDB file (kind=duckdb-file, 1 dual node)
qod serve ./warehouse/ # directory of parquet/csv -> views (kind=memory)
qod serve s3://bucket/sales/ # remote prefix -> a hive-partitioned view
qod serve # a fresh empty DuckLake to load into
qod serve ./sales.duckdb --tenant acme --name sales --pool bi
qod serve s3://bucket/wh/ --table orders=s3://bucket/wh/orders/**/*.parquet
```
Every step is ensure-semantics (create only what is missing, never delete), so
re-running is safe and adds a second database beside the first rather than
replacing it. On its own boot the manager provisions the tenant, database and
pool itself, from a spec file `qod serve` hands it, and reports the outcome in
`serve-provision.outcome.json` beside that spec, in qod's data dir: an object
that exists and matches is left alone, one that differs is refused, nothing is
ever updated or deleted, and later runs need no credential (see "Typical failure
modes" for a refused step). If a manager is already running locally (loopback
only), `qod serve` provisions straight into it instead of booting a second JVM,
with your own credential: `QOD_TOKEN`, the session that `qod login` saved, else a
password prompt on a terminal; with none it refuses ("run qod login, set
QOD_TOKEN, or run qod serve in a terminal"). A manager at a non-loopback URL is
refused rather than attached to. Credentials for a
remote prefix come from `--access-key-id` /
`--secret-access-key`, plus `--region` and (s3 only) `--endpoint` (flag-only,
no env fallback); for an `s3://`/`s3a://`/`r2://` prefix these fall back to
the ambient `AWS_*` environment, while a `gs://` (`gcs://` alias) or `az://`
prefix takes the same two credential flags with per-scheme meaning (gs: HMAC
key id/secret; az: storage account name/key, both required together) and no
environment fallback.
Posture, unlike `qod serve --demo` (`qod start --demo` still works too, as a
deprecated alias): TLS on, DB auth on (the nodes check grants in every mode), and an admin password you
choose on the first run (prompted twice, or taken from `QOD_ADMIN_PASSWORD` in
your shell). It is never stored by the CLI, and later runs do not ask again.
Rotate with `qod auth change-password`; if it is lost, or the account is locked
or disabled, run `qod admin reset-password` (a disabled superuser is re-enabled,
and its personal access tokens work again, only when `--username` names it; a
reset without `--username` leaves it disabled and says so; works with the manager down against an external control plane; it never starts Postgres, so the `qod serve` embedded one must be running, else add `--external` or start `qod serve` first). With the default `QOD_ADMIN_USERNAME` (`admin@localhost.local,admin`) both superusers share that first-boot password: rotate each one (`qod auth change-password --username <name>` per name); `qod admin reset-password` resets every configured admin name by default (`--username` targets one).
The embedded control plane lives at `<user-data-dir>/pg` on a fixed port
(25432 by default, `--pg-port`), persists across restarts, and is never deleted.
`qod status` reports its coordinates. It is a single-node evaluation and
small-team mode: point the manager at your own Postgres (`qod setup`,
`qod start`) for production, and HA refuses to boot with it.
Neither `qod serve` nor `qod start` stores the admin password: it is seeded
once, when the control plane has no admin row, and a later `qod start` against
the same control plane keeps it (no prompt). A
`memory` database whose views point at a remote prefix carries that prefix as
its object-store scope. `qod status` reports the embedded control plane by probing
its data directory; a custom `--pg-data-dir` run is only visible to `status`
when `QOD_PG_EMBEDDED_DATA_DIR` is set (env or `qod setup --set`).
`qod start` runs the manager in the foreground; Ctrl-C tears the manager and
its nodes down gracefully (same as `qod stop` from another terminal - never
kill the JVM directly, or DuckDB node processes are orphaned holding ports
`21900+`). Durable state (`certs/`, DuckLake data, node state) lives under the
platform user-data dir (`~/.local/share/qod` on Linux,
`~/Library/Application Support/qod` on macOS); jars and the provisioned duckdb
CLI cache under the user-cache dir. The admin is
`admin@localhost.local` with the password chosen at first boot (no built-in
default; rotate with `qod auth change-password`, recover with
`qod admin reset-password`). The
FlightSQL edge logs `auth: providers configured` at startup; a manager with no
FlightSQL authenticator refuses to boot instead.
**Self-contained demo.** For evaluation with no external Postgres and no
Docker, `qod serve --demo` boots everything against an embedded, ephemeral
Postgres (zonky), seeds the minimal demo, and tears it all down on exit
(`qod setup`'s stored config is deliberately not applied). `qod start --demo`
still works too, as a deprecated alias:
```bash
qod serve --demo
```
It creates a demo home under `/tmp/qod-demo` (override `QOD_DEMO_HOME`) holding the embedded PG data dir + the DuckLake data path, runs the whole demo config overlay (FlightSQL TLS on with a self-signed cert, so the banner's snippets skip verification; ACL/RLS/CLS on) - a posture produced ONLY on this code path, never on a normal `qod start` boot - seeds tenant `acme` (`acme_tpch.tpch1`) with TPC-H at SF 0.1, and prints a connect banner. Seeded principals: `alice`/`demo-alice` (analyst - sees `c_phone` masked + only `BUILDING` rows), `acme-admin`/`demo-acme-admin` (full), and any ungranted table is denied. Ctrl-C stops the manager, stops the embedded PG, and deletes the demo home. Insecure by design; not for production.
Bootstrap is driven by `QOD_BOOTSTRAP_YAML` - a path (or `classpath:` reference) to a YAML manifest. Bootstrap runs only when you request demo data: pass `LOAD_TPCH=1` or `LOAD_TPCDS=1` (or `LOAD_SSB=1`, or `LOAD_TPC=1` for all) in the environment of `qod start`, which seeds the benchmark in the background and sets `QOD_BOOTSTRAP_YAML` to the bundled demo manifest (`classpath:bootstrap-demo.yaml`). A bare `qod start` does NOT bootstrap. The demo manifest imports two tenants (`acme` with pools `bi` and `etl`, `globex` with pool `bi`), 2 nodes per pool, and a starter RBAC role graph. The import is idempotent: it is skipped when the demo tenants already exist, so restarting the manager is safe. (`LOAD_*` seeding is not yet supported on Windows through `qod start` - the bundled loaders are bash.)
A second profile targets fronting a single DuckDB instance: `DEMO=minimal` (with any
`LOAD_*` flag) imports `bootstrap-demo-minimal.yaml` instead: tenant `acme` only, one pool
`bi` with a single dual node serving reads and writes, and the analyst RLS/CLS demo. Use
`DEMO=full` (the default) for the multi-tenant demo. The profile is only
consulted when `QOD_BOOTSTRAP_YAML` is unset, and bootstrap only imports into a fresh
control plane, so switch profiles with `NUKE=1`:
NUKE=1 DEMO=minimal LOAD_TPCH=1 qod start
On a terminal, `NUKE=1` asks you to type the control-plane database's name
before proceeding (it drops the control plane and the demo tenant-dbs and
wipes the local state dirs); non-tty runs skip the prompt (a script that
wants no prompt redirects stdin, e.g. `< /dev/null`).
`DEMO=minimal` plus `LOAD_TPCDS` warns and skips the TPC-DS loader (no globex tenant in
this profile).
## Auth flow (REST + UI)
The REST API accepts two credentials, sent as `X-API-Key` (the header keeps its
historical name) or, from the browser, as the `qod_session` cookie:
1. **Session token** minted via `POST /api/auth/login` (`qod login`)
2. **Personal access token** (`qod_pat_...`), minted from a session
(see "Personal access tokens and the MCP server" below)
```bash
# Mint a session and store it in the CLI profile (admin role required)
qod login --username admin # prompts for the password
# CI and scripts: the password on stdin instead of a prompt
printf '%s\n' "$ADMIN_PASSWORD" | qod login --username admin --password-stdin
# Every subsequent command rides that session
qod pool list
```
There is no static API key and no open mode: every non-public `/api/...` call needs a session or a PAT, and a call without one answers 401. A `QOD_API_KEY` still set is ignored, with a warning at boot (see "Typical failure modes"). For automation, mint a PAT once from a session (`qod auth pat create`) and hand it over as `QOD_TOKEN`.
### Regular-user login (profile only)
The admin UI isn't admin-exclusive: a tenant-scoped `kind=user` principal can log in too, with the same `/api/auth/login` call plus a `tenant` (the blank/system login and OIDC SSO still require an admin grant). The resulting session is demoted to a fixed allowlist - `whoami`, `logout`, `/api/profile/usage`, `/api/profile/statements`, `/api/profile/pools` and its own PATs - and gets `403 admin_required` on everything else; the UI lands such a session straight on `/profile` (change own password; view own usage + recent statements).
```bash
# Log in as a regular tenant user (demo credentials from the bootstrap manifest)
qod login --username alice --tenant acme # prompts for the password (demo-alice)
# Own usage, recent statements and the pools this user may connect to - the only
# data endpoints this session can reach
qod profile usage --days 7
qod profile statements --limit 20
qod profile pools
```
`qod profile pools` (`GET /api/profile/pools`, any session or PAT, admin or not) lists the
`tenant`, `tenantDb`, `pool` and `status` (`ready` or `disabled`) of every pool of the caller's
tenants that it may connect to (every tenant's for a superuser), decided like a FlightSQL
handshake (kill switches, account enabled, then the pool grant or the tenant's OPA) and narrowed
by a PAT's `--database` / `--pool` restrictions. A denied pool is simply absent; an OPA outage
answers `503 authz_unavailable`.
### Personal access tokens and the MCP server
PATs are long-lived bearer credentials for agents and scripts. A PAT acts with exactly
its owner's permissions (admin PATs reach the admin surface, `kind=user` PATs are
demoted to the profile allowlist) and is accepted wherever a session token is, on both
`/api/*` and the MCP endpoint. Management is no longer session-only: a session can
mint, list, revoke and delete any of the caller's own tokens, and a PAT may now mint a
scoped child of itself and may list, revoke and delete within its own subtree only -
never a sibling, its own parent, or any other token of its owner. Revoking a token
cascades to its whole subtree in the same statement, so a stolen token cannot be rolled
forward past its own revocation by minting a successor first. The revoke also kills the
in-flight statements of every token in that subtree (locally at once, across HA replicas
via NOTIFY moments later); the response reports `killedStatements` for the serving
replica, and killed statements show in statement history with status `killed`.
A PAT can also be **scoped** narrower than its owner's own grants, so an AI agent holds
a credential it cannot exceed. Scope axes are `roles` / `databases` / `pools` / `tools`
(each a JSON array of strings), `verbCeiling` (`RO` / `RW` / `DDL` / `ALL`), `dropAdmin`
(boolean, strips superuser/admin standing), and `stmtTimeoutMs` / `maxRows` (integer
caps). Every axis is optional: an axis left out of the request is unrestricted (inherits
the owner's reach); an axis sent as `[]` restricts the token to nothing on that axis.
A PAT credential can mint a further-scoped child PAT of its own (`parentId` / `depth` on
the listing row show the chain), but any axis narrowed at mint time can only be narrowed
further down the chain, never widened back out - the server refuses a widening attempt
with `400 pat_scope_widens`.
**`roles` and `verbCeiling` are inert for a token whose owner is a tenant-less
superuser.** A node allows a superuser's statement without asking the manager, so it
never looks at a permission list, and attenuating an empty permission set changes nothing, and the
example below (`verbCeiling: "RO"`, `dropAdmin: true`) minted by the default
`admin@localhost.local` superuser still produces a token that can `DROP TABLE`. For a
tenant-scoped admin the ceiling does bite, which is exactly what makes this easy to miss
in testing: it only fails silently for a superuser owner. Scope a superuser-owned agent
token with `databases`, `tools` and `dropAdmin` instead, or - better - mint the token
from a tenant-scoped service user in the first place, where `roles` and `verbCeiling`
are load-bearing.
```bash
# Mint an unscoped token (session required; the token is printed ONCE - store it now)
qod auth pat create --name claude-code [--expires-at 2027-01-01T00:00:00Z]
# {"id":"pat-...","name":"claude-code","token":"qod_pat_..."}
# Mint a scoped token for an agent: read-only, one database, two tools, no admin standing
qod auth pat create --name claude-agent \
--database acme_db --tool run_sql --tool list_tables \
--verb-ceiling RO --drop-admin --max-rows 500
# List (metadata only; the raw token is unrecoverable after mint; the scope summary
# and parentId/depth on each row show what an agent's own PAT has minted)
qod auth pat list
# Revoke
qod auth pat revoke --id pat-...
```
`qod auth pat create --help` lists the full scope flag set (`--role`, `--database`,
`--pool`, `--tool` are repeatable; `--verb-ceiling`; `--drop-admin`; `--stmt-timeout-ms`;
`--max-rows`). A flag left off the command line is unrestricted on that axis, not empty -
same rule as the REST body above.
The MCP server lives at `POST /mcp` on the manager port (`QOD_MCP_ENABLED`, default
true). Auth is `Authorization: Bearer <PAT>`, and nothing else: session JWTs are
refused there. Point Claude Code at it with:
```bash
claude mcp add --transport http qod http://localhost:20900/mcp \
--header "Authorization: Bearer qod_pat_..."
```
MCP troubleshooting:
- **401 on every call**: the bearer is a session JWT (refused by design), a former
static API key (removed), or a dead PAT (revoked, expired, owner disabled). Mint a
fresh PAT.
- **Tool missing from tools/list**: wrong tier - admin tools need an admin-owned PAT.
- **run_sql denied**: the ACL message names the table and missing verb; fix the grant.
- **"pool is resuming"**: the suspended pool is waking; retry in a few seconds.
- Row caps: `run_sql` is truncated server-side at `QOD_MCP_MAX_ROWS` (default 500).
### Administering over MCP
An agent holding an admin PAT can drive the entire
control plane through `POST /mcp`, not just data tools - the same surface as the
admin REST API, gated by the same server-side guards (superuser checks, tenant scope,
self/floor guards, mutation gates, audit). Tool families, one line each:
- **Identity** - tenants, users, groups, roles, memberships
- **Access** - role table permissions, column/row policies, pool permissions
- **Pools & nodes** - pool create/scale/suspend/resume/stop/delete, pool settings
(resources, pod template, autoscale, disabled), node restart/quarantine/
max-concurrent, active statements + kill
- **Databases** - tenant-db create/update/delete, metastore defaults
- **Maintenance & tags** - maintenance policies, maintenance runs, tag create/delete/
protect-unprotect (toggles both ways)
- **Time travel** - restore/undrop, list recoverable snapshots
- **Federation** - federated sources and secrets (returns a `federation_disabled`
error if federation isn't wired on this manager)
- **Manifest** - export/import the control-plane YAML manifest
- **PATs** - create/list/revoke/delete, self-scoped: a token only manages its own
subtree, never a sibling or its owner's other tokens
- **Telemetry** - statement history, usage trends/report, server config, audit search
Tenant inference: a tenant-scoped PAT acts in its own tenant automatically (omit
`tenant` from tool arguments). A superuser PAT is cross-tenant and must pass `tenant`
explicitly on every tool call that needs one. Exception: `create_user` always requires an explicit `tenant` -- omitting it
attempts SUPERUSER creation, which only superuser credentials may do.
### Account lockout and self-service password reset
Lockout is opt-in and off by default. Turning it on requires SMTP to be configured first - boot refuses to start otherwise (the error names `QOD_SMTP_HOST`), because a locked-out user with no mail path would have no way back in.
```bash
# Enable lockout: SMTP relay + the lockout switch + the public link base
export QOD_SMTP_HOST=smtp.example.com
export QOD_SMTP_USER=apikey
export QOD_SMTP_PASSWORD=secret
export QOD_SMTP_FROM=no-reply@example.com
export QOD_PUBLIC_BASE_URL=https://qod.example.com
export QOD_AUTH_LOCKOUT_ENABLED=true
export QOD_AUTH_LOCKOUT_MAX_FAILURES=10 # default; locks after this many consecutive bad passwords
# A locked-out user (or anyone who forgot their password) self-serves a reset -
# the endpoint is public (no session needed) and always answers 200, even for
# an unknown username or an account without an email, to avoid leaking existence
qod auth forgot-password --username alice --tenant acme
# The mailed link carries a single-use, 1-hour token; the command prompts for
# the token and the new password:
qod auth reset-password
```
Lockout only ever applies to rows with an `email` set (`qod user create/update --email`). A user with a non-email username and no email is never locked and has no self-service path - recover it with an admin password reset instead:
An email-format username is its own email and cannot be set separately: `qod user create/update --email` with a conflicting value 400s `invalid_email`, and pre-existing such rows were backfilled automatically. This includes the seeded admin (`admin@localhost.local` by default): because its username is email-format, it is auto-assigned `email = username`, so it IS eligible for lockout when lockout is on, and for self-service reset. A locked or forgotten superuser password is recovered with `qod admin reset-password` (writes the new hash straight into the control-plane database and clears `failed_attempts` / `locked_at`; a disabled superuser is re-enabled only when `--username` names it, and a reset without `--username` leaves it disabled and says so; works with the manager down against an external control plane; it never starts Postgres, so for the `qod serve` embedded one that Postgres must be running and a stopped one is refused), or by another superuser through `qod user update --password`; a deleted superuser row comes back at the next boot when `QOD_ADMIN_PASSWORD` is set (the boot seed creates any configured admin whose row is missing). A restart does NOT reset it: the admin is seeded only when its row is missing, and `QOD_ADMIN_PASSWORD` is used on that first boot only (`qod start` / `qod serve` prompt for it then and never store it). Because `admin@localhost.local` is not a routable mailbox, its self-service email reset does not deliver by default - set `QOD_ADMIN_USERNAME` to a real address for email self-recovery.
```bash
qod user update <user-id> --password a-new-password
```
An admin password reset also unconditionally clears the lock (`failed_attempts` and `locked_at`), same as the self-service reset.
### SCIM 2.0 provisioning (IdP-driven user + group lifecycle)
For workforce identity, point an enterprise IdP's SCIM connector (Okta, Entra, Google Workspace) at the per-tenant base URL so users and groups are provisioned, updated and deprovisioned automatically instead of by hand. This complements per-tenant OIDC SSO: SSO authenticates the login, SCIM manages the accounts.
- **Base URL**: `https://qod.example.com/api/scim/v2/<tenant>` (the tenant id or its display name both work).
- **Auth**: `Authorization: Bearer <token>`, where the token is a tenant-admin personal access token: log in as an admin of that tenant, mint it once with `qod auth pat create --name scim-okta` (it is printed once), and paste it into the connector. A tenant-scoped token can only touch its own tenant.
- **Resources**: Users and Groups, plus the discovery endpoints (`ServiceProviderConfig`, `ResourceTypes`, `Schemas`) the connector reads on setup.
```bash
SCIM=https://qod.example.com/api/scim/v2/acme
SCIM_TOKEN=qod_pat_... # the tenant-admin PAT minted above
# Provision a user (no password needed - the user signs in through the tenant's OIDC SSO;
# QoD assigns an unguessable random password). active:false creates the row disabled.
curl -sS -H "Authorization: Bearer $SCIM_TOKEN" -H 'Content-Type: application/scim+json' -X POST "$SCIM/Users" -d '{"schemas":["urn:ietf:params:scim:schemas:core:2.0:User"],
"userName":"carol@acme.com","externalId":"okta-123",
"emails":[{"value":"carol@acme.com","primary":true}],"active":true}'
# Find by the IdP's identifier or by userName (filter: attr eq "value")
curl -sS -H "Authorization: Bearer $SCIM_TOKEN" "$SCIM/Users?filter=externalId%20eq%20%22okta-123%22"
# Deactivate (cuts BOTH the REST login and the FlightSQL handshake, not just the console)
curl -sS -H "Authorization: Bearer $SCIM_TOKEN" -H 'Content-Type: application/scim+json' -X PATCH "$SCIM/Users/<id>" -d '{"Operations":[{"op":"replace","path":"active","value":false}]}'
# Group with members; PATCH add/remove members drives RBAC group membership
curl -sS -H "Authorization: Bearer $SCIM_TOKEN" -H 'Content-Type: application/scim+json' -X POST "$SCIM/Groups" -d '{"displayName":"analysts","members":[{"value":"<user-id>"}]}'
```
Semantics worth knowing: `userName` and a group's `displayName` are immutable (a rename is refused with scimType `mutability`); `active` maps to the account `enabled` flag; the superuser realm is invisible to SCIM; creates are insert-only, so a connector retry can never rotate an existing user's password. SCIM is machine-to-machine, so it has no `qod` CLI command - manage users and groups by hand with `qod user`/`qod group`/`qod membership` when you are not driving them from an IdP.
## Tenant + pool management
```bash
# List tenants
qod tenant list
# Create a tenant, then its database (metastore keys omitted here resolve from
# the manager's defaultMetastore at spawn time; --metastore KEY=VALUE overrides)
qod tenant create acme
qod database create --tenant acme --name tpch \
--metastore dbName=tpch --metastore schemaName=tpch1
# A metastore holds only pgHost, pgPort, pgUser, pgPassword, dbName, schemaName,
# dataPath, catalogAlias and encryptionKey. Only the non-secret ones become a node's
# environment variables; pgPassword and encryptionKey reach the node only as files.
# Any other key is refused with 400 invalid_metastore_key (database create, database
# update, manifest import) before anything is written; database update still takes one
# named like an object-store secret sent empty, which removes it (see "Update a
# database"). A database stored with another key by an older release keeps running
# without it: the key is dropped when a node starts and left out of a manifest export.
# A password or encryption key stored under another spelling (PgPassword, EncryptionKey)
# is not dropped: its nodes still use it, but a manifest export leaves it out too (to
# export a password, first store it as pgPassword: see "Update a database").
# Create a pool (1 WriteOnly + 1 ReadOnly + 1 Dual = 3 nodes)
qod pool create --tenant acme --db acme_tpch --pool bi --size 3 \
--writeonly 1 --readonly 1 --dual 1
# Scale up
qod pool scale --tenant acme --db acme_tpch --pool bi --target-size 6 \
--writeonly 1 --readonly 2 --dual 3
# Stop a pool: scales it down to 0 nodes but KEEPS the pool (--force skips graceful drain)
qod pool stop --tenant acme --db acme_tpch --pool bi --force
# Suspend a pool (scale-to-zero, keeps the role distribution for resume)
qod pool suspend --tenant acme --db acme_tpch --pool bi
# Resume a suspended pool
qod pool resume --tenant acme --db acme_tpch --pool bi
# A suspended pool also wakes automatically on the first FlightSQL statement
# (bounded by PROXY_RESUME_HOLD_TIMEOUT_SEC, default 60s). A stopped pool
# (pool stop) stays down; a disabled pool is never auto-woken.
# Autoscale band: declare min/max nodes and the manager adds/removes READONLY
# nodes with demand, never leaving the band. Both bounds together or neither.
# Rules: 1 <= min <= max <= QOD_AUTOSCALE_HARD_CAP (16); min must cover the
# write-capable nodes (writeonly + dual); the CURRENT size must sit inside the
# band (so a stopped pool, size 0, cannot take one - scale it up first); a pool
# with authored cohorts cannot be elastic. Violations return 400 invalid_band.
# Create a pool with a band:
qod pool create --tenant acme --db acme_tpch --pool bi --size 2 \
--writeonly 1 --readonly 1 --min-nodes 2 --max-nodes 6
# Set (or change) the band on an existing pool
qod pool set-autoscale --tenant acme --db acme_tpch --pool bi --min-nodes 2 --max-nodes 6
# Clear the band (omit BOTH bounds) - the pool goes back to a fixed size
qod pool set-autoscale --tenant acme --db acme_tpch --pool bi
# pool scale on a banded pool refuses a target size outside [min, max] with
# 400 outside_band ("adjust the band first via pool/setAutoscale") - the next
# sweep would just undo it. Widen or clear the band, then scale.
# To pin a pool: set --min-nodes == --max-nodes. That is a legal band meaning
# "hold exactly this size, never scale", and it keeps manual scaling constrained
# to that size. To stop the sweep manager-wide: QOD_AUTOSCALE_ENABLED=false
# (bands stay recorded and are simply not acted on). Actions land in the audit
# log with actor "autoscale" and in the manager log as
# "autoscale: acme/acme_tpch/bi out 2 -> 3 util=0.91".
# Delete a pool: stops nodes AND removes the pool from the registry
qod pool delete --tenant acme --db acme_tpch --pool bi --force
# Delete a tenant (must have no pools first)
qod tenant delete acme
# Size a pool's k8s node pods: cpu and memory are each applied as request AND
# limit on the quack container (Guaranteed QoS when both set). Applies on the
# next node spawn; restart the pool's nodes to apply now. Empty clears.
# Set DuckDB memory (database/pool init SQL, SET memory_limit) to ~80% of pod
# memory so the engine spills before the kernel OOM-kills the pod.
qod pool set-resources --tenant acme --db acme_tpch --pool bi --cpu 2 --memory 8Gi
# Supply a full Pod-manifest template from a YAML file (superuser only; requires
# QOD_POD_TEMPLATE_ENABLED=true). The manager overlays the pod name, its
# identity labels, and the quack container's env contract and resources; a
# container named 'quack' is required. Use for sidecars, volumes, affinity.
qod pool set-pod-template --tenant acme --db acme_tpch --pool bi --file pod-template.yaml
```
The local backend ignores cpu/memory/template; use database or pool `initSql` (`SET memory_limit='...'`) for local memory control. The Helm chart's `resources` block sizes the MANAGER container, not node pods; use `qod pool set-resources` for node-pod sizing.
`pool/setResources` is mutation-gated as of 2026-08-13, like `pool/create` and `pool/scale`: a module gate may refuse it, and the refusal surfaces as **HTTP 429 `quota_exceeded`** with the reason in the body. Superuser callers (a superuser session or PAT) bypass the gate, as everywhere. Zero-module (plain OSS) boots have no gates registered, so nothing changes there.
Hosted deployments can cap a tenant's *cumulated* cores and memory across all its pools (dimensions `maxCores` / `maxMemoryGib`, `0` = unlimited). What an operator hitting a 429 needs to know:
- The charge is computed from **declared** pool shapes, not live nodes: `sum over pools of (desired nodes) * cpu` and likewise for memory. **Suspended and disabled pools still count** (they keep their reservation); a stopped pool whose distribution is zeroed charges nothing.
- Under a finite cap, a pool with an **undeclared** (empty) cpu or memory is refused rather than counted as zero. The message is `declare cpu/memory on this pool: resource caps are active for this tenant`; the fix is to `pool/setResources` a real shape on it (which is itself cap-checked). Pre-cap pools are not retroactively broken: they charge nothing until a gated mutation touches them.
- An unparseable quantity is refused naming the offending string. Kubernetes quantity syntax: cpu `"2"` / `"1.5"` / `"500m"` (millicores are the only cpu suffix), memory `"8Gi"` / `"1024Mi"` / `Ki` / `Ti`, the decimal `k`/`M`/`G`/`T` (lowercase `k`, matching what the manager's own quantity validator admits), or a plain byte count.
- **Shrinks always pass.** A scale-down, and any shape swap whose per-dimension delta is `<= 0`, is admitted without consulting the cap, so a tenant that is already over (caps lowered under it, shapes backfilled past them) can always shrink back into compliance.
- Autoscale scale-outs route through the same gate, so caps bound autoscale with no extra config: a band whose ceiling exceeds the cap simply stops growing at the cap, and the refusal feeds the sweep's normal failure backoff.
### Files and S3 for tenant users
Tenant users never read or write a file by path unless it is theirs to reach: an S3 location
with S3 keys of their own, or a local folder their tenant authorized on the node hosts.
Superusers are not limited by these rules, but the nodes' own keys now sign only each database's
data path: a superuser reading another bucket registers keys too (`qod profile s3 add`) or creates a
secret.
Each user registers their own S3 keys, for a bucket or a prefix of one:
```bash
# The secret is read from stdin (or --secret-prompt), never from the command line. The keys reach
# the user's next statement: no node restart.
qod profile s3 add s3://acme-etl/ --access-key-id AKIA... --secret-stdin --region eu-west-3 < key.secret
# Temporary keys: --session-token-stdin too (stdin: the secret's line, then the token's), or
# --session-token-prompt
# An S3-compatible store: --endpoint host[:port] (http:// for no TLS) --url-style path|vhost,
# only an endpoint the operator listed in QOD_USER_S3_ALLOWED_ENDPOINTS
qod profile s3 add s3://lake/in/ --access-key-id minio --secret-prompt \
--endpoint http://minio.internal:9000 --url-style path
qod profile s3 list
qod profile s3 remove <id from list>
# A tenant admin sees (never the secret) and removes their users' keys, never adds them
qod user s3 list <user id>
qod user s3 remove <user id> <id>
```
Adding a scope you already hold replaces its keys. Refusals: `400 invalid_scope` (not
`s3://<bucket>/` or `s3://<bucket>/<prefix>/`, an uppercase bucket, an access point alias, an empty,
`.` or `..` segment, `%`, `?`, `#`, `\` or a glob character), `400 credential_required` (no key id or
secret), `400 invalid_credential`, `400 invalid_endpoint`, `400 endpoint_not_allowed` (the operator
did not list it), `400 scope_in_data_folder` (at or under a database data folder), `400
too_many_credentials` (32 per user). Over MCP: `list_my_s3_credentials`, `remove_my_s3_credential
{id}` (no add: a secret never goes through an agent). Credentials are not in manifests: users enter
them again after an import. The profile page of the UI shows the same list. The keys travel from the
manager to the nodes signed but not encrypted, like every node credential: keep manager-to-node
traffic on a private network. They are stored in plaintext in the control-plane database, so its
backups and replicas carry every user's keys: protect them like the keys themselves.
A tenant admin or a superuser authorizes local folders:
```bash
# Absolute, under <root>/<tenant id>/, where root is the operator's QOD_TENANT_FILES_ROOT
# (unset = no local folder can be authorized)
qod tenant location add acme /srv/qod-files/acme/incoming/
qod tenant location list acme
qod tenant location remove acme <id from list>
```
Refusals: `400 invalid_location` (an `s3://` location: S3 is per user; a relative path, an empty,
`.` or `..` segment, `%`, a glob character or a backslash), `400 outside_tenant_files_root`, `400
tenant_files_root_unset`, `409 location_exists`. Over MCP: `add_tenant_location {tenant, location}`,
`list_tenant_locations {tenant}`, `remove_tenant_location {tenant, id}`. Folders are POSIX paths:
nodes on Windows admit no local folder. While any database's data path cannot be placed on the
nodes (a relative data path outside the local runtime, or a DuckLake database with no data path at
all), every local folder is refused to every tenant: give that database an absolute data path.
Then a user of `acme` holding keys for `s3://acme-etl/` may:
- read with a file reader: `SELECT * FROM read_parquet('s3://acme-etl/in/*.parquet')`,
`read_csv('/srv/qod-files/acme/incoming/x.csv')`;
- `COPY t FROM 's3://acme-etl/in/x.csv'` (an insert into `t`: needs a write grant on `t`) and
`COPY t TO 's3://acme-etl/out/t.parquet'` / `COPY (SELECT ...) TO ...` (reads what the query
reads: needs those grants);
- compute a location from constants, such as a dated file:
`read_csv('s3://acme-etl/in/' || strftime(today(), '%Y%m%d') || '.csv')` or
`COPY t TO ('s3://acme-etl/out/' || today() || '.parquet')`: the node evaluates the expression as
the user and the result is judged as if it had been typed (at most 4 KiB; a NULL or non-text value
leaves the statement refused).
S3 then lets them do what their keys allow, no more. Always refused to tenant users:
- a database's data folder, any tenant's (`QoD: <path> is a database data folder`), except through
a symbolic link below a tenant folder (see below): keys for a
DuckLake bucket never open the folder holding its Parquet files; `s3://b/lake-1/` does not cover
`s3://b/lake-10/`;
- an `s3://` URL none of their keys covers (`QoD: <url> is outside the tenant's authorized locations
(no S3 credential of yours covers it)`), a local path outside the tenant's folders, any scheme but
`s3://` (`http(s)`, `gs`, `az`, `r2`), relative paths, `.` / `..` / empty segments, a
percent-escape, a location computed from columns, a subquery or a parameter;
- a file in a table position: `FROM 'x.parquet'` and `FROM "s3://b/x"` are "table not found" for a
tenant user; use the reader function;
- `CREATE` / `DROP SECRET`, `SET` of the `s3_*`, proxy, certificate and cache settings, `ATTACH`,
`IMPORT` / `EXPORT DATABASE`, `LOAD` / `INSTALL` from a path.
Keep tenant folders free of symbolic links: the node resolves a path's deepest existing folder,
but a link below it can still lead out of the authorized folder through a glob (`*/x.csv`) or a
`COPY ... (PARTITION_BY ...)`, `PER_THREAD_OUTPUT` or `OVERWRITE`. Users cannot create links with
SQL; uploads that preserve links (`rsync -a`, some SFTP clients) can, so copy files in without them
(`rsync -rt`, or `-L` to copy what a link points to) and check with `find <folder> -type l`. The
stakes: such a link can point into any tenant's data folder, and a COPY with `OVERWRITE` deletes
files under its target.
The raw files of an attached Iceberg catalog's warehouse are not data folders QoD knows: keys for a
warehouse bucket let their holder read its files directly. A catalog that neither vends credentials
nor has AWS credentials of its own reads its tables with the reading user's keys. Iceberg tables on
S3 are read-only through QoD for every principal, superusers included: DuckDB's Iceberg extension
commits without the connection secrets the nodes hold users' keys in, so a write fails with an HTTP
403 at commit, until upstream duckdb-iceberg signs its commits with connection secrets. An Iceberg source's AWS credentials
name no S3 endpoint, so a warehouse on an S3-compatible store (not AWS) is read only with the users'
own keys (an endpoint the operator allowed) or credentials the catalog vends.
## RBAC grants
Grants live in the normalized `qodstate_*` tables in Postgres. The endpoints are always mounted (Postgres is the only control-plane store since 2026-06-12).
### Command reference
```bash
# Roles
qod role list --tenant acme
qod role create --tenant acme --name analyst --description "..."
qod role delete <roleId>
# Role table permissions (verb: RO | RW | DDL | ALL)
qod role permission list --role-id <roleId>
qod role permission grant --role-id <roleId> --catalog acme_tpch --schema tpch1 --table customer --verb RO
qod role permission revoke <permissionId>
# Users (--kind admin | user is the account kind; --role / --group are RBAC memberships)
qod user create --tenant acme --username alice --kind user # prompts for the password
# Pick memberships at create time (repeatable; names, not ids). Omitted = the permissive defaults.
qod user create --tenant acme --username bob --role analyst --group analysts
qod user create --tenant acme --username eve --role qod_no_tables --group qod_no_pools
# Groups
qod group create --tenant acme --name analysts
# Memberships (each has a matching remove)
qod membership group-role add --group-id <groupId> --role-id <roleId>
qod membership user-group add --user-id <userId> --group-id <groupId>
qod membership user-role add --user-id <userId> --role-id <roleId>
# Pool access - governs which pools a principal can reach
qod pool permission list --tenant acme
qod pool permission grant --tenant acme --pool-id <poolId> --group-id <groupId>
qod pool permission revoke <id>
```
### Built-in roles and groups (every tenant)
Every tenant carries four built-ins, created with the tenant (and backfilled into existing
tenants on manager boot):
| Name | Kind | Grants |
|---|---|---|
| `qod_all_tables` | role | `* * * ALL` (every table, every verb) |
| `qod_no_tables` | role | nothing |
| `qod_all_pools` | group | access to every pool of the tenant |
| `qod_no_pools` | group | nothing |
- **New tenant users default to `qod_all_tables` + `qod_all_pools`**: ALL on every table of
every pool. To create a restricted user, name its memberships at create time
(`--role` / `--group` in the CLI, `roles` / `groups` in REST and MCP, `ROLES ... GROUPS ...`
in SQL `CREATE USER`). Use `--role qod_no_tables --group qod_no_pools` for a user that can
reach nothing until you grant it something.
- An omitted list takes the default; an explicitly empty list is refused (`400 roles_required`
/ `groups_required`); an unknown name is `400 unknown_role` / `unknown_group`.
- `--role` / `--group` do not apply to superusers (no tenant): sending either is a 400. SCIM
provisioning and manifest import attach nothing by default.
- The built-ins cannot be deleted or edited (permissions, column/row policies, group-role
bindings, pool grants): `409 builtin_protected`. User memberships in them are freely added and removed.
- The `qod_` name prefix is reserved for built-ins (case-insensitive): creating a role or group
named `qod_...` is `400 reserved_name`. Manifest export omits the built-ins and refuses
(`400 reserved_name`) when a user-made `qod_...` role or group exists; rename it first. On
upgrade, a pre-existing user-made role or group holding a built-in name is renamed
`<name>_renamed` (or `<name>_renamed_N`), and a pristine legacy `admin` role (one `* * * ALL`
permission, no policies) is folded into `qod_all_tables`.
- Upgrade note: the user field formerly called `role` (admin | user) is now `kind` everywhere:
REST, CLI `--kind` (`--role` now means an RBAC role), MCP, the manifest `kind` key, custom
`QOD_AUTH_DB_*_QUERY` overrides (which must project `kind`), and `QOD_ADMIN_KIND` (formerly
`QOD_ADMIN_ROLE`, now ignored with a warning at boot) for the seeded admin. New tenants no
longer get an `admin` role.
### Grant a team read access (6-step flow)
The flow below narrows access only for users that do not also hold the defaults: create the
user with `--role qod_no_tables --group qod_no_pools` (or with the team's own role and group),
or remove its `qod_all_tables` / `qod_all_pools` memberships.
```bash
# 1. Create a role (qod --json prints the raw response so the id can be captured)
ROLE_ID=$(qod --json role create --tenant acme --name analyst --description "Read-only analyst" \
| python3 -c 'import sys,json; print(json.load(sys.stdin)["id"])')
# 2. Grant RO on acme_tpch.tpch1.customer (repeat per table, or use "*" to wildcard any field)
qod role permission grant --role-id "$ROLE_ID" \
--catalog acme_tpch --schema tpch1 --table customer --verb RO
# 3. Create a group
GROUP_ID=$(qod --json group create --tenant acme --name analysts \
| python3 -c 'import sys,json; print(json.load(sys.stdin)["id"])')
# 4. Attach the role to the group
qod membership group-role add --group-id "$GROUP_ID" --role-id "$ROLE_ID"
# 5. Add a user to the group (or use membership user-role add to attach the role directly to a user)
qod membership user-group add --user-id <userId> --group-id "$GROUP_ID"
# 6. Grant the group access to the pool (REQUIRED - without this the group cannot reach the pool)
qod pool permission grant --tenant acme --pool-id <poolId> --group-id "$GROUP_ID"
```
Retrieve `<userId>` and `<poolId>` from `qod user list --tenant acme` and `qod pool list` respectively.
### DML and DDL grants
Use the same `qod role permission grant` command with `--verb RW` for DML writes (covers reads too), `--verb DDL` for CREATE / DROP / ALTER, or `--verb ALL` to cover everything on a table at once. The verb vocabulary is deliberately coarse (`RO` / `RW` / `DDL` / `ALL`, matching what the node enforces per table); granular SQL keywords like `SELECT` or `INSERT` are only accepted by the SQL admin dialect (below), which maps them onto these four at parse time.
### Metadata browsing (information_schema)
Since the filtered-metadata feature, **no grant is needed to browse metadata**. Any
authenticated principal may read the session database's `information_schema`
(`schemata` / `tables` / `columns` / `views`); the manager rewrites the query so the
rows come back filtered to the objects the principal already holds at least RO on. A
user granted only `acme_tpch.tpch1.customer` sees that one table plus the system rows,
never the rest of the schema. This is what makes a JDBC/ADBC client's table tree
(DBeaver, ADBC `GetTables`) work for a grantless-on-`information_schema` principal:
those catalog RPCs are `information_schema` queries underneath and used to come back
denied.
The same filter covers DuckDB's own catalog functions `duckdb_tables()`,
`duckdb_views()`, `duckdb_schemas()` and `duckdb_columns()`: called unqualified and
without arguments in a read-only statement, each is narrowed to the session database and
the principal's granted objects (a grantless principal gets zero rows, not a denial).
This is what makes a native `ATTACH 'quack:...' (TYPE quack, TOKEN '...')` work for an
ordinary user: the DuckDB client syncs the remote catalog with
`duckdb_tables() UNION ALL duckdb_views()` on attach, and before 0.9.5 that sync was
denied for anyone without a `*.*.*` ALL grant (`unsupported constructs (deny,
fail-closed): table function duckdb_tables`). The attached catalog then lists only the
tables the user is granted; an ungranted table is simply absent (`does not exist` on
the client), never described. Other table functions stay denied without a wildcard ALL
grant, and the file-reading ones (`read_parquet`, `read_csv`, `glob`, ...) reach only the
locations the user may (see "Direct file access" under Hardening).
An **explicit** `information_schema` grant is still meaningful: it is the escape hatch
that turns the filter off for that principal and restores the unfiltered read (useful
for a tooling or admin account that must see the whole catalog):
```bash
qod role permission grant --role-id "$ROLE_ID" \
--catalog acme_tpch --schema information_schema --table '*' --verb RO
```
The grant must name `information_schema` literally (a wildcard schema does not count as
the escape hatch) and its catalog must be the session database or `*`.
Superusers and holders of a wildcard `ALL` grant are never filtered either. There is no
manager-wide switch: the filter is always on (`QOD_ACL_FILTERED_METADATA` was removed, and
set to any value it makes the manager refuse to start).
**Denial semantics** (all fail-closed):
- The filter rewrites `information_schema` references and `duckdb_*()` catalog calls it
finds in `FROM` position. A reference sitting in `ORDER BY`, `GROUP BY` or a window
clause, or a filterable name merely **mentioned inside a string literal**, is *denied*
with a message ending `query it directly in the FROM clause instead`. The remedy is to
move the reference into the `FROM` clause (or drop the literal mention) and re-run.
- A bare reference to one of DuckDB's system views (`FROM sqlite_master`, `FROM pg_class`,
`FROM duckdb_databases`, `FROM duckdb_tables` without parentheses, and the other default
views of `system.main` / `system.pg_catalog`, also spelled `main.X` or `system.main.X`)
is denied for every non-wildcard principal: on the node such a name
resolves to the system view unless a real table shadows it, and the manager cannot tell
which. For the catalog functions, call them as `duckdb_tables()` and they are filtered;
qualified (`main.duckdb_tables()`) and argument-carrying calls are denied the same way.
A real table that happens to carry such a name is reachable by its three-part name.
- `DESCRIBE <t>`, `SHOW <t>` and `SHOW COLUMNS FROM <t>` require RO on the target
table (for a tenant user they are answered from the filtered listings plus a read of
the table).
- Plain `SHOW TABLES` and `SHOW ALL TABLES` answer the filtered listing. The `FROM` /
`IN` / `LIKE` variants are denied while the filter narrows the principal: query
`information_schema.tables` instead.
- Known limitation: when a statement is metadata-rewritten, a DuckLake time-travel
clause (`AT (VERSION => n)` / `AT (TIMESTAMP => ...)`) on a co-referenced table in the
same statement is dropped, so that table reads at the current version. This only
affects the unusual shape of joining `information_schema` with a time-traveled table
in one query; a standalone `SELECT ... FROM t AT (VERSION => n)` is unaffected. Not a
data-exposure issue (the metadata is still filtered); run the time-travel read as its
own statement if you need the historical snapshot.
### Revoking access
```bash
# Remove a table permission from a role
qod role permission revoke <permissionId>
# Detach a role from a group
qod membership group-role remove --group-id <groupId> --role-id <roleId>
# Remove pool access
qod pool permission revoke <poolPermissionId>
```
The EffectiveSet cache is invalidated on every RBAC mutation, so changes take effect on the next statement, with no TTL window to wait for: an open FlightSQL or native Quack session resolves its grants again on its first statement after the change, so a revoke reaches it there and a re-grant restores it without a reconnect.
There is no switch that turns the grant check off: the nodes check every tenant statement against the grants (or the tenant's OPA), always. `QOD_ACL_ENABLED` was removed: set to `false` it makes the manager refuse to start, set to anything else it is ignored with a warning (`qod setup --unset QOD_ACL_ENABLED` removes a persisted one).
### Force a password change at next login
Create with a temporary password (or reset one) that only works against
`POST /api/auth/change-password`:
```bash
qod user create --tenant acme --username alice --password Temp123 --kind user \
--must-change-password
# reset an existing password as temporary
qod user update <userId> --password Temp123 --must-change-password
```
Until changed, REST login answers `401 password_change_required` and the FlightSQL
handshake fails `UNAUTHENTICATED` with "password change required". The user swaps it
(no session needed; also available anytime for voluntary rotation; prompts for the
current and new passwords):
```bash
qod auth change-password --username alice --tenant acme
```
### Delegate authorization to OPA (per tenant)
A tenant can hand its data-access decisions to its own Open Policy Agent: pool access at
handshake (rule `connect`) and table and file access per statement (rule `statement`,
asked by the node's decision on the tables, columns and files DuckDB resolved), at
`POST <opaUrl>/v1/data/<policyPath>/{connect,statement}`. QoD grants are then ignored for
that tenant (memberships are still sent as roles/groups); superusers never reach OPA.
```bash
# Switch a tenant to OPA (token is write-only; '' clears a field; omitted keeps it)
qod tenant set-acl acme --mode opa --opa-url https://opa.acme.internal:8181 \
--opa-policy-path qod/authz --opa-token "$OPA_TOKEN"
# Dry run: prints the exact input and OPA's answer, executes nothing
qod tenant opa-test acme --pool bi --user alice # connect
qod tenant opa-test acme --pool bi --user alice --sql "SELECT * FROM orders" # statement (pool must be running)
# Back to QoD grants
qod tenant set-acl acme --mode qod
```
Manager envs: `QOD_ACL_MODE` (default mode, `qod`), `QOD_OPA_URL` (fallback URL),
`QOD_OPA_TIMEOUT_MS` (2000), `QOD_OPA_CACHE_TTL_SEC` (5, 0 disables). Behaviour to know:
- Fail closed: only `{"result": {"allow": true}}` allows; an undefined rule, a non-boolean
`allow`, or `row_filter` / `masks` in the result deny with "policy error".
- OPA unreachable, slow, non-2xx or a body over 1 MiB = `UNAVAILABLE` "authorization service
unavailable: OPA unreachable or returned an error (see the manager log)" (retryable), never a
permission error. Check the OPA server, not the grants: the cause (host, port, status) is only
in the manager log, as a WARN `OPA <connect|statement> error for tenant=<id>: ...`;
`qod tenant opa-test` run as a superuser shows it too.
- `400 opa_url_required`: set `--opa-url` or `QOD_OPA_URL` before `--mode opa`.
- `opa-test --sql` runs the statement through a node of the pool, as the named tenant user, and
reports the exact input that node's decision sends OPA (`client.edge` `node`, each access with
`kind`, `columns` and `via`, the `locations`, never the SQL text); the plan is refused, never run.
It needs a running pool and never wakes one: `409 pool_not_running` means start or resume the
pool first. One statement only (`400 single_statement_required`), OPA tenants only
(`400 opa_mode_required`), and the user must be a tenant user, not a superuser
(`400 refused_before_opa`, which also carries a refusal the statement met before OPA). What
happens while binding (the parse screen, fetching the user's S3 credentials) still runs.
- Existing Rego must handle the node input: a policy that ignores `via` judges a read through a
secure view as a read of the underlying table.
- Policy changes apply within the cache TTL and never kill running sessions.
- Catalog listings (`information_schema`, `duckdb_*`) are still filtered by QoD grants, so an
OPA tenant with no QoD grants sees empty listings in BI tools; queries OPA allows still run.
- Manifest export redacts the token; importing where no token is stored is an error: put the
value in the manifest or set it afterwards with `qod tenant set-acl`.
## SQL administration (FlightSQL)
An admin SQL dialect is answered directly at the FlightSQL edge: GRANT/REVOKE,
CREATE/DROP ROLE, CREATE/ALTER/DROP USER, ROW/COLUMN POLICY, ALTER GROUP, and
admin SHOW forms are claimed by `FlightSqlRouter` before a statement would
otherwise be forwarded to a node, executed against the same `PoolSupervisor`
mutators the REST RBAC endpoints call, and answered as a small in-memory
Arrow result set. This is a second way to reach the RBAC/policy machinery
described above - REST and the SQL dialect are two doors onto the same rows,
and either one invalidates the same `EffectiveSet` cache.
Flag: `quack-on-demand.sqlAdmin.enabled` (env `QOD_SQL_ADMIN_ENABLED`, default
`true`). Off restores the pre-feature behavior exactly: these statements fall
through to the normal fail-closed denial instead of being claimed.
**Who may run them**: a superuser session, or a tenant admin session acting
within its own tenant. A non-admin session gets `PERMISSION_DENIED
admin_required`. Admin authority is evaluated against the handshake-time
principal snapshot cached on the connection - demoting an admin does not cut
off dialect authority on an already-open connection until that connection's
context TTL (`sessionTtlSec`, default 3600s) expires, matching the existing
handshake-cache behavior for every other authorization check on the wire.
**One example per statement family** (the semantics bullets below cover the
sharp edges of the grammar):
```sql
-- Roles and membership
CREATE ROLE analyst;
GRANT ROLE analyst TO USER alice;
ALTER GROUP finance ADD USER alice;
-- Users
CREATE USER alice PASSWORD 'secret';
CREATE USER ops PASSWORD 'secret' ADMIN;
CREATE USER bob PASSWORD 'secret' ROLES analyst, qod_no_tables GROUPS qod_no_pools;
ALTER USER alice PASSWORD 'newsecret';
ALTER USER alice REQUIRE PASSWORD CHANGE;
ALTER USER alice DISABLE;
ALTER USER alice ENABLE;
DROP USER IF EXISTS alice;
-- Table ACLs
GRANT SELECT ON tpch.main.orders TO ROLE analyst;
REVOKE ALL ON tpch.main.orders FROM ROLE analyst;
-- Row policy (RLS)
CREATE ROW POLICY ON tpch.main.orders FOR ROLE analyst
USING (region = ${tenantId} OR owner = ${user});
-- Column policy (CLS mask/deny)
CREATE COLUMN POLICY ON tpch.main.customers COLUMN email FOR ROLE analyst
MASK USING (SHA256(CAST(email AS VARCHAR)));
-- Pool access
GRANT CONNECT ON POOL tpch.bi TO USER alice;
-- Introspection
SHOW GRANTS FOR ROLE analyst;
SHOW GRANTS FOR USER alice;
SHOW USERS;
```
The prepared-statement path advertises the correct `(status, detail)` schema
for a mutating admin statement (dispatch lives inside `execute`, which both
the immediate and prepared paths call), so ADBC/JDBC clients - the dialect's
primary clients - work through Prepare/Execute exactly as they do for any
other DDL. Nothing is executed at Prepare time; only the schema advertised at
that step differs from an ordinary DDL statement's `Count: int64`, matching
what the later Execute actually delivers.
**Semantics worth knowing before you rely on them**:
- `REVOKE <verb> ON obj FROM ROLE r` on a grant that does not exist is not an
error - it succeeds with `detail = "revoked 0"` in the result row (there is
no warning channel over FlightSQL to distinguish "revoked something" from
"revoked nothing").
- `REVOKE ALL ON *.*.*` removes only a grant row stored **literally** as
`(*, *, *)` - it is not a shorthand for "every grant this role holds." Wildcards
in the `ON` clause match the stored tuple exactly, wildcards included; they
are not glob patterns over existing rows.
- `SHOW ... ON <table>` and `SHOW GRANTS FOR ROLE r` match the stored tuple
exactly too, same caveat.
- `SHOW ROLES`, `SHOW GRANTS`, and `SHOW USERS` are claimed by the dialect and
will **shadow** a real table literally named `roles`, `grants`, or `users`
in your schema. Quote the identifier (`SHOW "roles"`) to bypass the dialect
and reach DuckDB's normal describe-table behavior instead - the claim check
only fires on an unquoted keyword token.
- The `ON POOL db.pool` qualifier in `GRANT/REVOKE CONNECT ON POOL ...` is
optional and never disambiguates: pool names are unique per tenant already,
so a bare `ON POOL sales` and a qualified `ON POOL tpch.sales` resolve
identically. A *wrong* qualifier does not fall back to name-only resolution -
it fails `unknown_pool`, it does not silently pick a different pool.
- Admin SQL is FlightSQL-edge only. It is **not** available over the MCP
server or the REST catalog-preview/restore/undrop endpoints: those share one
routed-execution choke point that explicitly disables the dialect claim, so
a claimed statement reaching them still falls through to the ordinary
fail-closed denial rather than the dialect's own admin check (which does not
know about PAT scope attenuation the way the routed ACL path does). Use the
FlightSQL wire (or the REST RBAC endpoints above) for SQL administration.
- Admin statements are control-plane writes, not data-connection writes: a
surrounding `BEGIN` on the data connection does not cover them. They commit
immediately regardless of an open transaction, and are not rolled back by a
later `ROLLBACK` on that connection.
- `CREATE USER` / `DROP USER` always target a **tenant** user in the session
tenant - the dialect cannot mint superusers (tenant-NULL rows are
unreachable by construction), consistent with the standing
no-privilege-escalation rule that only superusers mint superusers, via REST.
`WITH` before `PASSWORD` is optional Postgres-style noise; `ADMIN` sets the
account kind to `admin` (management rights), not RBAC superuser status. `CREATE USER` is a true
create (`failIfExists = true`): an existing `(tenant, username)` is refused
with `ALREADY_EXISTS`, never upserted. Optional `ROLES r1, r2` then
`GROUPS g1, g2` (in that order, each at most once, at least one name each)
attach the user to exactly those roles / groups; an omitted clause defaults
to `qod_all_tables` (roles) / `qod_all_pools` (groups), and an unknown name
is refused with `INVALID_ARGUMENT` and creates nothing. `DROP USER` refuses to drop the
session's own username (self-drop guard, mirroring the REST posture). The
password literal is excluded from statement history (the executor logs only
the command kind) AND redacted from the edge's DEBUG statement logging - a
claim-shaped statement is logged as a constant redacted placeholder there,
never a substring of the raw text, and the FlightSQL wire is TLS.
- `ALTER USER ... PASSWORD` rotates a tenant user's password through the same
per-(tenant, username) path REST `user/update` uses, so lockout counters
(`failed_attempts` / `locked_at`) are cleared as part of the write, same as
the REST reset. Unlike `DROP USER`, self-rotation is allowed - there is no
session-user guard on this one. An unknown username 404s before the
rotation path ever runs. Same password-literal-excluded-and-redacted note as
`CREATE USER` above. `ALTER USER ... ENABLE` / `DISABLE` and `ALTER USER ...
REQUIRE PASSWORD CHANGE` (below) go through the same underlying upsert and
clear the same lockout counters as a side effect, even though neither
touches the password.
- `SHOW USERS` lists the session tenant's users only (`id`, `username`,
`role`, `enabled`, `email`) - never any credential material, since
`RbacUser` carries no password hash. The superuser realm is invisible, as
everywhere else in the dialect. `users` is a much likelier real table name
than `roles` or `grants`, so the shadowing caveat above bites harder here:
if your schema has its own `users` table, `SHOW USERS` claims the statement
and never reaches it - quote the identifier (`SHOW "users"`) to get DuckDB's
normal describe-table behavior instead.
- `ALTER USER ... REQUIRE PASSWORD CHANGE` flags the account so it must set a
new password on its next login, WITHOUT rotating the credential - it takes
no password argument. There is no self-guard: the session user may flag its
own account. An unknown username 404s before anything is written. Surprising
consequence: the write goes through the same upsert that unconditionally
clears `failed_attempts` / `locked_at`, so flagging a **locked-out** user for
a required password change also unlocks them - the same side effect an
admin password reset already has, just reached from a statement that never
mentions lockout at all.
- `ALTER USER ... ENABLE` / `DISABLE` flips the account's login/handshake gate
(the same `enabled` column REST `user/update` locks/unlocks). `DISABLE`
refuses to target the session's own username (self-disable guard, mirroring
`DROP USER`'s self-drop guard); `ENABLE` has no such guard - re-enabling
your own account is allowed. An unknown username 404s before anything is
written. `ENABLE` also clears lockout counters (see above) - re-enabling an
admin-disabled account also lifts any failed-login lock it had accumulated.
- `SHOW GRANTS FOR USER u` lists the flattened effective table-grant closure
for a tenant user - direct role grants plus grants reached through group
membership - as `(role, id, catalog, schema, table, verb)`, where `role`
names the specific role each row was granted through ("-" if it cannot be
resolved). Tenant-scoped by construction (the username is resolved within
the session tenant, same as every other user-targeting form); an unknown
username 404s. A resolved user holding no grants lists as an empty result,
not an error.
### Recover a dropped table (UNDROP TABLE, self-service)
Any signed-in user can recover a dropped DuckLake table, as themselves, through SQL on either door
(FlightSQL, `qod sql`, or a DuckDB client ATTACHed to the native front door):
```sql
UNDROP TABLE main.orders; -- under its last name
UNDROP TABLE main.orders AS orders_back; -- under a new name
```
It finds the table's last live snapshot and runs `CREATE TABLE ... AS SELECT * FROM ... AT (VERSION
=> n)` as the caller, so it needs a read grant on the table's last name, which no live table or view
may hold, and DDL in the schema. The answers are plain words: "there is no dropped table
main.orders", "a table or view main.orders exists; use UNDROP TABLE main.orders AS <new name>", "not
permitted: ...". Without a schema, the database's default schema is used; names compare without
case, as DuckDB resolves them. It is part of the SQL admin dialect, so it needs
`QOD_SQL_ADMIN_ENABLED` (on by default). It is refused inside an open transaction (`BEGIN ...`): its
recovery commits on a connection of its own, which the client's ROLLBACK could not undo; run it
outside one. A personal access token's databases, pools and verb ceiling apply to every undrop
route (a read-only token cannot recover).
The same, from the profile (regular users included): `qod profile recoverable --db lake` lists the
dropped tables of your own database you may recover, `qod profile undrop --db lake --schema main
--table orders [--as-name orders_back]` recovers one; the UI profile page has a "Recover a dropped
table" card; MCP `list_recoverable` / `undrop_table` work with a regular user's PAT. Admins keep
`qod catalog recoverable` / `qod catalog undrop` (any table, `--from-snapshot`). A name dropped more
than once is listed once per drop, each with its `tableId`: pass `--table-id <id>` (MCP `table_id`)
to recover that drop; the name alone recovers the newest.
**Which history a user may read** (time travel, the data diff, the change feed, undrop):
- a table renamed since is judged as the table it is now (its current name's grant);
- a table replaced since (`CREATE OR REPLACE`, a QoD restore) is the same table: its history is
judged on the current name's grant;
- a table dropped since is judged as a read of its last live name: allowed while no live table or
view holds that name and the grant covers it, refused once the name is reused (only a superuser
reads it then);
- any of those three is refused to a user with column or row policies.
- a dropped table read `AT (TIMESTAMP => ...)` can be refused when the node cannot pin the
timestamp to the same snapshot DuckLake reads (sub-microsecond rounding, a session TimeZone it
cannot apply): read it `AT (VERSION => n)` instead, which is never affected.
## Federation - external catalogs via DuckDB extensions
Quack-on-Demand supports per-tenant-db federated catalogs that attach external sources (Postgres, S3, Iceberg, any DuckDB extension) under DuckDB catalog aliases. Existing RBAC covers federated tables - a `RolePermission(catalog='fedpg', schema='public', table='orders', verb='RO')` grants read access to a federated alias just like a DuckLake table. **Known limitation, column policies:** with column-level security on (the default), a principal holding ANY column policy, even one on an unrelated table, is denied (fail-closed, "column policy rewrite could not parse statement" or "unresolved table") every read of a table outside the session's default catalog and schema: a federated alias, another attached catalog such as an Iceberg one, or another schema of the same DuckLake database. A table grant alone does not make such a table readable for that principal, and a mask on a federated table never masks: the read is denied instead. Principals with no column policy are unaffected. Sources come in two flavours: `sql`, where the operator writes the `ATTACH` text, and typed sources where QoD writes it: `iceberg-rest` (see "Register an Iceberg REST catalog") and `postgres` / `mysql` (see "Register a Postgres or MySQL database").
### Tenant-db kinds
When creating a tenant-db, pick a `kind`:
- `ducklake` (default) - Postgres metastore + object-store dataPath. Production multi-node persistence.
- `duckdb-file` - Local `.duckdb` file at `dataPath`. Single-node only (file must exist on every node).
- `memory` - No persistent default catalog. Use with `defaultDatabase` pointing at a federated alias.
For `ducklake` creates, Postgres connection keys are optional: anything omitted resolves from the manager's `defaultMetastore` (`QOD_PG_*`) at spawn time, and the row follows later config changes.
Example: create an in-memory tenant-db that only serves federated sources.
```bash
qod database create --tenant acme --name fed --kind memory \
--default-database fedpg --default-schema public
```
### Encryption at rest (create-time only)
`--encrypted` makes a database encrypt what it writes to disk or to a bucket.
```bash
# ducklake: every Parquet file DuckLake writes is encrypted. DuckLake mints one key
# per file into its own catalog; QoD holds no key material.
qod database create --tenant acme --name warehouse --encrypted
# duckdb-file: the .duckdb file, its WAL and its temp files (AES-256-GCM).
# QoD mints the key unless you pass --encryption-key.
qod database create --tenant acme --name ledger --kind duckdb-file \
--data-path /srv/qod/ledger.duckdb --encrypted
# Bring your own key instead (duckdb-file only; ducklake rejects it).
qod database create --tenant acme --name ledger --kind duckdb-file \
--data-path /srv/qod/ledger.duckdb --encrypted --encryption-key "$MY_KEY"
```
Rules to know before using it:
- **Create-time only, in both directions, for both kinds.** Neither engine can encrypt an
existing database in place, or decrypt one. There is no `encrypted` field on
`qod database update`, and a manifest import that flips the flag on an existing database
is refused. To change it, create a new database and copy the data.
- **The `duckdb-file` key is never readable back.** It is redacted from every API response,
from `qod database list`, and from an exported manifest. Recovery means reading the
control-plane Postgres directly. Lose the key and that row, and the database is unreadable.
This also means
**an exported manifest cannot recreate an encrypted `duckdb-file` database**; an encrypted
`ducklake` database round-trips fine, since its keys live in its own catalog.
- `--encrypted` on `--kind memory` is refused: nothing is at rest.
- A **branch inherits its parent's encryption**, and reads the parent's encrypted files with
the parent's keys.
- Encrypting a database makes its nodes load `httpfs`, because DuckDB needs OpenSSL for a
writable encrypted file. On an air-gapped host, pre-cache that extension.
- **Trust boundary.** Encryption at rest moves the trust boundary to the control plane. For
`ducklake` the object store becomes untrusted storage, which is the point, but the per-file
keys sit in the tenant-db's Postgres catalog and `pgPassword` reaches it. For `duckdb-file`
the key sits in the control-plane row. In both cases, whoever can read the control-plane
database can decrypt the data. Treat an exported manifest as a secret for the same reason:
`encryptionKey` is redacted out of it, but `pgPassword` is not, and for an encrypted
`ducklake` database that password opens the catalog holding the per-file keys.
Manager-wide policy: `QOD_REQUIRE_ENCRYPTION=true` (default off) refuses any database create
that does not ask for encryption, whether it arrives through `database/create` or through a
manifest import, so no plaintext database can exist in the deployment. It gates creates only,
so turning it on never breaks databases that already exist, and a manifest describing them
still applies.
One asymmetry worth knowing: DuckLake errors when an unencrypted catalog is attached as
encrypted, but attaching an encrypted catalog *without* the flag silently succeeds (and still
writes encrypted files, since the catalog's own metadata decides). The engine will therefore
never tell you that a database row saying `encrypted=false` points at a catalog that is in
fact encrypted. QoD's pre-attach guard is what catches that, in both directions.
**Upgrading on Kubernetes:** node credentials (`pgPassword`, and `encryptionKey` for encrypted
`duckdb-file` databases) now reach a pod through a per-pool Secret instead of the pod's plain
environment. Pods created by an earlier manager keep the old shape and are not migrated in
place. Restart every node after upgrading, for example by scaling each pool down and back up.
### Update a database
```bash
# Update a database: any subset of metastore, objectStore, defaultDatabase,
# defaultSchema, initSql. Absent fields stay unchanged; empty clears. Editing
# metastore/objectStore/initSql restarts ALL the database's nodes immediately
# (in-flight statements on them fail); default database/schema edits do not.
# pgPassword is preserved unless you send it: pgPassword=new rotates. Removing a key
# the database's kind requires (incl. pgPassword on ducklake) is rejected.
# Engine defaults only in initSql, never credentials: the value is stored
# unredacted and inlined in pod specs; secrets belong in federation sources.
qod database update --tenant acme --name acme_tpch --init-sql "SET memory_limit = '8GB';"
# Rotate the metastore password (restarts the db's nodes):
# Send the FULL metastore map when editing it (minus pgPassword to keep it): the map is replaced, and dropping a required key is rejected.
# A database stored with another key (from an older release) keeps running without it: the key
# never reaches its nodes (the manager logs it at ERROR once per pool), and an update that sends
# it back is refused with 400 invalid_metastore_key, so leave it out of the map. One named like an
# object-store secret (s3_secret_access_key, azure_account_key, gcs_hmac_secret) is hidden from
# responses and kept when left out: remove it by sending it empty with the rest of the map, e.g.
# --metastore s3_secret_access_key=
# A password stored under another spelling (PgPassword) still reaches its nodes, but a manifest
# export leaves it out (logged at ERROR): store it as pgPassword by sending
# pgPassword=<the password> with the rest of the map, which replaces every spelling.
qod database update --tenant acme --name acme_tpch \
--metastore dbName=acme_tpch --metastore pgHost=localhost --metastore pgPort=5432 \
--metastore pgUser=postgres --metastore schemaName=main --metastore pgPassword=newpass
# A pool's own initSql (runs after the catalog ATTACH, on the next node spawn):
qod pool set-init-sql --tenant acme --db acme_tpch --pool bi --init-sql "SET threads = 4;"
```
**Who may write what in initSql.** A superuser's database or pool `initSql` (and one the manager
writes: `qod serve`, a manifest import, a branch copying its parent) runs as written. A tenant
admin's runs on the node under an allowlist, statement by statement, each one alone:
- `SET` / `RESET` (default or `GLOBAL` scope, one literal value) of `calendar`,
`checkpoint_threshold`, `default_collation`, `default_null_order`, `default_order`,
`errors_as_json`, `max_memory`, `max_temp_directory_size`, `memory_limit`,
`ordered_aggregate_threshold`, `preserve_insertion_order`, `threads`, `timezone`,
`wal_autocheckpoint`, `worker_threads`;
- a read-only `PRAGMA` (`table_info`, `show_tables`, `show_tables_expanded`, `show_databases`,
`database_list`, `functions`, `version`) with literal arguments;
- `INSTALL` / `LOAD` by bare name of `avro`, `aws`, `azure`, `delta`, `ducklake`, `encodings`,
`excel`, `fts`, `httpfs`, `iceberg`, `icu`, `inet`, `json`, `mysql_scanner`, `parquet`,
`postgres_scanner`, `spatial`, `sqlite_scanner`, `tpcds`, `tpch`, `vss` (no `FORCE`, `FROM`,
`VERSION`, path or URL).
Anything else (views, secrets, `COPY`, `ATTACH`, other settings) is refused when it is written: `400
init_sql_refused` with each refused statement's position and reason (`database create`, `database
update` when the text changes, `pool create`, `pool set-init-sql`; also over MCP), and skipped on the
node if it gets there anyway (the node log names the statement's position, never its text). A
manager on Kubernetes, or in fleet mode without `QOD_FLEET_EPHEMERAL=local`, has no local node binary
to check with: the write is accepted and the response carries `initSqlWarning`. A tenant admin's
initSql holds at most 64 KiB, and no initSql may contain the text `qod:untrusted-`. Responses show
`initSqlTrusted`. A session-level setting (`timezone`, `calendar`, `errors_as_json`, ...) set this
way at the default scope changes only the node's start connection, never the sessions it serves;
with `SET GLOBAL` it becomes every session's default.
After upgrading, every non-empty initSql a tenant admin wrote before is untrusted: the boot log has a
WARN for each database or pool that loses statements, naming it (never the SQL). To keep such a
text as it is, have a superuser re-save it unchanged (`qod database update --init-sql ...`, `qod
pool set-init-sql ...`), then restart its nodes.
### Per-database object-store credentials
`objectStore` on a database takes effect at node spawn: it authors a DuckDB
secret scoped to that database's `dataPath`, so this database authenticates
its own bucket with its own keys, coexisting with the process-global
`QOD_S3_*` / `QOD_AZURE_*` / `QOD_GCS_*` default secret (DuckDB picks the most
specific scope per path). Keys, by `dataPath` scheme:
- `s3://` / `s3a://` / `r2://`: `s3_region`, `s3_access_key_id`,
`s3_secret_access_key`, `s3_endpoint`, `s3_url_style`.
- `gs://`: `gcs_hmac_key_id`, `gcs_hmac_secret`.
- `az://` / `azure://` / `abfss://`: `azure_account`, `azure_account_key`.
```bash
# Create a database that authenticates its own bucket, distinct from the
# manager-wide default credentials.
qod database create --tenant acme --name coldstore --kind ducklake \
--data-path s3://acme-coldstore/ducklake \
--object-store s3_region=us-east-1 \
--object-store s3_access_key_id=AKIA... \
--object-store s3_secret_access_key=...
```
An empty (or absent) `objectStore` falls back to the global env credentials -
exact back-compat for every deployment that only ever used one set of keys.
On Kubernetes the resolved SQL is never inlined in the pod spec: it lands in a
per-node Secret `qod-store-${nodeId}` injected via `env.valueFrom.secretKeyRef`
(same pattern as the per-pod token and per-pool federation secrets); `kubectl
describe pod` shows the ref, not the values. On the local backend it rides the
node's process env like the other spawn-time SQL blocks. GET/list responses
redact `s3_secret_access_key`, `azure_account_key`, and `gcs_hmac_secret`
(alongside `pgPassword`). The secret is authored only for `kind=ducklake`
databases - a `duckdb-file` database on a remote `dataPath` gets no per-db
object-store secret today (that spawn arm doesn't install `httpfs`/`azure` at
all, a separate pre-existing gap). Editing `objectStore` restarts the
database's nodes so the new secret takes effect immediately; there is no
in-place rotation on an already-running node.
### Managed object storage (QoD provisions the bucket prefix)
Instead of bringing a bucket, a `ducklake` database can ask QoD to carve its
data path out of ONE operator-owned root bucket. Off by default; enable it on
the manager with:
```bash
export QOD_MANAGED_STORE_ENABLED=true
export QOD_MANAGED_STORE_ENDPOINT=http://seaweedfs:8333 # empty = AWS default resolution
export QOD_MANAGED_STORE_REGION=us-east-1
export QOD_MANAGED_STORE_BUCKET=qod-managed
export QOD_MANAGED_STORE_ACCESS_KEY_ID=...
export QOD_MANAGED_STORE_SECRET_ACCESS_KEY=...
export QOD_MANAGED_STORE_URL_STYLE=path # path (S3-compatible) | vhost (AWS)
export QOD_MANAGED_STORE_RETAIN_DAYS=7 # retention after delete, 0 = immediate
export QOD_MANAGED_STORE_PURGE_SWEEP_SEC=300 # purge worker cadence, 60s floor
```
**The root bucket must have versioning OFF.** On a versioned bucket a delete
writes a delete marker instead of removing the object: the purge worker's next
listing comes back empty, it stamps the prefix purged, and the non-current
versions keep billing forever. Boot creates the bucket if missing; an
unreachable store only WARNs (`managed object store unreachable`) and never
blocks a managed create at the control plane - the create still succeeds, and
the failure surfaces later, at node spawn/ATTACH against the missing bucket.
In HA, replicas race that first create, so the losing ones can log one false
"unreachable" WARN at first boot; it self-heals.
```bash
# Create a managed database. No data path, no object store: the server resolves
# both. The response's dataPath is s3://<bucket>/<tenant>_<name>-<id8>/ where
# id8 is the first 8 chars of the tenant-db surrogate id, so recreating a
# deleted name always lands on a fresh empty prefix.
qod database create --tenant acme --name sales --kind ducklake --managed-storage
```
400s, all actionable: managed storage not enabled on this deployment
(`QOD_MANAGED_STORE_ENABLED`); `managedStorage` sent together with a
`dataPath`/`objectStore` (one intent per call); `managedStorage` on a
non-`ducklake` kind. `database/update` has NO `managedStorage` field: there is
no BYO-to-managed (or managed-to-BYO) migration, recreate instead.
- **Credential rotation**: a managed create snapshots the operator credential
(`QOD_MANAGED_STORE_SECRET_ACCESS_KEY`) into that database's own
`objectStore` at create time. Rotating the env var only affects *future*
managed creates; existing managed databases keep signing with the old key
and break at their next node respawn once it is revoked. Remediation today:
`database/update` each managed database's `objectStore` with the new
secret, then recycle its pools. Per-database credential minting is the
designed follow-up.
```bash
# Delete: tombstone now, objects purged after retainDays (7 by default).
qod database delete --tenant acme --name acme_sales
# Delete and make the storage purge-eligible immediately (the worker drains it
# on its next sweep; the call itself still returns straight away).
qod database delete --tenant acme --name acme_sales --purge-managed-data
```
`purgeManagedData` on a BYO / default-path database is ignored with a WARN.
Deleting the whole TENANT cascades tombstones with the normal retention window,
never immediate.
Inventory / audit the prefixes in the control-plane Postgres:
```sql
SELECT id, prefix, deleted_at, purge_eligible_at, purged_at
FROM qodstate_managed_prefix
ORDER BY created_at;
```
- `deleted_at IS NULL` - live database.
- `deleted_at` set, `purged_at NULL` - retained; the objects are still there and
still billed. Until `purge_eligible_at`, this window doubles as the undrop
window (the data is recoverable by hand).
- `purged_at` set - objects gone, row kept as the audit trail.
The purge worker is HA-leader-gated, sweeps every `purgeSweepSec`, drains each
due prefix in bounded list+delete batches (resuming across sweeps for large
prefixes), isolates failures per prefix, and stamps `purged_at` only when a
listing comes back empty. Grep the manager log for `managed purge:`.
Two operator cautions:
- **Changing `QOD_MANAGED_STORE_BUCKET` strands existing tombstones.** Rows
written under the old bucket no longer match the configured root, so the
worker logs `skipping <id>, prefix ... is not under ...` on every sweep and
the old bucket's objects leak. Purge or migrate before switching buckets.
- **All managed databases share the operator credential.** Isolation between
tenants' managed data is prefix-by-convention, enforced by the per-db
`CREATE SECRET` scoped to that database's own `dataPath`, and by the nodes,
which refuse every database data folder to tenant users and sign their S3
requests with their own keys only; not by store-level ACLs. Per-database credential minting is the
designed follow-up.
### Register a federated source
A `sql` source runs the setup SQL you write, verbatim, on every node of the
tenant-db, so only a superuser may create or change one (a tenant admin gets `403
superuser_required` on every path, CLI, REST, MCP and manifest import; typed sources
stay open to tenant admins). Every `{{secret.NAME}}` in it must sit inside a plain
single-quoted literal (`PASSWORD '{{secret.PG_PWD}}'`, never `E'...'`, dollar quoting
or a `$` parameter outside literals): a save otherwise answers `400
invalid_federated_source` naming the placeholder. A template stored before this rule
keeps an unquoted placeholder only for a plain value (letters, digits, `_`, `.`, `-`,
next to no quote or letter, such as `PORT {{secret.PG_PORT}}` = `5432`); for any other
value that source is left out of the node's setup with a WARN (the boot log lists each
such template): quote the placeholder and save it again. A `sql` source stored before
the upgrade keeps running, and boot WARNs once per one whose author is unknown: a
superuser re-saves it unchanged to vouch for it, or deletes it.
For a Postgres or MySQL database, prefer the typed source ("Register a
Postgres or MySQL database" below): QoD writes this same block for you and the
source is read-only by default. A hand-written one looks like this:
```bash
# One statement per line. --setup-sql takes the text itself (there is no file
# option), so read it from a heredoc.
SETUP_SQL=$(cat <<'SQL'
INSTALL postgres;
LOAD postgres;
CREATE OR REPLACE TEMPORARY SECRET {{alias}}_pg (TYPE postgres, HOST 'pg.prod', PORT 5432, DATABASE 'warehouse', USER 'svc_qod', PASSWORD '{{secret.PG_PWD}}');
ATTACH '' AS {{alias}} (TYPE postgres, SECRET {{alias}}_pg, READ_ONLY);
DROP SECRET IF EXISTS {{alias}}_pg;
SQL
)
# 1. Save it disabled: the PG_PWD secret can only be set once the source exists.
qod federation create acme acme_fed --alias fedpg \
--description "Prod warehouse Postgres" --read-only --disabled \
--setup-sql "$SETUP_SQL"
# 2. Set the secret ("Add a Postgres-backed secret" below).
qod federation secret set acme acme_fed fedpg --name PG_PWD --value "$PG_PWD"
# 3. Enable it: the same create, without --disabled.
qod federation create acme acme_fed --alias fedpg \
--description "Prod warehouse Postgres" --read-only \
--setup-sql "$SETUP_SQL"
```
Why it is shaped this way:
- **A temporary secret, dropped right after the ATTACH.** A DuckDB secret is
visible to every session on the node and outlives the ATTACH. Left in place,
any session could re-attach the database with it, writable, under the
service account's credentials.
- **One statement per line.** The node keeps going past a failed statement, so
a `DROP SECRET` on its own line still runs when the ATTACH fails (the remote is
down at node start, say). On the ATTACH's line it would be skipped and the
secret would stay.
- **`--read-only`.** A `sql` source defaults to writable: `READ_ONLY` on the
ATTACH binds that one attachment only, and `--read-only` makes every node
refuse writes, DDL, ATTACH and DETACH on the catalog to every principal,
superusers included (`QoD: catalog <alias> is read-only`), from the next
statement on.
- **Disabled until its secret exists.** An enabled source whose
`{{secret.NAME}}` names a missing secret fails the whole tenant-db federation
blob at node spawn, not just this catalog.
Placeholders:
- `{{alias}}` - replaced with the source's `alias` field.
- `{{secret.NAME}}` - replaced with the resolved value of the secret named `NAME`.
Alias rules, for **every** source type (`sql` as well as the typed ones):
- **An alias must be a plain lowercase identifier**: ASCII letters, digits and
underscore, starting with a letter or underscore, 1..63 chars. `--alias ext-s3`
(hyphen, dot, or over-length) is a `400`; use `ext_s3`. DuckDB treats catalog
and secret names case-insensitively, so a mixed-case alias is stored lowercase
and `Sales_Lake` and `sales_lake` are one row, not two.
- **A source already stored under an alias that rule rejects stays editable.**
Re-`create` it naming that same alias and the edit applies under the stored
spelling. Its NAME is frozen, though: renaming it means delete and recreate,
and since the alias is the catalog segment of every role permission, that also
means re-granting.
- The manager lists every stored alias it would reject or rewrite at boot, at
ERROR level, so an upgrade names the affected rows for you.
- An alias must not collide with the tenant-db's own catalog alias, with a
sibling federated alias, or with a DuckDB builtin (`memory`, `system`, `temp`).
- **`create` upserts, and an omitted field is RESET, not preserved.** Re-POST the
same alias to edit a source in place (there is no `qod federation update`). Every
field the request leaves out goes back to its default, so a re-`create` that
omits `--read-only` on a read-only source makes it writable again; the manager
WARNs when that happens. Pass the flags you want kept.
### Add a Postgres-backed secret
```bash
qod federation secret set acme acme_fed fedpg --name PG_PWD --value hunter2
```
Or a secret backed by an external store (env var, AWS Secrets Manager, etc.):
```bash
qod federation secret set acme acme_fed fedpg --name PG_PWD \
--external-ref "vault:secret/data/qod/fedpg#password"
```
### Register an Iceberg REST catalog
An external Iceberg REST catalog (Polaris, Lakekeeper, Glue, S3 Tables, any
REST-spec catalog) is a **typed** source: declare the connection settings and
QoD renders the `INSTALL` / `CREATE SECRET` / `ATTACH` block itself, validating
them at create time instead of at node spawn. No `--setup-sql` for this type.
```bash
# 1. declare the catalog. Credential flags carry {{secret.NAME}} placeholders, never values.
qod federation create acme acme_fed --alias icelake --type iceberg-rest \
--uri https://polaris.example.com/api/catalog \
--warehouse analytics \
--auth oauth2 \
--client-id qod-svc \
--client-secret '{{secret.ICE_SECRET}}'
# 2. store the real credential under the name the placeholder used
qod federation secret set acme acme_fed icelake --name ICE_SECRET --value "$CLIENT_SECRET"
# 3. recycle the pool's nodes so they re-attach with the new source
qod node restart --tenant acme --db acme_fed --pool bi --node-id bi-1
```
Flags:
- `--type iceberg-rest` selects the typed path. `--warehouse` is always required.
- Exactly ONE of `--auth` (`none` | `oauth2` | `token` | `sigv4`) or
`--endpoint-type` (`glue` | `s3_tables`) - DuckDB refuses both at once. `--uri`
is required whenever `--auth` is set.
- `oauth2` needs `--client-id` + `--client-secret`; `token` needs `--token` and
takes nothing else; `none` takes no credential flags at all. Optional oauth2
knobs: `--oauth2-server-uri`, `--oauth2-scope`, `--oauth2-grant-type`.
- `--warehouse` changes meaning with the endpoint type: for `glue` it is the AWS
account id (`123456789012`, optionally `:catalog`), for `s3_tables` the table
bucket ARN (`arn:aws:s3tables:<region>:<account>:bucket/<name>`).
- `sigv4` and both endpoint types sign with AWS credentials, set with the
`--aws-*` flags (see "AWS-signed catalogs" below). Without them no secret is
rendered and DuckDB signs with whatever default s3 secret the node already
holds, usually QoD's own storage credentials.
- `--config '<json>'` passes the whole config object instead of the flags, and
wins over them when both are given.
- `--read-only` / `--no-read-only`. **Defaults ON for `iceberg-rest`** (an
external catalog is one QoD does not own), and it is enforced by the engine:
the rendered `ATTACH` carries `READ_ONLY`, so writes come back as DuckDB's
"attached in read-only mode" error, and every node refuses writes into it to
every principal, superusers included (`QoD: catalog <alias> is read-only`).
Rules worth knowing before the first create:
- **Credentials must be placeholders.** `--client-secret` / `--token` holding a
literal is a `400`: they must match `{{secret.NAME}}` exactly, and the value
lives in `qod federation secret set`. That is what keeps the config safe to
echo back on `qod federation get`. `--client-id` is not constrained this way.
- **The secret is set after the source exists** - secrets hang off the alias, so
step 1 must precede step 2. Step 1 does not check that the secret exists; an
unresolved `{{secret.NAME}}` fails the whole tenant-db federation blob at node
spawn, not just this catalog.
- **`create` upserts**, with the reset-on-omit behaviour described under
"Register a federated source" above. Changing an alias between `sql` and
`iceberg-rest` is refused: delete it first.
- **The alias rules are not Iceberg-specific.** See "Register a federated
source" above: they apply to every source type.
#### AWS-signed catalogs (Glue, S3 Tables, sigv4)
```bash
qod federation create acme acme_fed --alias glue_lake --type iceberg-rest \
--endpoint-type glue --warehouse 123456789012 \
--aws-credentials config --aws-region eu-west-1 \
--aws-key-id '{{secret.AWS_KEY_ID}}' \
--aws-secret-access-key '{{secret.AWS_SECRET}}' \
--aws-scope s3://my-lake/
qod federation secret set acme acme_fed glue_lake --name AWS_KEY_ID --value "$AWS_ACCESS_KEY_ID"
qod federation secret set acme acme_fed glue_lake --name AWS_SECRET --value "$AWS_SECRET_ACCESS_KEY"
```
- `--aws-credentials config` takes `--aws-key-id` + `--aws-secret-access-key`
(plus `--aws-session-token` for temporary credentials); `credential_chain`
takes no keys and uses the node's instance role, env or profile.
- `--aws-region` is required for `glue` and `sigv4`; `s3_tables` reads it off
the ARN when omitted.
- `--aws-scope` (repeatable) is **required**: the s3:// prefixes holding the
catalog's table data. The credentials apply only there. An unscoped secret
would outrank the node's own storage secret on every S3 read, the database's
own data included, so QoD refuses to render one.
- `--aws-secret-access-key` / `--aws-session-token` must be `{{secret.NAME}}`
placeholders, like `--client-secret`.
### Register a Postgres or MySQL database
A typed source for an external Postgres or MySQL database: no SQL to write, and
the password never sits in an `ATTACH` string. QoD renders a temporary DuckDB
secret holding the connection, an `ATTACH '' AS <alias> (TYPE postgres, SECRET
..., READ_ONLY)` and a `DROP SECRET` right after it, so no session can reuse
the credentials once the ATTACH returns (for instance to re-attach the server
writable).
```bash
# Read the password from stdin (or --password-prompt): the CLI stores it as the
# source's secret PASSWORD and puts the {{secret.PASSWORD}} placeholder in the source.
printf '%s' "$PG_PASSWORD" | qod federation create acme acme_fed --alias shop \
--type postgres --host pg.prod.internal --database shop --user qod_reader \
--schema sales --ssl-mode require --password-stdin
# MySQL: same flags, no --schema, MySQL TLS mode names
qod federation create acme acme_fed --alias crm --type mysql \
--host mysql.prod.internal --database crm --user qod_reader \
--ssl-mode required --password-prompt
# Or point at a secret you manage yourself (an --external-ref one, say).
# 1. Save the source disabled: its secret can only be set once the source exists,
# and an enabled source naming a missing secret fails the whole tenant-db
# federation blob at node spawn.
qod federation create acme acme_fed --alias shop --type postgres \
--host pg.prod.internal --database shop --user qod_reader \
--password '{{secret.SHOP_PG}}' --disabled
# 2. Set the secret. --external-ref needs a superuser session: a tenant admin gets
# 403 superuser_required and stores an inline --value instead.
qod federation secret set acme acme_fed shop --name SHOP_PG --external-ref env:SHOP_PG_PASSWORD
# 3. Enable it: the same create, without --disabled.
qod federation create acme acme_fed --alias shop --type postgres \
--host pg.prod.internal --database shop --user qod_reader \
--password '{{secret.SHOP_PG}}'
```
Then recycle the pool's nodes, as for any new source.
- `--host` (a host name or an IP address; a socket path is refused), `--database`,
`--user` and a password are required. `--port` defaults to 5432 / 3306.
- **The password is required and is always a secret.** `--password` takes only a
`{{secret.NAME}}` placeholder (a literal is refused: it would land in shell
history); `--password-stdin` / `--password-prompt` store the value as the
secret named by `--password-secret` (default `PASSWORD`). With no password at
all, the client library would fall back to the node's own credentials, which
is why QoD refuses that.
- `--ssl-mode`: postgres `disable | allow | prefer | require | verify-ca |
verify-full`; mysql `disabled | preferred | required | verify_ca |
verify_identity`. `--schema` (postgres only) attaches just that schema.
- `--connection '<json>'` passes the whole object (`host`, `port`, `database`,
`user`, `password`, `schema`, `sslMode`) instead of the flags.
- `--read-only` **defaults ON**, enforced by the engine (`READ_ONLY` on the
ATTACH, bound when a node starts) and by every node for every principal,
superusers included (`QoD: catalog <alias> is read-only`, from the next
statement on). The raw-SQL passthroughs `postgres_execute`,
`postgres_query`, `mysql_execute` and `mysql_query` are superuser-only on the
nodes, and the node does not read their SQL argument: a superuser can still
end the remote's read-only transaction through one and write (as through a
maintenance function taking a catalog name, or an ATTACH of the same source
under another alias). **Connect as a database user without write
privileges**: that is the complete guarantee. For `mysql`, whether
`READ_ONLY` alone refuses writes is unverified (the extension is not in the
DuckDB 2.0 toolchain).
- A `sql` source's setup SQL may not mention `qod_pg_`, `qod_mysql_` or
`qod_ice_`: those secret-name prefixes belong to the typed sources, whose
blocks create and drop them.
- A manifest import validates typed sources exactly as `create` does.
- `create` upserts, so re-running it edits the source, and every field it
leaves out is RESET (see "Register a federated source"). A re-run that
repeats only the required flags puts `--port`, `--schema`, `--ssl-mode` and
`--description` back to their defaults (no `--ssl-mode` means the client
library's own default, which for postgres is `prefer` and falls back to
plaintext), and enables a disabled source; the manager warns only about a
`--read-only` downgrade. So to rotate the password, re-run `create` with
`--password-stdin` (or `--password-prompt`) **and every flag you want kept**,
`--password-secret` included if you changed it. To rotate the secret alone,
`qod federation secret set ... --name PASSWORD --value ...` leaves the source
untouched but puts the password on the command line; the admin UI (its edit
form keeps every stored field) and the MCP `set_federated_secret` tool rotate
it without that exposure. Recycle the pool to apply.
- The attach state is reported like an Iceberg catalog's (next section).
### Check whether an Iceberg catalog actually attached
A failed `ATTACH` does NOT fail the node: it comes up healthy, serves
everything else, and the catalog is simply missing, so clients see
`Catalog 'icelake' does not exist`. Two places report it:
```bash
# Per source, aggregated over the pool's nodes:
# "attached" | "unknown" | "failed on N of M nodes"
qod federation get acme acme_fed icelake # -> .attachStatus
# Per node, with the DuckDB error that says why:
qod pool status --tenant acme --db acme_fed --pool bi # -> .nodes[].catalogAttachFailures
```
`attachStatus` is reported only for enabled typed sources (`iceberg-rest`,
`postgres`, `mysql`; a `sql` or disabled source has no attach state), and both views are **replica-local**: each
manager replica verifies only the nodes it tracks, so under HA the same node can
read differently depending on which replica answered.
The manager retries a missing catalog on its own health ticks, with backoff, and
logs `catalog '<alias>' attached on retry` when a transient failure heals. It
stops retrying a node once every declared catalog is attached, so a source added
to an ALREADY-RUNNING pool is not picked up until its nodes restart.
### Inspect an Iceberg catalog
Once an `iceberg-rest` source is attached, browse it, look at a table's snapshot
history, preview it at a past snapshot, and diff two snapshots the same way you
would a DuckLake tenant-db, by adding `--iceberg ALIAS` to the existing `qod
catalog` commands:
```bash
qod catalog schemas acme acme_fed --iceberg icelake
qod catalog tables acme acme_fed analytics --iceberg icelake
qod catalog describe acme acme_fed analytics orders --iceberg icelake
qod catalog history acme acme_fed analytics orders --iceberg icelake --limit 20
qod catalog preview acme acme_fed analytics orders --iceberg icelake --as-of 7761858545720969174
qod catalog data-diff acme acme_fed analytics orders --iceberg icelake --from <old id> --to <new id>
```
The same views are just SQL under the hood, usable directly through `qod sql`
(or any DuckDB client behind the ATTACH):
```sql
-- a raw snapshot list for ad hoc queries; `qod catalog history ... --iceberg` instead reads
-- iceberg_load_table_response, which carries per-snapshot summary counts this function does not
SELECT * FROM iceberg_snapshots(icelake.analytics.orders);
-- what `qod catalog preview ... --iceberg --as-of <id>` runs
SELECT * FROM icelake.analytics.orders AT (VERSION => 7761858545720969174);
```
Limits and differences from the DuckLake views:
- **Admin-only**, same gate as the DuckLake catalog views (`TenantDbGate`: tenant
admins and superusers). Preview and diff run as the calling identity (a PAT
runs as its owner, carrying the token's restriction), so ACL, CLS and RLS
apply exactly as they do on a normal query.
- **Snapshot ids are strings everywhere** - JSON fields, `--as-of`, `--before`,
`--from` / `--to`. Iceberg snapshot ids are random 64-bit values, too big for
a JSON number to round-trip safely.
- The CLI refuses `--as-of` / `--as-of-tag` / `--as-of-ts` with `--iceberg` on
`qod catalog describe`: table detail is current-only, and the server's
table-detail route itself takes no selector. Time travel lives in
`preview` and `data-diff` only; `--as-of-tag` is refused there too (Iceberg
tags are not exposed through this path) - use `--as-of` (a snapshot id) or
`--as-of-ts`.
- `qod catalog history --iceberg` pages with `--before <snapshot id>`, ordered by
sequence number rather than by the id itself (ids are random, not sequential).
No `--from` / `--to` / `--author` (refused with `--iceberg`).
- Table detail's file listing shows each file's content as `DATA`,
`POSITION_DELETES` or `EQUALITY_DELETES`, plus format and record count - no
file sizes, because DuckDB's `iceberg_metadata()` does not report them.
`total-records` is not shown in history either: DuckDB's position deletes do
not reduce it, so it would misreport the live row count.
- `qod catalog data-diff --iceberg` refuses with `413 diff_too_large` once
either snapshot has more than `QOD_CATALOG_ICEBERG_DIFF_MAX_FILES` (default
200) data files - the diff scans both versions in full. An UPDATE shows up as
one removed row plus one added row, not as a change in place.
- **Iceberg format v1 tables are refused** everywhere in these views (`400
unsupported_for_iceberg`, "table uses Iceberg format v1 (no snapshot sequence
numbers); these views need format v2 or later") - v1 carries no per-snapshot
sequence number, which history ordering and paging need.
- No restore, undrop, tags, schema diff or catalog-wide snapshot list on an
Iceberg source: none of those exist under `--iceberg` (DuckDB's Iceberg
extension does not support them).
### Switch the secret resolver
`secretStore = postgres | env | aws-sm | gcp-sm | azure-kv | vault`. Set via `QOD_FEDERATION_SECRET_STORE=env` (and per-backend config keys). The four KMS backends are stubbed in v1; calling `resolve()` raises `NotImplementedError`. To enable one, fill in the corresponding resolver class with the real SDK call.
`externalRef` formats:
| Backend | Format |
| -------- | ------------------------------------------------------------ |
| env | `env:SL_QOD_SECRET_FOO` |
| aws-sm | `aws-sm:arn:aws:secretsmanager:...` or `aws-sm:name#jsonKey` |
| gcp-sm | `gcp-sm:projects/<p>/secrets/<name>/versions/latest` |
| azure-kv | `azure-kv:<secretName>` (vault URL from config) |
| vault | `vault:secret/data/<path>#<key>` |
### Export / import as YAML
These two endpoints have no `qod` command yet - call them over REST with a
personal access token in `X-API-Key` (mint one with `qod auth pat create` and
keep it in `QOD_TOKEN`).
Export (`***REDACTED***` replaces every value-backed secret; `externalRef` is left as-is):
```bash
curl -H "X-API-Key: $QOD_TOKEN" \
"http://localhost:20900/api/tenants/acme/tenant-dbs/acme_fed/federated-sources/yaml/export" > fed.yaml
```
Re-import after editing. Secrets with `value: "***REDACTED***"` (and no `externalRef`) reuse the existing row's value, so a round-trip never requires re-typing passwords:
```bash
curl -X POST -H "X-API-Key: $QOD_TOKEN" -H 'Content-Type: text/plain' \
"http://localhost:20900/api/tenants/acme/tenant-dbs/acme_fed/federated-sources/yaml/import" --data-binary @fed.yaml
```
Import semantics: replace-by-alias inside the tenant-db. Sources absent from the YAML are deleted; secrets absent from a source are deleted.
### Lifecycle
- **Edits take effect on the next spawn.** Editing or disabling a source affects every Quack node spawned after the commit: idle-timeout replacement, manual restart, scale-up additions, pool recreation. Already-running nodes keep their attached catalogs until they exit.
- **Boot-time failure is fatal.** If a source's `setupSql` errors at node startup (extension missing, bad credentials, DNS), the spawn script exits 91 and the supervisor surfaces the last lines of stderr.
- **Disabled sources** are filtered out at blob assembly. No live DETACH.
- **Deleting a federated source** removes its alias from the policy holders' ambiguity check immediately, but running nodes keep the catalog attached until the pool recycles. Recycle the pool right after deleting a source.
### Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| `unresolved secret 'X' in source '<alias>'` in supervisor log | Source's setupSql references `{{secret.X}}` but no matching row | Add the secret row via `qod federation secret set` |
| `unsubstituted placeholder` at boot | Typo in setupSql like `{{secret.X}` (missing brace) | Fix setupSql via re-create (POST upserts); recycle the pool |
| `catalog 'fedpg' does not exist` from the client | Pool was not recycled after editing the source | Drop and recreate the pool, or wait for idle-timeout recycle |
| `missing RO grant on fedpg.public.X` | ACL not granted on the federated alias | `qod role permission grant --role-id <roleId> --catalog fedpg --schema public --table X --verb RO` |
| `kind env var is required` from spawn script | Manager invoked the script without setting `kind` | Manager release too old for this feature - upgrade (`qod start` with a current release) and restart |
| `secret '<name>' for source '<alias>' has no existing value to reuse` on YAML import | Imported `***REDACTED***` for a new source that didn't exist before | Provide the actual `value` or `externalRef` for that secret in the YAML |
| YAML import HTTP 400 `duplicate alias '<X>' in payload` | Two sources in the imported YAML have the same alias | Dedupe in the YAML before re-importing |
| HTTP 400 `clientSecret must be a secret placeholder of the form {{secret.NAME}}, not a literal value` | Credential passed inline to `qod federation create --type iceberg-rest` | Pass `'{{secret.NAME}}'` and store the value with `qod federation secret set` |
| HTTP 400 `awsScope is required` | `--aws-credentials` given without `--aws-scope` | Add `--aws-scope s3://<bucket>/` for each prefix holding the catalog's table data |
| `Assumed catalog secret ... does not have a region` / `Could not find a valid storage secret (s3 or aws)` in `catalogAttachFailures` | A `glue` / `s3_tables` / `sigv4` source created without `--aws-credentials` | Recreate it with the `--aws-*` flags |
| HTTP 400 `set exactly one of authType / endpointType` | Both `--auth` and `--endpoint-type` given (or neither) | Pick one: `--auth` for a plain REST catalog, `--endpoint-type` for Glue / S3 Tables |
| `attachStatus: failed on N of M nodes`, or `catalog '<alias>' does not exist` from the client | The Iceberg ATTACH failed on those nodes (bad credential, unreachable catalog, alias collision) | Read the per-node DuckDB error in `qod pool status` under `catalogAttachFailures`, fix the source or secret, then `qod node restart` the affected nodes |
### What does NOT need an ACL change
The existing RBAC graph covers federated tables with zero changes:
- Grant `RO` on `fedpg.public.orders` to role `analyst` via `qod role permission grant` (verb `RO`), exactly like a DuckLake table.
- Federated writes (INSERT/UPDATE/DELETE on a federated alias) require an `RW` grant on the same triple; otherwise they are denied.
- Read-only (`--read-only`) is enforced by every node for every principal, superusers included, from the next statement after the change; a `sql` source's `setupSql` should also include `READ_ONLY` itself, while typed sources get it on their ATTACH from QoD (bound when a node starts).
## Node status + metrics
```bash
# Live node table (used by the UI)
qod pool list
# Compact one-line-per-node view of the same data
qod --json pool list \
| python3 -c "import sys,json; d=json.load(sys.stdin); \
[print(f'{n[\"nodeId\"]:28s} role={n[\"role\"]:9s} healthy={n[\"healthy\"]} \
served={n[\"totalServed\"]:5d} p50={n[\"p50Ms\"]:4.0f} p95={n[\"p95Ms\"]:4.0f} p99={n[\"p99Ms\"]:4.0f}') \
for p in d['pools'] for n in p['nodes']]"
# Recent statement history (newest first)
qod node statements --limit 20
```
Per-node fields surfaced via `/api/pool/list`:
- `inFlight` - currently executing statements
- `totalServed` - lifetime counter since manager start
- `avgDurationMs` - EWMA latency
- `p50Ms`/`p95Ms`/`p99Ms` - rolling 256-sample window
- `healthy` / `draining` - tracker flags
On object-store pools the router biases toward a node that already has the query's tables cached (cache-aware placement, on by default); local-path pools stay pure least-loaded. `QOD_ROUTING_CACHE_AWARE=false` instantly reverts routing decisions to least-loaded everywhere while the locality metrics keep running.
Authorization metrics (Prometheus sink, the default `QOD_METRICS_SINK=prometheus`):
- `qod_authz_decisions_total{tenant, kind=plan|screen, outcome=allow|refuse|unavailable, mode=qod|opa}`:
every verdict a replica gave its nodes (dry runs excluded). Decision rate:
`sum(rate(qod_authz_decisions_total[5m])) by (outcome)`.
- `qod_authz_decide_duration_ms{kind}` (milliseconds, buckets 0.5 to 5000; exported as `_bucket`,
`_count`, `_sum` and `_max`). p95:
`histogram_quantile(0.95, sum(rate(qod_authz_decide_duration_ms_bucket[5m])) by (le, kind))`.
- Per node, exported by the HA leader only (a single manager always is), after the node's first
healthy probe tick: `qod_node_authz_{plan_cache_hits,plan_cache_misses,decide_calls,decide_unavailable,decide_refused,local_refusals,screen_calls,verdict_cache_evictions}_total{tenant, pool, node}`
and the gauges `qod_node_authz_verdict_cache_size` / `qod_node_authz_credential_sets_size`.
`screen_calls` includes the manager's own statements (preludes, health probe reads). A rising
`qod_node_authz_verdict_cache_evictions_total` means `QOD_AUTHZ_CACHE_ENTRIES` (default 10000) is
too small for the node.
- Per replica, the bounded authorization caches: `cache_size`, `cache_gets_total{result=hit|miss}`,
`cache_evictions_total` (and the other `cache_*` meters) with `cache` = `qod_minted_handles`,
`qod_issued_handles`, `qod_dry_run_results`, `qod_effective_sets`, `qod_opa_decisions` (the last
only while `QOD_OPA_CACHE_TTL_SEC` > 0), plus `qod_cache_capacity{cache}`, the configured bound. Sum
them across replicas. Rising `cache_evictions_total{cache="qod_minted_handles"}` means raise
`QOD_AUTHZ_HANDLE_CONTEXTS_MAX` (default 200000, at least 1000).
The node limits `QOD_AUTHZ_DECIDE_TIMEOUT_MS` (default 2000, 1 to 60000) and
`QOD_AUTHZ_CACHE_ENTRIES` (default 10000, 0 to 10000000) are set on the manager, which refuses to
start on a value outside those ranges and hands both to every node on every runtime (local,
Kubernetes, fleet); restart the nodes after changing them.
## Incident response
Quarantine is durable operator state, separate from node health. Check the quarantined flag in `pool/list` before debugging a node that shows unhealthy-like symptoms.
```bash
# Quarantine a node: stop routing new statements to it (running ones finish).
# Durable: survives manager restarts; only unquarantine clears it. Superuser only.
qod node quarantine --tenant acme --db acme_tpch --pool bi --node-id bi-1
qod node unquarantine --tenant acme --db acme_tpch --pool bi --node-id bi-1
# Restart a node: kills everything running on it, respawns with the same id, and clears any quarantine. Superuser only.
qod node restart --tenant acme --db acme_tpch --pool bi --node-id bi-1
# In-flight statements (tenant admins see only their tenant).
qod node active-statements
# Best-effort kill by statement id from the list above. "accepted" is not a guarantee:
# the manager closes the stream; a node that ignores disconnect keeps executing.
# Response is "accepted" (stream closed, best-effort) or "already-completed" (statement finished before the kill arrived).
# Escalate with node restart when the statement must die.
qod statement kill <statement-id>
```
## Audit log
The audit log records control-plane mutations, auth events, data-plane denials, and data-plane writes. It is backed by the `qodstate_audit` Postgres table when `QOD_TELEMETRY_STORE=postgres` (the default).
```bash
# Most recent 50 control-plane events
qod audit list --family control-plane --limit 50
# Page through with the keyset cursor (use nextBefore from the previous response)
qod audit list --before <nextBefore-from-previous-page>
# Failed logins in a time window
qod audit list --action auth.login.failure --from 2026-07-01T00:00:00Z
# Only no-tenant rows (anonymous auth failures, node ops, manifest imports; superuser only)
qod audit list --no-tenant
# Exhaustive action vocabulary for exact --action filters
qod audit actions
```
Filters: `family`, `tenant` (superuser: returns only that tenant's rows; null-tenant rows not included when this is set), `noTenant=true` (superuser: only null-tenant rows; wins over `tenant`), `actor`, `action` (exact), `q` (substring on action/target), `from`, `to` (ISO-8601), `limit` (max 500), `before` (keyset cursor). Results are newest-first.
Tenant admins see only their own tenant's rows. Superusers see everything, including null-tenant rows (anonymous failures, node ops, manifest imports).
**Retention and the off switch:**
| Env var | Default | Effect |
|---|---|---|
| `QOD_TELEMETRY_JOURNAL_CAPACITY` | `8192` | Bounded in-process telemetry journal queue depth, also the stdout sink's queue depth. Overflow drops events and increments `qod_journal_dropped_total` (label `table`: `audit` / `stmt_history` = not written to Postgres, `stdout` = not written to stdout). Increase to buffer higher statement throughput under Postgres write latency spikes |
| `QOD_AUDIT_RETENTION_DAYS` | `90` | Delete rows older than N days (hourly purge); set to `0` to keep forever |
| `QOD_TELEMETRY_STORE` | `postgres` | `none` disables all recording, hides the Audit UI page, and keeps the drop counter at zero |
| `QOD_AUDIT_SINK` | `none` | `stdout` also writes every audit and statement event to stdout as one JSON line (`qodEvent=audit` or `statement`) for a log shipper. The line is redacted: SQL literals become `'?'`, engine error text is scrubbed, and on a failed auth event the typed login name and tenant become a keyed hash. Needs `QOD_TELEMETRY_STORE=postgres`. Lines dropped under stdout backpressure count in `qod_journal_dropped_total{table="stdout"}` |
## Statement history and trends
Statement history records every FlightSQL statement (including reads). Raw rows are searchable for a short window; aggregated rollups drive trend charts over a longer period. Both are tenant-scoped: superusers see all tenants, tenant admins see only their own.
```bash
# Most recent 50 statements for a tenant
qod history statements --tenant acme --limit 50
# Page through with the keyset cursor (use nextBefore from the previous response)
qod history statements --before <nextBefore-from-previous-page>
# Find yesterday's slow statements: fetch the window, filter durationMs locally
# (there is no duration filter parameter on the endpoint)
qod --json history statements --tenant acme --pool bi \
--from 2026-07-05T00:00:00Z --to 2026-07-06T00:00:00Z --limit 500 \
| python3 -c "
import sys, json
rows = json.load(sys.stdin).get('statements', [])
slow = [r for r in rows if r.get('durationMs', 0) > 5000]
for r in sorted(slow, key=lambda x: -x.get('durationMs', 0)):
print(r.get('durationMs'), r.get('username'), r.get('sql', '')[:80])
"
# Hourly trend for the last 7 days
qod history trends --granularity hour --tenant acme --pool bi \
--from 2026-06-29T00:00:00Z --to 2026-07-06T00:00:00Z
# Daily trend for the last 30 days
qod history trends --granularity day --tenant acme \
--from 2026-06-06T00:00:00Z --to 2026-07-06T00:00:00Z
```
Statement filters: `tenant`, `pool`, `user`, `status` (`ok`, `denied`, `transient`, `permanent`, `no-node`, `no-pool`, or `pin-lost`), `q` (substring on SQL), `from`, `to` (ISO-8601), `limit` (max 500), `before` (keyset cursor). Results are newest-first.
Trend filters: `granularity` (required: `hour` or `day`), `tenant`, `pool`, `from`, `to`. Hourly buckets include p50/p95/p99; daily buckets have null percentiles.
**Retention env vars:**
| Env var | Default | Effect |
|---|---|---|
| `QOD_STMT_HISTORY_RETENTION_DAYS` | `7` | Delete raw statement rows older than N days (hourly purge); set to `0` to keep forever |
| `QOD_HOURLY_ROLLUP_RETENTION_DAYS` | `90` | Delete hourly rollup rows older than N days (hourly purge); set to `0` to keep forever |
| `QOD_ROLLUP_INTERVAL_SEC` | `300` | How often the rollup job recomputes touched buckets (leader-gated in HA mode) |
`QOD_TELEMETRY_STORE=none` disables all recording, hides the History UI page, and keeps the drop counter at zero. Raw retention must stay at least 2 days so the daily recompute can rebuild its whole-day bucket.
## Usage and accounting
Durable per-tenant / per-pool / per-user metering over daily rollups. Tenant-scoped like the
history endpoints: superusers see all tenants, tenant admins are pinned to their own.
```bash
# Month-to-date per tenant (defaults: current calendar month UTC, group-by tenant)
qod usage
# A closed month per pool, for billing
qod usage --from 2026-06-01T00:00:00Z --to 2026-07-01T00:00:00Z --group-by pool --tenant acme
# CSV extraction for billing (columns per the spec contract)
qod --json usage --from 2026-06-01T00:00:00Z --to 2026-07-01T00:00:00Z \
| jq -r '["tenant","statements","errors","denied","engine_ms"],
(.groups[] | [.tenant, .statements, .errors, .denied, .engineMs]) | @csv'
```
Params: `from` / `to` (ISO-8601 instants, half-open, default = current calendar month),
`groupBy` (`tenant` default, `pool`, `user`), `tenant`, `pool`. Groups are sorted by
`engineMs` descending; each group carries a per-day `days` array. `dataStart` marks the
oldest daily bucket still retained.
| Env var | Default | Effect |
|---|---|---|
| `QOD_USAGE_RETENTION_DAYS` | `400` | Delete daily rollup buckets older than N days (hourly purge); `0` = keep forever |
## Branches (an agent proposes, a human merges)
A branch is a writable, zero-copy clone of a DuckLake database at its current
head: the branch reads the parent's Parquet files in place and writes its own
files under a sibling prefix, served by its own one-node pool. Reads and writes
on a branch never touch the live database. Grants, row and column policies
written against the parent apply unchanged on the branch (same catalog name).
Branch names: lowercase letter first, then letters, digits, `_` or `-`, at most
48 characters. Only DuckLake databases can be branched, and never a branch.
```bash
# 1. Create a branch of acme's tpch1 database (expires after 24h; omit for the server default,
# 0 = never). Anyone who can connect to a pool of the database may branch it.
qod branch create --tenant acme --db acme_tpch1 --name feature-x --ttl-hours 24
# 2. Work on the branch. The FlightSQL `branch` connection header (or `qod sql --branch`)
# routes the session to the branch's pool; authorization still runs against --pool.
qod sql --tenant acme --pool bi --branch feature-x \
"UPDATE tpch1.nation SET n_comment = 'reviewed' WHERE n_nationkey = 3"
# 3. Review: touched tables (created / dropped / recreated / modified / altered), row counts,
# conflicts against main, and the merge verdict; then the row-level diff of one table.
qod branch changes --tenant acme --db acme_tpch1 --branch feature-x
qod branch diff --tenant acme --db acme_tpch1 --branch feature-x --schema tpch1 --table nation
# 4. Propose. Records a merge request with the change set as of now.
qod branch propose --tenant acme --db acme_tpch1 --branch feature-x
# 5. Merge, as a DIFFERENT principal than the proposer (403 self_merge_forbidden otherwise).
# Fast-forward only: 409 merge_conflict lists the tables main changed since the fork.
# On success main gains ONE snapshot stamped with proposer and approver, a tag
# `merge-<branch>-<id8>`, and the branch is torn down.
qod --profile reviewer branch merge --tenant acme --db acme_tpch1 --branch feature-x
# Or discard (owner or a tenant admin). Expired branches are discarded by the manager.
qod branch discard --tenant acme --db acme_tpch1 --branch feature-x
qod branch list --tenant acme --db acme_tpch1 --all
```
The admin console has the same lifecycle on the tenant page, "Branches" tab:
create, list (live or all), expand a branch for its change set, per-table row
diffs and merge history, then propose, merge or discard.
Agents over MCP get the same lifecycle as data-tier tools: `create_branch`,
`list_branches`, `branch_changes`, `diff`, `propose_merge`, `discard`, and a
`branch` argument on `run_sql`, `list_tables` and `describe_table`. There is
deliberately no merge tool. To make "agents never write main" a hard rule,
mint the agent's token with `qod auth pat create --name agent --branch-only`:
INSERT / UPDATE / DELETE / DDL on the live database are then refused with
`write_requires_branch`, reads are unchanged, and every child token inherits
the flag.
Merge limits in v1: column changes (add / drop / retype), views, macros and
schema-level DDL on the branch are refused with `422 merge_unsupported`; use a
fresh branch for data changes and apply DDL on main. A merge that loses a
DuckLake commit race answers `409 concurrent_write` and reopens the branch:
re-propose and retry. `qod branch create --from-snapshot` is reserved (v1 forks
at head only).
Safety rules the manager enforces: the fork snapshot of every live branch is
pinned against maintenance expiry on the parent, branch catalogs are never
maintained, and `database delete` refuses a database with live branches
(`409`). Knobs: `QOD_BRANCH_ENABLED`, `QOD_BRANCH_DEFAULT_TTL_HOURS` (168),
`QOD_BRANCH_MAX_PER_DATABASE` (20), `QOD_BRANCH_SWEEP_SEC` (300),
`QOD_BRANCH_MERGE_TIMEOUT_SEC` (600).
## Ad-hoc queries (qod sql)
`qod sql` runs SQL against the FlightSQL edge and prints a terminal table
(`--csv`, or the global `--json`, for machine output). `qod login` stores the
edge host/port/TLS and the SQL username in the active CLI profile, so after a
login it needs no flags beyond the routing target. It's the quickest way to
confirm what a given user actually sees - handy for spot-checking ACL,
column-, and row-level policies.
```bash
# Query as the profile's user (prompts once for the SQL password)
qod sql --tenant acme --pool bi \
"SELECT c_mktsegment, count(*) FROM tpch1.customer GROUP BY 1 ORDER BY 1"
# Same query as a superuser (system realm) - bypasses RLS/CLS, so diffing the
# two outputs shows exactly what a policy filtered or masked. Keep one CLI
# profile per principal and pick one with --profile.
qod --profile root sql --tenant acme --pool bi --superuser \
"SELECT c_mktsegment, count(*) FROM tpch1.customer GROUP BY 1 ORDER BY 1"
# Interactive REPL (\q quits), or a script file (first error aborts, exit 1)
qod sql
qod sql --file setup.sql
echo "SELECT 1" | qod sql --file -
```
Connection settings resolve like every other CLI setting: flags > `QOD_HOST` /
`QOD_PORT` / `QOD_TLS` / `QOD_USER` / `QOD_PASSWORD` / `QOD_TENANT` /
`QOD_POOL` / `QOD_SUPERUSER` env vars > the profile written by `qod login`.
Unqualified table names resolve against the pool's default schema, but the
FlightSQL prepare-time probe needs a real table - schema-qualify
(`tpch1.customer`) if you hit "Table … does not exist" at prepare.
Two-part names are only unambiguous when the head is a schema in the pool's default catalog, as in `tpch1.customer` above. When the head instead names an attached catalog (the tenant-db itself, e.g. `acme_tpch`, or a federation alias), the node judges it as DuckDB binds it (catalog-first); for a user holding column or row policies it is rejected as ambiguous, since the policy rewriters cannot tell which catalog you meant. Write the full three-part form to be safe: `acme_tpch.tpch1.customer`.
`VARIANT` columns reach FlightSQL clients as the Arrow `arrow.parquet.variant` extension type. A
client that does not know that extension sees a struct of two binary fields (`metadata`,
`value`); cast to JSON in the query for such clients (`SELECT v::JSON AS v ...`).
## Connecting from DuckDB (native Quack protocol)
Besides FlightSQL, the manager serves DuckDB's own Quack protocol on a dedicated port
(default `9494`, env `QOD_QUACK_PORT`; `QOD_QUACK_ENABLED=false` turns the listener off).
Any DuckDB 2.0 that carries the `quack` extension (the CLI, the Python package, an embedded
DuckDB) attaches the gateway as a database, with no driver in between, and joins it with
its local tables. The token string is the same set of parameters the FlightSQL JDBC URL
takes after `?`; a superuser adds `&superuser=true`, an OIDC bearer replaces
`user`/`password` with `token=<jwt>`.
```sql
-- attach once (DuckDB 2.0 client; a 1.x client fails with
-- "requires a DuckDB 2.0 client"), then query like any other catalog
ATTACH 'quack:localhost:9494' AS qod
(TYPE quack, TOKEN 'tenant=acme&pool=bi&user=alice&password=<password>');
SELECT count(*) FROM qod.tpch1.customer WHERE c_mktsegment = 'BUILDING';
SELECT c.c_name FROM qod.tpch1.customer c JOIN my_local_table l USING (c_custkey);
INSERT INTO qod.tpch1.staging SELECT * FROM my_local_table; -- needs a write grant
-- one shot, no ATTACH
SELECT * FROM quack_query('quack:localhost:9494', 'SELECT count(*) FROM tpch1.orders',
token := 'tenant=acme&pool=bi&user=alice&password=<password>');
```
What applies is exactly what applies to a FlightSQL client: the same handshake gates
(tenant scope, pool grant), per-statement routing across the pool, the nodes' grant check,
column masking and row filters, metadata filtering, author stamping, statement history, audit
(origin `quack`), kill, and scale-to-zero wake-up. A denied table answers the node's
`QoD: ...` refusal inside DuckDB's error; a bad token answers `Authentication failed`
at `ATTACH` time.
Things to know:
- **TLS.** The DuckDB client speaks plain HTTP to `localhost` / `127.0.0.1` and TLS to
every other host, and cannot be told to use TLS on loopback. The listener is therefore
plain HTTP by default; before exposing it beyond the host either set
`QOD_QUACK_TLS_ENABLED=true` (the FlightSQL edge's certificate is reused; the client
does not verify self-signed certificates by default) or terminate TLS in front of the
port. A remote client talking to a plain-HTTP listener adds `DISABLE_SSL true` to the
`ATTACH` options (`disable_ssl := true` on `quack_query`).
- **Sessions are per manager replica.** Under HA, put a session-sticky balancer in front
of the port; a client whose next request lands on another replica gets
`Invalid connection id` and must re-attach.
- **Transactions.** `BEGIN; ...; COMMIT` from a DuckDB client runs on one node connection
(unlike FlightSQL, where each statement lands on a fresh node session). Session state
outside an explicit transaction (SET, temp tables) does not carry over between
statements.
- **Upstream client limitation** (quack extension `40de7ba`, DuckDB 1.5.4, also against a
raw node): a multi-column result larger than the client's inline batch (about 24k rows)
fails inside the client with `Attempted to access index 1 within vector of size 1`.
Single-column results of any size, and multi-column results that fit inline, work.
- **Long statements.** A DuckDB 2.0 client gives up on a request after its `http_timeout`
(default 30 seconds) and fails with `Timeout was reached`, whatever the gateway is doing.
Before a statement that may run longer, raise it in the client session:
`SET http_timeout = 600;` (seconds). FlightSQL clients are not affected.
- **Personal access tokens are not accepted** on this wire (same rule as FlightSQL); use
a user and password or an OIDC bearer.
## Fleet mode (nodes on your own servers, no Kubernetes)
Manager: `QOD_RUNTIME_TYPE=fleet QOD_FLEET_JOIN_TOKEN=<random> QOD_INTERNAL_URL=http://<manager address the servers reach>:20900`
(plus HA if you run several managers). `QOD_INTERNAL_URL` is required in fleet mode (boot refuses
without it: `fleet mode requires QOD_INTERNAL_URL ...`): every node calls it back for each
statement's authorization decision, so it must be an address the servers reach the manager's REST
port on, plain `http://`, on the private network. Under HA every fleet manager sets its own
`QOD_INTERNAL_URL`, its own address on the network the fleet servers share; never a load balancer
or an address several replicas share (the servers may heartbeat to one), since a node's verdict
calls must reach the replica that minted its handle. A host name there is resolved once per 30 s
by each node, outside the decision's deadline: use a name that resolves quickly, or an address.
Every Linux or macOS server: `QOD_FLEET_JOIN_TOKEN=<random> uvx qod fleet join --manager https://mgr --advertise-host <data-ip>`
(`https://mgr` is a TLS-terminating proxy in front of the REST port: the port itself has no TLS, so never point `https://` at `:20900`. `qod fleet join` refuses `http://` because the assignment carries credentials; `--manager http://mgr:20900 --insecure` is for tests on a trusted network only)
under systemd or launchd (unit files below): `qod fleet join` is long-running, it heartbeats and runs the node the manager assigns until stopped. The node is qod-node, the node binary qod bundles for Linux and macOS (x86_64, aarch64), installed in the qod cache beside the DuckDB library qod downloads; `--qod-node-bin <path>` runs another build. Run the same qod version on the servers as the manager. Pass the token through the environment, never
`--join-token`: a command-line argument is visible to every local user in `ps`. A joined server runs exactly one node; pools are scheduled
onto free servers whose reported RAM covers the pool's `--memory`. `qod pool create --size 3` before three
servers exist is fine: the pool shows `pending` (reason `none_free` or `none_fits`) until they join.
The join token is as sensitive as the control-plane Postgres password: whoever holds it can join a
server and receive the credentials of every pool scheduled onto it. Keep unit files root-only, rotate
on suspicion (every heartbeat carries the token, so after a rotation restart each `qod fleet join` with the new value; one left on the old token goes unreachable, then dead), and check `qod fleet servers` for names you
did not install. Managers and servers must share a private network: the manager-to-node hop is
plain HTTP.
**Approving servers.** `QOD_FLEET_AUTO_APPROVE` on the manager lists the networks (CIDRs,
comma-separated) whose servers are approved as they join; the default `0.0.0.0/0,::/0` approves
everyone, and an empty value approves no one automatically. A server outside the list joins with
`approval: pending` (the UI shows "pending approval"), heartbeats, and takes no node; its `qod fleet join`
logs "waiting for approval". Check where it came from and approve it:
qod fleet servers # approval and sourceAddr fields
qod fleet approve <name>
Behind a load balancer, list it in `QOD_FLEET_TRUSTED_PROXIES` or every server appears to come
from the balancer. Removing a server forgets its approval. To refuse a server,
`qod fleet remove <name>` and stop its `qod fleet join`, otherwise it re-joins as pending. A drained server
that comes back from a new address loses its approval and is judged again.
An approval is bound to the address it was approved from, so an approved server heartbeating from
another machine gets `source_change_refused`; to move one, `qod fleet drain <name>`, start
`qod fleet join` on the new machine and let it re-join, `qod fleet approve <name>` once it shows as pending,
then `qod fleet undrain <name>` (a new address inside `QOD_FLEET_AUTO_APPROVE` is accepted without
this).
After an upgrade, servers approved before the manager recorded source addresses bind to the first
heartbeat from inside `QOD_FLEET_AUTO_APPROVE`; if you narrow the list, those outside it get
`approval_unbound` (their heartbeats are refused) until you run `qod fleet drain <name>`,
`qod fleet approve <name>` once it shows as pending, then `qod fleet undrain <name>`. `qod fleet
approve` answers `source_unknown` while the server has no known source address yet: wait for its
next heartbeat. Behind a trusted proxy that does not send `X-Forwarded-For`, a server's source
stays unknown, so it stays pending and `qod fleet approve` answers `source_unknown`; fix the proxy
or `QOD_FLEET_TRUSTED_PROXIES`.
**Reading fleet errors.** Every fleet refusal states its facts (registered vs reported address,
approved source, drained, the node a server holds) and ends with a `to fix` block saying where to
run each command: `on the manager:` is any host with an admin qod profile, `on this server:` the
joining machine (`qod fleet join` labels it that way; other qod commands and the UI print
`on <server name>:`); `to fix (any one):` lists alternatives. Before its first successful heartbeat,
`qod fleet join` exits 2 on a refusal no retry can fix (wrong token, wrong URL or scheme, a name
registered with another address); retryable refusals and network errors are retried before the
first heartbeat too, and under the systemd unit below a join that exited is re-run every 5 s.
After the first heartbeat it keeps its node running and retries, printing one
`still failing` line per attempt. In `qod fleet servers`, a failed node's `nodeError` comes with a
`nodeAction` when the failure is a known one (port in use, extension download blocked, catalog
Postgres unreachable, duckdb missing). A pending pool's `pendingDetail` (`qod pool list`, the UI's
pending badge) says what every server is doing and the next command.
systemd unit (`/etc/systemd/system/qod-fleet-join.service`):
```
[Unit]
StartLimitIntervalSec=0
[Service]
ExecStart=/usr/local/bin/qod fleet join --manager https://mgr --advertise-host 10.0.3.17 --name %H
Environment=QOD_FLEET_JOIN_TOKEN=<random>
Restart=always
RestartSec=5
KillMode=control-group
[Install]
WantedBy=multi-user.target
```
launchd plist (`/Library/LaunchDaemons/ai.starlake.qod-fleet-join.plist`): `ProgramArguments` with the same
command line, `KeepAlive` true, `EnvironmentVariables` carrying the token. On a crash the next `qod fleet join`
start reaps the previous node through `node.pid` in the state dir.
The node does NOT inherit the join process's whole environment (it holds the join token). It gets only
`PATH HOME TMPDIR LANG LC_ALL TZ USER LOGNAME SHELL`, the proxy variables,
`QOD_S3_*` / `QOD_AZURE_*`, and these metastore settings, so set them on the `qod fleet join` unit when needed:
`PG_ADMIN_DB` (the database `CREATE DATABASE` runs from, when it is not `postgres`), `PGSSLMODE`,
`PGSSLROOTCERT`, `PGSSLCERT`, `PGSSLKEY`, `PGCONNECT_TIMEOUT` (a metastore reached over TLS) and
`SSL_CERT_FILE`. Anything else exported on the join process never reaches a node.
- `qod fleet servers` lists servers with liveness (reachable / unreachable / dead) and the node they run.
- Maintenance on a server: `qod fleet drain <name>` (its node moves elsewhere or goes pending), work,
`qod fleet undrain <name>`. To retire it: drain, stop its `qod fleet join`, `qod fleet remove <name>`.
- A server silent longer than `QOD_FLEET_REASSIGN_AFTER_SEC` (default 600) loses its slot to a free server.
- "no fleet server" on a maintenance run or branch merge: those need a spare server; keep one free, or set
`QOD_FLEET_EPHEMERAL=local` so they run on the manager host (needs duckdb there).
- Data paths must be object storage reachable from every server; so must the metastore Postgres.
- `qod pool create --cpu 2 --memory 4Gi` (or `pool set-resources`) caps each node's DuckDB threads and
memory_limit. This is DuckDB enforcing it, not the kernel; an explicit SET in the database init SQL wins.
### Fleet servers as Docker containers
Manager and servers can all be containers. One container per server (normally one per host), from
`starlakeai/quack-on-demand-worker` (it runs `qod fleet join` and bundles the node binary and its DuckDB
library):
docker run -d --name qod-worker --restart unless-stopped --stop-timeout 70 \
-p 21900:21900 \
-e QOD_MANAGER_URL=http://mgr.internal:20900 -e QOD_FLEET_INSECURE=true \
-e QOD_FLEET_JOIN_TOKEN=<token> \
-e QOD_FLEET_NAME=<stable name, e.g. the host name> \
-e QOD_FLEET_ADVERTISE_HOST=<this host's address the manager can dial> \
-e QOD_FLEET_NODE_PORT=21900 \
-e QOD_S3_ENDPOINT=... -e QOD_S3_ACCESS_KEY_ID=... -e QOD_S3_SECRET_ACCESS_KEY=... \
starlakeai/quack-on-demand-worker:latest
- `QOD_FLEET_NAME` and `QOD_FLEET_ADVERTISE_HOST` are required: a container's hostname changes on every
recreate (each would join as a new server) and its own IP is a bridge address the manager cannot reach.
- Publish the node port with the SAME number on both sides (`-p 21901:21901` with
`QOD_FLEET_NODE_PORT=21901`); a remapped port is not supported. Several workers on one host each take
their own port, which is also how to try fleet mode on a single machine.
- `docker run --memory / --cpus` limits are what the server reports as capacity, so `pool create
--memory` only lands on containers big enough.
- The manager container needs `QOD_RUNTIME_TYPE=fleet`, the same `QOD_FLEET_JOIN_TOKEN`,
`QOD_INTERNAL_URL` set to the address the workers reach it at (`http://host.docker.internal:20900`
from containers on the same host), an object storage data path, and, when it reaches Postgres by a name the servers cannot resolve (a compose
service name), `QOD_FLEET_NODE_PG_HOST` / `QOD_FLEET_NODE_PG_PORT`: the managed Postgres address
handed to servers. A database on its own Postgres or with its own object store endpoint must be
reachable from the manager and every server under the same address.
- Behind Docker NAT the manager may see a gateway address as the server's source. The default
`QOD_FLEET_AUTO_APPROVE` admits it; a narrowed list must include that address (`qod fleet servers`
shows `sourceAddr`).
## Hardening (node enforcement, pod security, network policy, reader eviction)
Self-serve / hosted deployments need a tighter isolation posture than the OSS
default (single trusted-operator assumption). What the nodes enforce is always on:
there is no ACL switch and no per-pool lockdown any more (`QOD_ACL_ENABLED` or
`QOD_NODE_LOCKDOWN` set to `false` makes the manager refuse to start, naming the
variable to unset; set to anything else, it is ignored with a warning). Pod security is always on too; network policy and reader
eviction are opt-in knobs.
- **Always on, superuser-only (no flag)** - whatever the tenant's mode (QoD grants
or OPA) and the grants (`*.*.* ALL` included), a caller who is not a superuser is
refused, on every pool:
- the DuckLake metadata catalog `__ducklake_metadata_<alias>` (it holds the
per-file encryption keys) and `ducklake_list_files` / `ducklake_commit`
("QoD: ... is DuckLake catalog metadata, which only superusers reach"). A user
who may create in a schema can still create a plain view over it, but every
read through that view by anyone but a superuser is refused the same way: a
plain view runs with its reader's rights;
- node-wide settings in any `SET` / `RESET` / `PRAGMA` form (threads,
`memory_limit`, `max_temp_directory_size`, `temp_directory`, checkpoint /
WAL / allocator knobs, logging and profiling, every `quack_*`, `debug_*`,
`verify_*`), any `SET GLOBAL` / `RESET GLOBAL`, `CHECKPOINT` / `FORCE
CHECKPOINT`, and the node-control calls (`quack_serve`, `quack_stop`,
`enable_logging`, ...) ("QoD: setting <name> is superuser-only", "QoD:
setting <name> at global scope is superuser-only", "QoD: function <name> is
superuser-only").
Set resources at node start instead: database / pool `initSql` or
`qod pool set-resources`. Session settings (`search_path`, `TimeZone`,
`http_timeout`, `SET VARIABLE`, ...) stay open, but any of them other than
`schema` / `search_path`, a `SET VARIABLE`, a temporary view, macro or type,
or a `USE` of another schema (a search path other than the session's
default) makes every secure view unreadable for as long as the session holds
it (`QoD: statement uses a secure view while the session ...`). A client
that sets `TimeZone` on connect loses secure views for that session;
- trigger DDL (`CREATE` / `DROP TRIGGER`), tenant admins included, and
`pg_get_viewdef` (it would print every view's SQL).
Superusers are not restricted by these rules on any pool: they may also set the
parser and credential settings and run `IMPORT DATABASE`.
- **Table grants, enforced on the nodes, always** - each node decides every tenant statement
on what DuckDB resolved (plain views by the tables they read, secure views by their own grant),
and nothing else checks it: a statement no grant covers is refused ("QoD: ...") as a permission
error (FlightSQL `UNAUTHORIZED`, history status `denied`). Every function and setting a tenant statement calls must be classified:
an unknown name, a misspelled one included, reads `QoD: function <name> is not classified`, and so
does a macro a superuser created, or a catalog macro named like a built-in, until it is called as
`system.main.<name>` or classified. Views follow the same rules: a view whose body calls a user
macro, an unclassified or superuser-only function, the `histogram(<table>, <column>)` table macro,
or a `query()` with a computed argument or one running a statement other than a query, or that
nests views more than 16 deep, is unreadable for tenant users, and so is a table
whose generated column, default, CHECK constraint or trigger does that (unreadable or unwritable).
A secure view must qualify every table, function, macro, type and sequence its body names, read
only base tables, other secure views and built-ins (no plain view, no user type), advance no
sequence and call no `current_setting()`. A wildcard grant (`*.*.*`, `qod_all_tables`) never
covers a federated catalog: grant it by its alias. It does cover the `memory` catalog on a memory
database's own pool and a database attached under a custom alias. Cost: each plan a node has not judged costs
one call to the manager replica that minted the session (about 0.8 ms per call; a fresh FlightSQL
query makes two, its schema probe and the statement, about 1.4 ms end to end on one host); the
verdict is then cached on that node for 60 s (an OPA tenant's for its OPA cache TTL), so a
statement repeated all day costs one or two calls per minute per node. Sessions of one user with the same grants share
verdicts; bearer-token sessions share them only within one token, so a client that mints a token
per connection pays a call on the first run of every statement. A revoke reaches open sessions
on their next statement; a re-grant restores them without a reconnect.
- **Direct file access, only where the user may reach** (always) - a tenant user, `*.*.* ALL`
included, reads or writes a file by path only through a file reader or `COPY` naming an `s3://`
location one of their own S3 credentials covers, or a folder the tenant authorized (see "Files and
S3 for tenant users"), never a database data folder; a path or URL in a table position (`FROM
's3://b/x.parquet'`), `EXPORT` / `IMPORT DATABASE`, `ATTACH`, `CREATE` / `DROP SECRET` and `SET` of
the `s3_*`, proxy, certificate and cache settings stay refused. The nodes decide this on what DuckDB
parsed and resolved ("QoD: ... is outside the tenant's authorized locations") and sign each user's
S3 requests with that user's keys only (connection secrets, the file caches off). The native front
door's client data (`INSERT INTO t SELECT * FROM <client table>`) still flows; such a scan sent any
other way (FlightSQL, MCP) is refused to every caller.
- **Extensions and path settings, superuser-only** -
a tenant user cannot `INSTALL` / `FORCE INSTALL` / `UPDATE EXTENSIONS` or `LOAD` an
extension, by bare name included ("QoD: statement uses load, which only superusers may run"):
install and load extensions at node start, in a database or pool `initSql`.
`autoinstall_known_extensions`, `allow_community_extensions` and `allow_unsigned_extensions` are
false on every node unless the operator sets `QOD_NODE_AUTOINSTALL_KNOWN_EXTENSIONS`,
`QOD_NODE_ALLOW_COMMUNITY_EXTENSIONS` or `QOD_NODE_ALLOW_UNSIGNED_EXTENSIONS` (default false,
applied before each node's database opens, so pool and database `initSql` cannot load community or
unsigned code either; an `initSql` that installs a community extension needs the operator setting;
the Helm chart has no value for them, set them in its `env`). With autoinstall off, a statement that
relied on DuckDB downloading a known extension fails until that extension is installed at node
start (`INSTALL <name>;` in a database or pool `initSql`); nodes install httpfs and avro
themselves, before the database's `initSql`, so an `extension_directory` or
`custom_extension_repository` set there does not apply to those two (on an air-gapped mirror, or a
pod whose HOME is fresh at every start, both are fetched from DuckDB's default repository at every
boot). Only a superuser may change them, and DuckDB refuses either `allow_*` setting once a node
runs. Nor can a tenant user set
`file_search_path`, `home_directory`, `allow_asterisks_in_http_paths`, `ca_cert_file`
or the server certificate verification settings. `shell_history()` does not exist on a node
(qod-node is not the DuckDB CLI).
- **Node-state introspection, superuser-only** - a tenant user cannot read
`duckdb_secrets()`, `which_secret()`, `whoami()`, `duckdb_settings()` / `pg_settings`,
`current_setting(..)` of any setting (a node cannot tell which one a call reads), the
logs (`duckdb_logs*`), `duckdb_external_file_cache()`, `duckdb_temporary_files()`,
`duckdb_databases()` / `PRAGMA database_list`, `duckdb_extensions()`, or
`pragma_storage_info` ("QoD: function <name> is superuser-only", or in a FROM clause "QoD:
table function <name> is not available to non-superusers"). Catalog listings
(`duckdb_tables()`, `duckdb_views()`, `duckdb_schemas()`, `duckdb_columns()`, `information_schema`)
stay open and filtered; the other catalog-wide listings (`duckdb_constraints()`, `duckdb_indexes()`,
`duckdb_sequences()`, `duckdb_triggers()`, `duckdb_types()`, `duckdb_functions()`,
`duckdb_dependencies()`) are superuser-only, `*.*.* ALL` included, since nothing filters them yet.
For a tenant user the gateway answers `DESCRIBE <t>`, `SHOW <t>`, `PRAGMA table_info('<t>')`,
`SUMMARIZE <t>` and `SHOW TABLES` with equivalent SELECTs over the filtered listings plus a read of
the object (so they need a read grant on it); `DESCRIBE (<query>)` and `SUMMARIZE (<query>)` stay
refused. DESCRIBE's `key` and table_info's `pk` come back NULL. On an OPA tenant these answers are
empty and SUMMARIZE is refused, since the listings are narrowed by QoD grants. Inside a native front
door transaction a refused DESCRIBE or SUMMARIZE aborts the transaction, like any statement the node
refuses there.
- **No per-pool switch** - the node enforces the rules above on every pool, so there is
nothing to turn on per pool or manager-wide (the former per-pool override, its REST
endpoint, CLI command and flag, MCP tool and pool field are gone; an old manifest's
pool field is ignored on import). Nodes no longer
run `disabled_filesystems = 'LocalFileSystem'` on object-store pools; extension
settings follow `QOD_NODE_ALLOW_COMMUNITY_EXTENSIONS` /
`QOD_NODE_ALLOW_UNSIGNED_EXTENSIONS` / `QOD_NODE_AUTOINSTALL_KNOWN_EXTENSIONS` on
every pool. Verify the posture as a tenant user: `SELECT * FROM
read_text('/etc/passwd')`, `ATTACH ':memory:' AS x` and `SET
autoinstall_known_extensions=true` are refused (`QoD: ...`), and ordinary queries on
granted tables still work.
- **Pod security defaults** (K8s backend only, no flag, always on) - spawned
node pods get `runAsNonRoot: true`, `runAsUser`/`fsGroup` from
`k8s.runAsUser` (default 1000), `seccompProfile: RuntimeDefault` on the pod,
and `allowPrivilegeEscalation: false`, `capabilities: drop [ALL]`,
`readOnlyRootFilesystem: true` on the quack container, with writable
`emptyDir` mounts for `/tmp` and the DuckDB temp directory. A pod-template
override merges field-by-field rather than clobbering, so an operator
template can relax individual fields if a workload needs it.
- **NetworkPolicy** (helm, opt-in) - `--set networkPolicy.enabled=true`
(default false) renders `networkpolicy-nodes.yaml` (default-deny; ingress
from manager pods only on the node port range, egress to the manager pods
on `:20900` for each statement's authorization decision, to DNS, Postgres,
and the object store) and `networkpolicy-manager.yaml` (ingress on
`:20900`/`:31338` from `networkPolicy.ingressFrom`, plus `:20900` from the
node pods whatever `ingressFrom` says, egress to node pods, Postgres, DNS,
optional SMTP). The node-to-manager rule opens the whole REST port (a
network policy cannot filter by path; every route authenticates). The rules
match the release namespace only: with `QOD_K8S_NAMESPACE` set to another
namespace you need `namespaceSelector` rules the chart does not template. Tune `networkPolicy.postgres.cidr`,
`networkPolicy.objectStore.cidrs`, and `networkPolicy.nodePortRange` in the
chart values (`helm show values oci://ghcr.io/starlake-ai/charts/quack-on-demand`).
Sanity-check a rendered policy with
`helm template --set networkPolicy.enabled=true` before applying.
- **Catalog-reader idle eviction** - each cached `DuckLakeCatalogReader`
(one per browsed tenant-db, in `Main`'s `catalogReaderCache`) owns a small
HikariCP pool; without bounding, thousands of self-serve tenant-dbs would
each pin a pool forever. A daemon sweeper (process-local - every manager
replica sweeps its own cache, no HA coordination) runs every
`QOD_CATALOG_READER_SWEEP_MIN` minutes (config
`quack-on-demand.catalogReader.sweepIntervalMin`, default 10) and closes +
evicts any reader idle past `QOD_CATALOG_READER_IDLE_EVICT_MIN` minutes
(config `catalogReader.idleEvictMin`, default 30). Each reader's Hikari
pool also sets `minimumIdle=0` / `idleTimeout=60s`, so an idle-but-not-yet-
evicted reader already holds zero live Postgres connections. Delete/rotate
of a tenant-db still evicts immediately regardless of the sweep cadence.
To watch it fire without waiting 30 minutes, set
`QOD_CATALOG_READER_IDLE_EVICT_MIN=1`, browse a tenant-db's catalog, wait a
couple minutes, and grep the manager log for `reader-cache sweep: evicted`.
## Typical failure modes
| Symptom | Cause | Fix |
|---|---|---|
| `/api/*` returns 401 (the CLI adds "run qod login, or set QOD_TOKEN to a personal access token") | No valid credential on the call: none sent, an expired or revoked session or PAT, or a former static API key | `qod login`, or set `QOD_TOKEN` to a personal access token (`X-API-Key: <token>` on raw REST calls) |
| Manager boots with `WARNING: QOD_API_KEY (quack-on-demand.apiKey) was removed and is ignored`, and clients sending the key get 401 | `QOD_API_KEY` is set to a non-empty value (shell, container env, chart `env`, or `quack-on-demand.apiKey` in an operator config file); the manager no longer accepts a static API key | Unset it everywhere it is set. Mint a personal access token for automation (`qod login`, then `qod auth pat create`) and point scripts (`QOD_TOKEN`), MCP clients and SCIM connectors at it |
| `qod start` / `qod serve` (with or without `--demo`) print `warning: QOD_API_KEY is set` | The key is in your shell, or an earlier `qod setup` stored it in the `[start]` table of the CLI profile file; it is ignored and never passed to the manager | `unset QOD_API_KEY`, or `qod setup --unset QOD_API_KEY --non-interactive` (or delete the line from the `[start]` table of the file the message names) |
| Manager refuses to boot: `the FlightSQL edge and the Quack front door have no authenticator: ...` | `QOD_AUTH_DB_ENABLED=false` and no other `quack-flightsql.auth` provider: the FlightSQL edge and the Quack front door would refuse every client. System-scope OIDC (`QOD_MGMT_OIDC_*`) does not count: it signs in to the admin UI and REST only | `QOD_AUTH_DB_ENABLED=true`, or a Keycloak or Azure password backend (`QOD_AUTH_KEYCLOAK_ENABLED=true` / `QOD_AUTH_AZURE_ENABLED=true` with their settings), or a bearer provider (`JWT_SECRET_KEY` or `JWT_PUBLIC_KEY_PATH`, `QOD_AUTH_GOOGLE_ENABLED=true`, `QOD_AUTH_AWS_ENABLED=true`) |
| Manager refuses to boot: `nothing can sign in to this manager: ...`, naming `QOD_AUTH_DB_ENABLED` and `QOD_MGMT_IDENTITY_SOURCE` / `QOD_MGMT_OIDC_ISSUER_URL` / `QOD_MGMT_OIDC_CLIENT_ID` | FlightSQL has only a bearer provider, and there is no password backend (`QOD_AUTH_DB_ENABLED=false` and no Keycloak or Azure one) and no system-scope OIDC sign-in, which counts only with `QOD_MGMT_IDENTITY_SOURCE=oidc` and both the issuer and the client id set: nobody could ever log in | Turn database auth back on (`QOD_AUTH_DB_ENABLED=true`), or configure system-scope OIDC sign-in: `QOD_MGMT_IDENTITY_SOURCE=oidc` with `QOD_MGMT_OIDC_ISSUER_URL`, `QOD_MGMT_OIDC_CLIENT_ID` and `QOD_MGMT_OIDC_CLIENT_SECRET` |
| `helm install` / `helm upgrade` fails: `apiKey was removed: the manager no longer accepts a static API key; use a personal access token` | The chart values still carry a non-empty `apiKey` (an all-empty block left from an older values file passes) | Delete `apiKey` from the values (and the Secret it named); give automation a personal access token |
| `helm install` fails: `admin.password or admin.existingSecret is required: ...` | The chart needs an admin: without one nobody can log in | Set `admin.password`, or `admin.existingSecret` naming a Secret that holds the password under `admin.existingSecretKey` (default `adminPassword`) |
| `qod serve` prints `qod serve: create tenant` / `create database` / `create pool` `failed: <reason>`, then `finish by hand: <command>` | The manager refused that boot provisioning step, for the reason a REST create would give (a name, a quota, encryption); the outcome is also in `serve-provision.outcome.json` in qod's data dir, and the manager log has it at ERROR. Provisioning is add-only, so nothing existing changed, and the manager keeps running | Fix the cause the reason names, then run the printed command, or re-run `qod serve` |
| The reason says the database `already serves kind=... dataPath=...`, or that its views point at different data (`initSql differs`), or that the pool name already serves another database; the way out, `serve it under another name: ...`, ends the reason or follows it on its own line, and no `finish by hand:` line follows (a create would collide again) | The tenant already has a database (or pool) of that name for other data; `qod serve` never updates or replaces one | Serve the target under another name: `qod serve <target> --name <other>` (a pool: `--pool <other>`) |
| `qod serve: provisioning failed: the manager answers /ready but wrote no provisioning outcome within --ready-timeout (<N>s)`, then three `finish by hand:` lines; or `... could not read the provisioning file the launcher wrote` | The manager release does not provision at boot, or is not the one this CLI expects (a launcher/manager version mismatch), or it did not reach its boot provisioning in time | Run the manager release matching this CLI (drop an older `--jar` or `--version`), or `qod login` and run the printed commands; a slow first boot needs a larger `--ready-timeout`. The manager log says what it did |
| The boot banner ends `is up, with N problem(s): see PROBLEMS below` and lists `<tenant>/<db>: BLOCKED, pools ... NOT started`; `qod pool list` shows that database's pools with `"status": "blocked"` (the UI: `blocked: nodes not started`), and any node listed for them is a stale record of an earlier run | The database's DuckLake catalog recorded another data path than the control plane resolves for it. Usually two installations share one control plane with different data roots (a source checkout's launcher and `qod start`): a database that names no `dataPath` of its own derives it from the manager's `QOD_DUCKLAKE_DATA_PATH`, which differs between them. `statusDetail` and the banner give the reason and the fixes | Apply the `fix :` line: restart the manager with the `QOD_DUCKLAKE_DATA_PATH` it names, or pin the database with the `qod database update ... --metastore dataPath=...` command it prints, or give each installation its own control plane (`QOD_PG_DBNAME`). Pinning clears the block at once; an env change needs a restart |
| The banner lists `<tenant>/<db>: federation aliases missing`, e.g. `env var 'QOD_PG_PASSWORD' not set for secret 'PG_PASSWORD'` | One of the database's federation sources did not resolve, so its nodes start without any of its federation aliases (one bad source drops them all) | Fix the source, or set the secret it names (an `env:` secret is read from the manager's own environment), then restart the manager |
| `no node with role READONLY or DUAL` | All nodes flipped unhealthy (port unreachable) | Check `pgrep -fl spawn-quack-node`; if 0, run `qod stop` + `qod start` (reconcile respawns) |
| `QoD: user:<name> lacks grants on ...` naming the table (FlightSQL `UNAUTHORIZED`) | The user has no matching grant. The nodes always check grants; there is no switch to turn that off | Add the grant via `qod role permission grant` |
| The manager refuses to start naming `QOD_ACL_ENABLED` or `QOD_NODE_LOCKDOWN` set to `false`, or `QOD_ACL_FILTERED_METADATA` or `QOD_ACL_DIALECT` set at all (set to anything but `false`, the first two only print a warning at boot) | That setting was removed: the nodes enforce always, and listings are always filtered | Unset it (`qod setup --unset <NAME>` for a persisted one; remove it from the Helm values or env), and upgrade `qod` with the manager (an older `qod serve` exports `QOD_ACL_ENABLED`) |
| `QoD: statement uses a secure view while the session holds variables` / `... has changed the setting <name>` / `... search path differs from the one it was given` / `... holds temporary entries ...`, `which QoD does not support` | The session set a variable, changed a setting other than `schema` / `search_path` (a client's `SET TimeZone` on connect included), ran `USE` of another schema, or created a temporary view, macro or type: any of them could change what a secure view's body reads | Open a new session (or reconnect) without that state, and read the secure view there; set time zones and other settings in a session that reads no secure view |
| `QoD: function <name> is not classified` | A tenant user called a name the node's classification does not list: a typo, a function of an extension the node loaded later, a macro a superuser created, or a catalog macro named like a built-in (also reached through a view, a default or a built-in macro's body) | Check the spelling; call DuckDB's own with `system.main.<name>`; have a superuser drop or rename the shadowing macro. A new extension's functions need a manager release that classifies them |
| `QoD: authorization unavailable, retry` (FlightSQL `UNAVAILABLE`) | The node could not get a verdict from the manager replica that minted the session's handle in time (`QOD_AUTHZ_DECIDE_TIMEOUT_MS`, default 2000): that replica restarted, is down or unreachable from the nodes. A multi-statement submission so refused is not retryable as a whole (FlightSQL `INVALID_ARGUMENT`, "not retryable as a whole: the statements before the refused one may have run") | Retry a single statement. For a batch, check which of its statements ran before resubmitting. If it persists, check that the nodes reach `QOD_INTERNAL_URL` (boot prints `node decide URL: ...`), and on Kubernetes that the NetworkPolicy lets node pods reach the manager on 20900 |
| `QoD: authorization unavailable, retry: authorization context expired; retry` under load | The replica kept fewer session handle contexts than its statement rate needs (each lives 360 s; about `QOD_AUTHZ_HANDLE_CONTEXTS_MAX / 360` tenant statements per second per replica, about 555 at the default 200000). `cache_evictions_total{cache="qod_minted_handles"}` rises | Raise `QOD_AUTHZ_HANDLE_CONTEXTS_MAX` (at least 1000), or add replicas |
| `QoD: catalog <alias> is read-only` for a superuser | The federated source is read-only (`--read-only`), which binds every principal | Re-create the source with `--no-read-only` if writes are intended |
| `QoD: <url> is outside the tenant's authorized locations` / `QoD: <path> is a database data folder` | A tenant user named a file their own S3 credentials or their tenant's folders do not cover, or a database's data folder | `qod profile s3 add` for the user's own bucket or prefix, or `qod tenant location add` for a local folder (see "Files and S3 for tenant users"); a data folder is never reachable |
| Manager refuses to boot: `fleet mode requires QOD_INTERNAL_URL ...` or `the node decide URL is ..., a loopback address no node pod can reach on the kubernetes runtime ...` | Nodes call the manager back for every statement's verdict, at the URL each handle carries | Fleet: set `QOD_INTERNAL_URL` to this replica's own `http://` address on the servers' network. Kubernetes: the Helm chart sets `QOD_POD_IP`; a hand-written manifest sets `QOD_POD_IP` from the downward API (`status.podIP`) or `QOD_INTERNAL_URL` to the pod's own address |
| `400 init_sql_refused` on `database create` / `update`, `pool create` / `set-init-sql` | A tenant admin's initSql holds a statement outside the allowlist ("Who may write what in initSql") | Keep only allowlisted SETs, read-only PRAGMAs and extension INSTALL/LOADs, or have a superuser write it |
| An `INSERT` into an Iceberg table on S3 fails with an HTTP 403 at commit | DuckDB's Iceberg extension commits without connection secrets, and the nodes hold every registered S3 credential as one | Iceberg tables on S3 are read-only through QoD for every principal, superusers included, until upstream duckdb-iceberg signs its commits with connection secrets; write with the warehouse's own tooling |
| A FlightSQL call fails `UNAUTHENTICATED` with `No bearer auth providers configured` or `Bearer authentication failed: ...` | The client replayed a session bearer the manager no longer holds (it restarted between calls), or sent a token no configured provider accepts | Reconnect with username and password (Basic) |
| `Could not connect to server` for `http://127.0.0.1:21NNN/quack` | Quack child died after manager restart | Reconcile respawns on next boot; until then `qod pool delete` + `qod pool create` |
| A pool's nodes all go unhealthy and its statements fail (`the node refused the statement's session handle`, then `no node with role ...`) after `QOD_NODE_KEY_SECRET` was set or changed, with manager replicas that disagree on it, or once host clocks drift | The nodes refuse the manager's session handles. The manager log says why, once per node: `node <id> refuses the manager's session handles ... it holds a key derived from another node-key secret` (the nodes' keys come from another secret), or `... the manager's and the node's clocks disagree by about the handle lifetime (60 s) or more` | Give every replica the same `QOD_NODE_KEY_SECRET` (or unset it on all of them), then restart the nodes so they get keys from it: `qod node restart --tenant <t> --db <db> --pool <pool> --node-id <id>` per node, or scale the pool down and back up. For the clock message, sync the clocks (NTP) of the manager and node hosts |
| `DuckLake catalog '<db>': this DuckLake catalog was created unencrypted ... but the tenant-db row asks for encryption` (or the reverse) | The database row's `encrypted` flag disagrees with what the catalog recorded when it was created | Encryption is create-time only and the flag has no update path: create a new database with the encryption you want and copy the data. A row that is merely mislabelled has to be corrected in the control-plane database directly |
| Manager (or spawned node) hangs at startup right after `BaseAllocator` log line, java pegged at 100% CPU | `INSTALL quack` is blocked by a corporate proxy - DuckDB is silently retrying to fetch the extension from `extensions.duckdb.org` | Pass `HTTP_PROXY` / `HTTPS_PROXY` / `NO_PROXY` env vars to the process (container `-e` or shell env). See "Behind a corporate proxy" in the project README on GitHub. |
## Where state lives
- **Tenants, tenant-dbs, pools, nodes** - normalized `qodstate_tenant` / `qodstate_tenant_db` / `qodstate_pool` / `qodstate_node` tables (Liquibase-managed) in the dedicated control-plane Postgres database (`qod` by default, override with `QOD_PG_DBNAME`). The legacy single-JSONB-row `slkstate_pool_state` blob was dropped.
- **Users + RBAC** - `qodstate_user` (bcrypt-hashed passwords), plus `qodstate_role`, `qodstate_group`, `qodstate_role_permission`, `qodstate_user_role`, `qodstate_user_group`, `qodstate_group_role`, `qodstate_pool_permission`. The old `slkstate_user` and `slkstate_acl_grant` tables were dropped in the RBAC cutover.
- **Federation** - `qodstate_federated_source`, `qodstate_federated_secret`
- **DuckLake catalog metadata** - `ducklake_*` tables in each managed tenant-db's own Postgres database (`${tenant}_${suffix}`), separate from the control plane
- **DuckLake data files** - `defaultMetastore.dataPath` on disk (or s3://, gs://, az://)
- **Self-signed TLS cert** - `certs/server-{cert,key}.pem` under `qod start`'s state dir (the platform user-data dir), auto-generated on first boot if missing
- **Manager log** - `qod start` runs the manager in the foreground; its log is the terminal output
## When operating
- There is no built-in admin password: it is chosen at the first `qod start` / `qod serve` and the admin row is never rewritten on restart. Rotate with `qod auth change-password`; recover a lost or locked password with `qod admin reset-password`, and a disabled superuser with `qod admin reset-password --username <name>`. With the default `QOD_ADMIN_USERNAME` (`admin@localhost.local,admin`) both superusers share that first-boot password: rotate each one (`qod auth change-password --username <name>` per name); `qod admin reset-password` resets every configured admin name by default (`--username` targets one).
- There is no static API key: people log in (`qod login`, the UI) and automation uses personal access tokens (`qod auth pat create`). Upgrading from a release that had one: mint the PATs first, point MCP clients and SCIM connectors at them, then remove `QOD_API_KEY` from every environment, chart value and Compose file (a set key is ignored, with a warning at boot).
- `QOD_SESSION_JWT_SECRET` is optional: when it is unset, the first boot generates the session secret and stores it in the control-plane database, so sessions survive restarts and every HA replica shares it (boot prints only where it came from, never the value). Set it only when another service must verify the sessions, as the Starflow pairing does. To rotate the stored one, run `DELETE FROM qodstate_boot_secret WHERE name = 'session_jwt'` against the control-plane database and restart the manager (every replica): every session and password-reset link ends. The stored secret makes a read-only copy of the control-plane database (a `pg_dump` backup, a snapshot, a read replica, a SELECT-only role) able to forge a superuser session, on top of the credentials it already holds (among them tenant catalog passwords, object-store keys, inline federation secrets, node bearer tokens, tenant OIDC client secrets and OPA tokens): protect those copies like the secret, or, when they are less trusted than the manager, pin `QOD_SESSION_JWT_SECRET` from an external secret store so nothing is persisted.
- All config scalars have matching `QOD_*` env-var overrides. Prefer env vars over editing `application.conf` (it is bundled into the jar at build time).
## Common UI URLs
Pages are scoped by tenant in the path: `/ui/t/<tenant>/...` for one tenant, `/ui/all/...` for every tenant (superusers only; tenant admins are kept within the tenants they administer). The sidebar's tenant switcher changes the scope.
- `http://localhost:20900/ui/` → Nodes dashboard (landing page; redirects to `/ui/all/dashboard` or `/ui/t/<tenant>/dashboard`)
- `http://localhost:20900/ui/tenants` → Tenants list
- `http://localhost:20900/ui/t/<tenant>/databases` → tenant databases
- `http://localhost:20900/ui/t/<tenant>/pools` → tenant pools
- `http://localhost:20900/ui/t/<tenant>/pools/<db>/<pool>` → per-pool nodes + JDBC URLs
- `http://localhost:20900/ui/t/<tenant>/catalog` → catalog browser
- `http://localhost:20900/ui/t/<tenant>/access-control` → tenant ACL settings (users, groups and roles under `/users`, `/groups`, `/roles`)
- `http://localhost:20900/ui/t/<tenant>/audit/statements` → statement history (`/ui/all/audit/statements` across tenants)