---
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"). As a tenant admin, naming a local
target there is refused (`403 superuser_required`). 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
```

The demo can keep its data on S3: `qod serve --demo --s3` runs a throwaway local store, and
`qod serve --demo --s3-path s3://bucket/prefix` uses your bucket (a long-lived key pair from
`--access-key-id` / `--secret-access-key` or `AWS_*`, both halves required; `--endpoint` for an
S3-compatible store). Prefer `AWS_ACCESS_KEY_ID` / `AWS_SECRET_ACCESS_KEY` over
`--secret-access-key`, which other users of the machine can see in the process list. Without
`--s3-path` the object-store flags are refused, and `--s3` is refused at
once on a platform with no SeaweedFS build (Windows on ARM). With a bucket the demo listens on
`127.0.0.1` only and deletes its `qod-demo-<id>` folder on exit, so use a dedicated bucket and key.
`AWS_SESSION_TOKEN` set with `AWS_*` keys is refused: temporary credentials are not supported. On
Windows, Ctrl-C stops the demo before it can delete that folder: remove it by hand. A `qod stop` from
another terminal may stop the demo before the delete finishes (it waits 10 s, where the demo's own
Ctrl-C waits up to 90 s): run it as `FORCE_AFTER=90 qod stop`, or stop the demo with Ctrl-C.

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`, its own PATs and the branch routes (see "Branches") - 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 (Postgres connection keys are left out: they resolve
# from the manager's defaultMetastore at spawn time, and only a superuser sets them).
# A new tenant's name holds letters and digits only and starts with a letter (400 invalid_id
# otherwise, for a superuser too): its databases are named <tenant>_<database>, and this keeps two
# tenants from ever composing the same database name. A tenant that already holds an underscore,
# or one a manifest import brings, keeps its name.
qod tenant create acme
qod database create --tenant acme --name 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").
#
# Where its data lies: leave --data-path out of a DuckLake database and QoD places it in a folder of
# its own beside the default data path, stored on the row (409 data_path_taken over a folder another
# database holds: choose another name; 400 with no default data path, or one that is a bucket's root:
# use --managed-storage, or ask a superuser). A tenant admin's own path is a folder of a remote store
# (s3://, s3a://, r2://, gs://, az://, azure://, abfss://), not its root, with their own keys in
# --object-store, complete for its scheme; at, under or above a folder another database holds it answers
# 409 data_path_taken, naming no one, for ten minutes after that database is dropped too. Any other path,
# on any kind, is a superuser's to name: 403 superuser_required, nothing written. --object-store goes
# only with such a remote path, as its own keys.
#
# Only a superuser names a Postgres server: pgHost, pgPort, pgUser, pgPassword and a DuckLake
# database's dbName answer anyone else 403 superuser_required, nothing written (the dbName of a
# duckdb-file database names a catalog in its file and stays free). A superuser may put a database on
# another server, with a login of its own: QoD's login goes only to QoD's server, so a metastore whose
# pgHost or pgPort differ from the default's, compared as text after normalizing (127.0.0.1 is not
# localhost), answers 400 own_login_required without its own pgUser and pgPassword (leave both pgHost
# and pgPort out to use QoD's server). Its catalog is its owner's: QoD creates and drops no database
# there, so create one named <tenant>_<name> first (400 catalog_database_missing names it); database
# delete and a tenant delete leave it, logged at ERROR; it cannot be branched (400 branch_own_server).
# A database's Postgres server is trusted as far as its nodes are: whoever controls it reaches what
# those nodes reach. A superuser who names one accepts that.
qod database create --tenant acme --name byo \
  --metastore pgHost=pg.acme.example --metastore pgPort=5432 \
  --metastore pgUser=acme --metastore pgPassword=...
# On QoD's own server, a tenant admin's create makes the database, reuses an empty one, and answers
# 409 database_name_taken for one holding anything, touching nothing (choose another name). A
# superuser's create, qod serve's provisioning and a manifest import take over what is there.

# 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 a superuser's to send: 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. A local path is a superuser's to name.
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 a superuser sends 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, a superuser's to do (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
# Anyone but a superuser gets 403 superuser_required when the result would add, change or remove any
# of pgHost, pgPort, pgUser, pgPassword or a DuckLake database's dbName, on any database: send them back
# as the row holds them, with the password left out, and change the rest. A password sent counts as a
# change whatever it says. catalogAlias and schemaName name things inside the database's own catalog
# and stay free.
# A superuser's update answers 400 own_login_required when the result names another server with no
# login of its own, and when pgHost or pgPort change with the stored password left out: the password
# does not follow the row, so send it again (or send it empty to go back to QoD's server, where QoD's
# login fills in).

# 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, and its nodes hold no secret of the manager's
`QOD_S3_*` / `QOD_AZURE_*` keys. 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,
scoped to that database's data path and its branches: exact back-compat for
every deployment that only ever used one set of keys. A
path that falls back on them is a superuser's to name. A tenant admin sends `objectStore`
on a create only with a remote path it completes, and changes it only on a database holding
complete keys of its own, which stay complete, sending the scheme's secret with any change: an
endpoint, a region or a key id alone answers `403 superuser_required`. Every other database's,
one QoD placed or of managed storage included, is a superuser's to add, change or clear (`403
superuser_required`); an edit leaving it as stored still works.
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:
  a superuser runs `database/update` on each managed database's `objectStore`
  with the new secret (a tenant admin cannot change it), then recycles its pools.

```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.

### 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:

- **A node reads whatever files the catalog lists, local files included.** The
  manager prints a `WARN:` line on its stdout when such a source is created or
  changed and at each boot. In production disable local file access on its nodes
  (a superuser's init SQL: `SET disabled_filesystems = 'LocalFileSystem'`, on an
  object-store pool, where a sort past `memory_limit` then fails instead of spilling).
- **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.
- Once a source holds a secret, a tenant admin changes neither its connection
  nor its config: `403 superuser_required`, nothing written. A superuser changes
  it, or delete the source and create it again with its secrets. The same values
  sent again are no change, and `--description`, `--disabled` and `--read-only`
  stay open.
- `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.
On Kubernetes a branch's node id is 36 characters plus the tenant's and the parent
database's names, and it must fit a Service name and a label value, 63 characters
each, so a create past that answers `502 branch_create_failed` (`waynecorp` and
`waynecorp_sales_2026` make 65); a merge node's id holds neither name and stays
within 35 characters, so `propose` and `merge` have no such limit.

```bash
# 1. Create a branch of acme's tpch1 database (expires after 24h; omit for the server default).
#    Anyone who may connect to one of the database's pools may branch it, in their own tenant. An
#    admin may ask for --ttl-hours 0 (never); anyone else asks for 1 up to the server's default.
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,
#    table conflicts against main; then the row-level diff of one table. Row conflicts come with the
#    proposal: `mergeable` here does not read them.
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: the branch's owner, or an admin (403 not_owner otherwise). Records the change set and
#    what a merge would do to its rows now: rows to update,
#    delete and insert, conflicts per column, sample rowids. Never a cell value: query the branch and
#    main by rowid to see them. Proposing again replaces an open proposal. `merge.id` in the reply is
#    the proposal's id; in `qod branch show` it is the `id` of the entry with status `proposed` under
#    `merges`. The approver merges by it.
#    --on-conflict main|branch: who wins every row conflict: a cell, a row one side deleted and the
#    other updated, rows matching a --key (under branch, main's matching rows are deleted). Without
#    it, a conflict refuses the merge.
#    --key schema.table=col[,col...]: rows both sides inserted with this key become conflicts.
qod branch propose --tenant acme --db acme_tpch1 --branch feature-x

# 5. Merge: an admin only (403 admin_required), and a DIFFERENT principal than the proposer
#    (403 self_merge_forbidden otherwise).
#    Every table the branch modified merges cell by cell, with main's side empty when only the branch
#    changed it. 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 show --tenant acme --db acme_tpch1 --branch feature-x
qod --profile reviewer branch merge --tenant acme --db acme_tpch1 --branch feature-x --proposal <id>

# Or discard (owner or a tenant admin). Refused while a propose or a merge of the branch runs (409
# branch_busy), and when a merge of it landed on main and is not on record (409 proposal_unsettled):
# merging it again records it, which needs an admin. Expired branches are discarded by the manager,
# which skips a branch in either state. One whose merge landed stays, with an ERROR in the manager's
# log at every sweep, and holds back snapshot expiry on its parent until someone acts.
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. Plain users work from
the CLI and MCP.

A plain user who may connect to no pool of the database gets `403
no_pool_access` from every branch command, and `403 tenant_forbidden` on any
tenant but their own. What they read follows their grants. `branch changes`, `branch show`
and a proposal's reply name only the tables they hold a read grant on (`RO`,
`RW` or `ALL`, the tables their `SHOW TABLES` lists; a `DDL` grant alone shows
nothing, even of a table they altered on their own branch), and
`otherTables` counts the rest; a diff of any other table answers as for no such
table, and a key on one as for a table not in the change set. They see a table's
row numbers only when their own count of it succeeds, and the count of a user
holding a column or row policy never does: such a user gets no `rowConflicts` at
all. An OPA tenant's users see what their QoD grants cover for reading, as with
their `SHOW TABLES`, since OPA does not narrow those grants: nothing with no such
grant, and every name on the default built-ins.
`mergeable` is always the whole branch's, and a refusal's text can still name
another table of it. An admin sees every table.

Agents over MCP get the same lifecycle, with a plain user's token as with an admin's (a token minted with `--drop-admin` acts as the plain user it is): `create_branch`,
`list_branches`, `branch_changes`, `diff`, `propose_merge`, `discard`, and a
`branch` argument on `run_sql`, `list_tables` and `describe_table` (the last
two read the catalog, which takes an admin; a plain user's token reads it with
`run_sql`). There is deliberately no merge tool: a merge needs an admin, over
REST or the CLI. 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.

An open proposal's `error` starting `timeout:` means a merge is running or may have landed. Merging again settles it; if it landed on a branch that has moved since, that removes what was written on the branch since. A retry that reads `409 already_merged` found the first run landed and on record: that merge is done.

After a manager restart mid-merge, merge again. If that does not settle it, the branch's pool is still frozen (disabled and suspended): `qod branch show` gives its `catalogDb` and `pool`, then `qod pool set-disabled --tenant acme --db <catalogDb> --pool <pool> --enabled` and `qod pool resume --tenant acme --db <catalogDb> --pool <pool>` (the pool belongs to the branch's own database, not the parent).

A crash can also leave the merge node running. It has no node row, so nothing reaps it: it keeps its port, its memory (many GB at scale) and a transaction open on the parent's catalog, which holds back VACUUM there and can make a catalog migration's DDL time out. Sweep it:

- Local runtime: `qod stop`, then `qod start` (`qod serve <target>` for a `qod serve` manager). This stops the serving nodes too. On Windows `qod stop` exits 1 and names the way to stop.
- Kubernetes: delete the `merge-*` pod and Service and the `qod-token-merge-*` and `qod-store-merge-*` Secrets. Two per-pool Secrets, which hold the catalog password and cloud keys, outlive them until a later merge or proposal on that database stops its node with none left. When no merge of that database is running, delete `qod-nodeenv-<tenant>-<db>---merge` and `qod-fedsql-<tenant>-<db>---merge` too (their names write `_` as `-`, so the pool `__merge` reads `--merge`).
- Fleet: find the server holding the `merge-*` node in `qod fleet servers`, then `qod fleet drain <name>` and `qod fleet undrain <name>`. With `QOD_FLEET_EPHEMERAL=local` the node runs on the manager host, `qod fleet servers` lists no such server, and the local sweep applies.

`qod branch propose` and `merge` have no read limit: they wait for the server, which bounds each stage. If a NAT or load balancer drops the connection during a long merge, the CLI waits until Ctrl-C; merging again is safe: it waits behind a run still going, and `409 already_merged` then means that run landed.

What refuses a merge, and what to do:

| Refusal | Meaning | Next |
|---|---|---|
| `409 merge_conflict` | A table conflict, or row conflicts with no `--on-conflict` | Row conflicts: propose again with `--on-conflict`. A table conflict: no policy overrides it; redo the change on a fresh branch |
| `409 conflicts_changed` | Main moved: a conflict, or a delete from main, the proposal did not show | Propose again |
| `409 proposal_stale` | The branch changed since the proposal | Propose again |
| `409 proposal_replaced` | Another proposal replaced the one you read | Read the new one, then merge it by its id |
| `409 already_merged` | The proposal you named has merged; the refusal names the main snapshot and the tag. What a retry reads when the first run landed, and what a second approver reads | Nothing: the merge is done, whatever became of the first answer. `qod branch merge` exits 1 with `409 already_merged` on stderr, so a script retrying a merge treats that code as done |
| `409 not_proposed` | The branch has no open proposal: a refusal that closes one (`merge_conflict`, `conflicts_changed`, `proposal_stale`, `history_gap`, and `merge_failed` when the node failed) leaves none | Propose again, unless the closing refusal's row says otherwise |
| `409 proposal_unsettled` | On propose or merge: a merge may have landed on main. On discard: a merge of the branch landed on main and is not on record | Merge again, which settles it and needs an admin other than its proposer, or, when it says another proposal landed, discard the branch |
| `409 branch_busy` | On discard: a propose or a merge of the branch is running | Try again when it ends |
| `409 branch_expired` | On propose, whoever asks: the branch passed its TTL and the next sweep discards it. An open proposal stays as it is | Create a new branch. An admin other than its proposer can still merge an open proposal until the sweep runs |
| `502 catalog_unavailable` | On discard: main's catalog cannot be read to look for a merge that landed, so nothing changed | Try again |
| `502 merge_failed` | The merge node failed, or a stage timed out | After a timeout or a manager restart mid-merge, merge again and never propose first (`409 already_merged` on that retry means the first run landed); raise `QOD_BRANCH_MERGE_TIMEOUT_SEC` if a stage needs longer. Otherwise fix the cause and propose again; for "the merge node needs more memory" that is the node's memory limit (see Size below) |
| `409 concurrent_write` | Main kept moving across `QOD_BRANCH_MERGE_MAX_ATTEMPTS` attempts, or is not at the snapshot `--expect-main` names | Merge again, with a current `--expect-main` or none, or `--hold-writers` if the writers are on the merged tables |
| `409 history_gap` | A snapshot from the fork on is gone, on main or on the branch: expired by hand, by a maintenance run that started just before the branch was created, or, on a branch an earlier release created, by maintenance before the expiry floor existed | Discard the branch and create it again |
| `422 merge_unsupported` | A schema change on a merged table, views, macros, a column named `rowid` or `snapshot_id`, or a snapshot that mixes maintenance and data | Use a fresh branch for data changes; apply DDL on main; rename a reserved column |
| `422 nothing_to_merge` | No changes, or every change is already on main or loses to main under the proposal's policy | Discard the branch |
| `403 branch_only_token` | The token was minted with `--branch-only`, and a merge writes main | Merge with a session, or a token without that restriction |

A merge needs a quiet moment on each table it changes: DuckLake refuses a commit if another commit
touched that table since the merge's transaction began, whichever rows it touched. Commits on other
tables are fine. The merge retries by itself. On a table that commits too often, `qod branch merge
--hold-writers` refuses other commits on the merged tables until the merge ends. Writers retry for
about 9 seconds (only a superuser can raise `ducklake_max_retry_count`; other writers retry themselves),
then fail with "table is being merged". A writer whose transaction was open when the merge committed
fails once and must redo its transaction. The hold ends by itself after `QOD_BRANCH_MERGE_TIMEOUT_SEC`,
even if the manager dies. It is sized by that timeout, not by the expected merge, so raising the timeout
for a slow Apply also lengthens the worst-case hold after a manager's death.

Rows are matched by DuckDB's `rowid`, so no primary key is needed. Rowids survive updates, compaction
and moves between partitions. A pipeline that rewrites rows by delete and re-insert gives them new
rowids: a branch's edit of such a row shows up as a conflict, never as wrong data. Rows both sides
insert both land unless `--key` says what makes them the same row.

Size: a merge reads about 15MB per side at 1B rows plus the changed rows, and original values only for
rows both sides changed, at about 4MB per such row, up to one read of the table. Measured at 1B rows and
1M changes per side: 10 s and 32GB read on local disk, 3 s of it writing. At 100M changes per side, 187 s.
The merge node's memory runs well above its limit (33GB at a 16GB limit) and it spills up to 37GB at
that size, so bound its memory and check scratch disk before merging at that scale. QoD sets no CPU or
memory limit on a merge node, a Kubernetes merge pod included, and a pool's `--memory` does not reach it.
With no `memory_limit` set it runs at DuckDB's default, about 80% of the host's RAM (85% of a cgroup
limit when its container has one). It takes the init SQL of the database and of one of the database's
pools (the manager picks which), the pool's after the database's, so a `memory_limit` in either bounds it
and the pool's own wins; with no pool on the database, no init SQL reaches it. For example, this replaces
the database's init SQL and restarts its nodes:
`qod database update --tenant acme --name acme_tpch --init-sql "SET memory_limit = '16GB';"`.
It applies to every node of the database and wins over each pool's `--memory`: keep it within the
smallest pool's, or restore the text after the merge (that restarts them again).
At 1B rows and 100M changes per side, 16GB is the lowest limit that passed. Below it a propose or a merge
is refused with "the merge node needs more memory", unpredictably (a merge can fail where its propose
passed, and 12GB can fail where 8GB passed), so leave a margin rather than search for the floor.
An update of up to `QOD_BRANCH_MERGE_UPDATE_CHUNK_ROWS` rows (50M) goes as one UPDATE, a larger one as
rowid ranges, which cost far more on object storage: at that size and 16GB one UPDATE of 110M rows read 2GB
in 4,253 reads where 64 ranges read 52GB in 116,756, took 91 s of Apply against 156 s, and spilled 45GB
against 33GB. But whether one UPDATE fits does not follow its size: at 1B rows and 16GB it passed at 44M and
69M rows, ran out of memory at 92M, passed at 110M and ran out of memory at about 220M. A failure costs a
retry with ranges, which the merge makes by itself: at 92M it took 299 s against 199 s straight to
ranges. So raising the setting is a bet on that retry.
Compact a table in very many small files before creating the branch, because maintenance skips
compaction while the database has a live branch: on object storage those reads and writes are the cost.

Safety rules the manager enforces: every snapshot since the oldest live branch's fork is kept on the
parent, branch catalogs are never maintained, and `database delete` refuses a database with live
branches (`409`). A large `snapshotsSkippedPinned` figure in a maintenance run (the UI's "Pinned
skips") usually means an old branch: a branch with no TTL stops expiry above its fork until it is merged
or discarded. 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,
per call to the merge node, and a stage makes several), `QOD_BRANCH_NODE_READY_TIMEOUT_SEC` (120, how
long to wait for the merge node to accept connections: the `merge_failed` text "did not accept
connections within 120s" is this one), `QOD_BRANCH_MERGE_MAX_ATTEMPTS` (5) and
`QOD_BRANCH_MERGE_UPDATE_CHUNK_ROWS` (50000000, rows one UPDATE may carry per table: above that, or after
an out-of-memory failure, the update splits into rowid ranges inside the same transaction).
`qod branch create --from-snapshot` is reserved (forks at head only).

## 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>:20901`
(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 node
verdict listener on (the REST port + 1 by default, `QOD_INTERNAL_PORT`; boot refuses the REST port),
plain `http://`, on the private network. Let only the servers reach that port, since a client that
reaches it can hold its connection slots and stop authorization; name the manager's own IP address
in the URL and the listener binds only that address. 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:20901`
  from containers on the same host, with port 20901 published), 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, for a user with no
  read-covering QoD grant, these answers are empty and SUMMARIZE is refused, since the listings follow
  QoD grants, which OPA does not narrow. 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 `:20901` for each statement's authorization decision, to DNS, Postgres,
  and the object store) and `networkpolicy-manager.yaml` (ingress on
  `:20900`/`:31338`/`:9494` from `networkPolicy.ingressFrom`, plus `:20901` from the
  node pods whatever `ingressFrom` says, egress to node pods, Postgres, DNS,
  optional SMTP). The node-to-manager rule opens the node verdict listener
  only, where every call authenticates with the node's key. With no policy any
  pod can reach that listener and hold its connection slots, which stops
  authorization for every tenant: enable the policy, or open `:20901` to the node
  pods alone in a policy of your own. 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, or a client that reaches the verdict listener's port is holding its connection slots (it reads a call's body before it can check the call's signature). 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 20901. Only the nodes should reach that port (with no NetworkPolicy any pod can) |
| `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 ...`, `QOD_INTERNAL_URL ... names port N, the manager's REST port ...` 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, on the node verdict listener's port (the REST port + 1 by default, `QOD_INTERNAL_PORT`) | Fleet: set `QOD_INTERNAL_URL` to this replica's own `http://` address on the servers' network, naming the verdict listener's port. 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 |
| Manager refuses to boot: `the node verdict listener's port N (QOD_INTERNAL_PORT, ...; by default the REST port + 1) is the REST port` (or `the FlightSQL port`, `the Quack port`, `in the node port range ...`), or `the REST port N leaves no port for the node verdict listener` | Nodes call the manager back for verdicts on a listener of its own, on the REST port + 1 unless `QOD_INTERNAL_PORT` names another port, which must differ from the REST, FlightSQL and Quack ports and lie outside the node port range (`QOD_MIN_PORT` to `QOD_MAX_PORT`) | Set `QOD_INTERNAL_PORT` to a free port outside those |
| Boot stops with `the node verdict listener could not bind <host>:<port> (QOD_INTERNAL_PORT): ...`, after the nodes started | Another process holds the verdict listener's port: the REST port + 1 by default, so two managers on one host on consecutive REST ports clash. The bind comes after the nodes were spawned, and a boot that fails there leaves them running | Stop the holder, or set `QOD_INTERNAL_PORT` on one of the managers to a free port. On the local runtime run `qod stop` first, so the nodes of the failed boot do not hold their ports |
| REST answers `413 payload_too_large` or `408 request_timeout` | The body is over `QOD_REST_MAX_PUBLIC_BODY_BYTES` (public routes, 64 KiB), `QOD_REST_MAX_MODULE_PUBLIC_BODY_BYTES` (a module's public routes, 1 MiB) or `QOD_REST_MAX_BODY_BYTES` (16 MiB), or did not arrive within `QOD_REST_BODY_TIMEOUT_SEC` (30 s) | Send less, or raise the setting |
| REST answers `400 json_too_deep` or `411 length_required` | The JSON body nests past 256 levels, or a public route got chunked framing | Flatten the body, or send a `Content-Length` |
| A browser or client gets `431 headers_too_large` (REST), or 431 from the Quack front door | The request line or the headers are over `QOD_REST_MAX_HEADER_BYTES` (8 KiB) or `QOD_QUACK_MAX_HEADER_BYTES` (4 KiB): usually many cookies on the manager's host or parent domain | Clear the cookies, or raise the setting. Fewer connections then fit the heap |
| A FlightSQL call fails `RESOURCE_EXHAUSTED` ("gRPC message exceeds maximum size 16777216: ..."), or a client sending a very large header list is disconnected | The message is over `PROXY_MAX_INBOUND_MESSAGE_BYTES` (16 MiB), or the header list over `PROXY_MAX_HEADER_BYTES` (64 KiB). Both bind every client, with or without credentials | Send less, or raise the setting |
| A FlightSQL call fails `UNAUTHENTICATED` with "credentials required" or "no pool bound to session; authenticate first", on a client that worked before 0.12.0 | A call with no credentials reaches only `GetSqlInfo` and `GetXdbcTypeInfo`. Other metadata calls, such as `GetTableTypes`, and a `Handshake` with no credentials used to answer | Send credentials on every call |
| A FlightSQL call fails `UNAVAILABLE` "too many calls with no credentials; retry" | More than `PROXY_MAX_ANONYMOUS_CALLS` (256) calls with no credentials at once, usually a flood | Send credentials on the call, which then takes no place. Retry |
| A FlightSQL update fails `UNAVAILABLE` "too many concurrent updates; retry", or `TIMED_OUT` "no data from the client within 30000 ms" | At most a quarter of `PROXY_FLIGHT_THREADS` (128) DoPuts wait for their data at once, each for 30 s. Arrow Java's literal `FlightSqlClient.executeUpdate` reads its answer before it half-closes, so it always gets `TIMED_OUT` | Retry, or raise `PROXY_FLIGHT_THREADS`. Use JDBC or ADBC for updates: they half-close at once |
| New FlightSQL connections fail at once with `UNAVAILABLE` (JDBC's `getConnection` throws), while clients already connected go on | Too many connections have not yet shown a working credential (at most `PROXY_MAX_UNAUTHENTICATED_CONNECTIONS`, and what fits a tenth of the heap: 76 with TLS and 44 without at 2 GiB, 38 and 22 at 1 GiB), or more calls wait for a thread than `PROXY_FLIGHT_THREADS` (512). Usual causes: a flood of connections or of wrong passwords, a tenant's identity provider that does not answer, many clients reconnecting at once, or more calls at once than threads | Retry. Look for the flood at the network and for a tenant whose identity provider hangs. For real load raise `PROXY_FLIGHT_THREADS` or the heap (`JAVA_OPTS=-Xmx4g`) |
| A login fails after up to 5 s with `UNAVAILABLE` (REST `503 auth_unavailable`), "the password could not be checked right now; retry" | The password was not judged: either clients retrying a wrong password keep the password checks busy, as a login gives up after waiting 5 s for a database connection, or that database is down. The manager log has the cause. Statements on connections already open are not affected | Retry. Find the client sending the wrong password and fix or stop it, or restore the database |
| Power BI connects but its Navigator shows no catalog, while DBeaver lists the tables | The pool serves a database of `kind=memory`. The FlightSQL catalog list leaves out the `memory` catalog, and Power BI starts from that list | Use a pool on a DuckLake or duckdb-file database |
| A FlightSQL client waits up to 60 s on a new connection, then reads `UNAVAILABLE` where it expected `UNAUTHENTICATED` | Its first statement is over about 80 KiB and its credential is missing or fails: a connection that has shown no working credential is read only up to `PROXY_MAX_HEADER_BYTES` plus 16 KiB, then waits for one | Fix the credential. A working one is not affected |
| A FlightSQL client is disconnected after 16 calls or 60 s, and reconnects on its next call | A connection that has shown no working credential is closed past these. A proxy opening more than 8 streams on a new upstream connection, before it reads the server's SETTINGS, can lose it the same way | Send a working credential, or limit the proxy's first burst |
| Boot prints `The FlightSQL edge takes at most N of the M connections of PROXY_MAX_UNAUTHENTICATED_CONNECTIONS ...` | The variable was set above what fits a tenth of the heap | Raise the heap (`JAVA_OPTS=-Xmx4g`), lower `PROXY_MAX_HEADER_BYTES`, or unset the variable |
| Boot prints `WARNING: Netty shares the JDK's direct memory ...` | Netty could not take a direct memory budget of its own, so a flood of FlightSQL calls can halt the manager: the JVM runs without `--add-opens=java.base/java.nio=ALL-UNNAMED` (a launch with `-cp` instead of `java -jar`, whose manifest carries it), or `io.netty.tryReflectionSetAccessible=false` or `io.netty.maxDirectMemory=0` is set | Start with `java -jar`, or add the `--add-opens` flag, and unset those two properties |
| Boot prints `The REST port takes at most N of the M connections of QOD_REST_MAX_CONNECTIONS ...` (likewise `The Quack front door` with `QOD_QUACK_MAX_CONNECTIONS`, `The node verdict listener` with `QOD_INTERNAL_MAX_CONNECTIONS`) | A listener takes no more connections than fit a fifth of the heap, since what they hold before any credential must fit it | Raise the heap (`JAVA_OPTS=-Xmx2g`, or `-Xmx` on a `java -jar` command) to take more. For the REST port and the front door, lowering their header and body limits takes more too; the verdict listener's limits are fixed, so only the heap does |
| `/metrics` answers `503` | A scrape took longer than 10 s to come back, and this request stopped waiting for it | Retry. A scrape that keeps failing to return points at the registry, so read the manager's log |
| An SSO login or OIDC start fails while an identity provider is slow, or `authorization unavailable` for an OPA tenant under load | At most 128 identity provider exchanges (256 for OPA) run at once, and the rest fail at once. On Linux each exchange also leaves a TIME_WAIT socket on the manager's host, so sustained exchanges to one identity provider or OPA server above about 470 a second run out of local ports | Retry. Look for an issuer that hangs, or a flood of anonymous starts, and limit them in front of the manager. For the local ports, set `net.ipv4.tcp_tw_reuse=1` on the manager's host or container |
| An SSO sign-in fails with `discovery_failed` | The issuer's discovery document could not be read (unreachable, over 256 KiB, or no answer within 15 s), or was refused: its `jwks_uri`, `token_endpoint` or `authorization_endpoint` is not an absolute http(s) URL, or one of its fields is over 2048 characters. A failure is remembered for 10 s | Check the issuer URL and what it serves, then retry after 10 s |
| Boot prints `WARNING: this JDK has no HttpClient.shutdownNow ...` | The manager runs on a JDK older than 21, which cannot close the connection of an identity provider's or OPA server's refused answer | Run the manager on JDK 21 or later |
| `400 invalid_id` on `tenant create` | The name holds something other than letters and digits, an underscore included, or does not start with a letter | Choose a name of letters and digits; a tenant that already holds an underscore keeps it, and a manifest import brings any name |
| `409 database_name_taken` on `database create` or `update` | The Postgres database `<tenant>_<name>` on QoD's server already holds something, and the caller is not a superuser | Choose another name, or have a superuser create it if the database is really this one's |
| `400 own_login_required` on `database create` or `update` | The metastore names another server than QoD's own (host and port, compared as text) without its own `pgUser` and `pgPassword`, or an update moves the row with its stored password left out | Send the login for that server, or leave `pgHost` and `pgPort` out to use QoD's |
| `400 catalog_database_missing` on `database create` | The database names another server, where QoD creates no database, and its catalog database is not there | Create the database the message names on that server, then create again |
| `403 superuser_required` on `database create` or `update`, naming the data path | A tenant admin named a local path, a remote one without complete keys of their own, or `metastore.dataPath`, or an update removes such keys | Leave `--data-path` out and QoD places it, or name a remote path with `--object-store` keys for its scheme; any other path takes a superuser |
| `403 superuser_required` on `database create` or `update`, naming `objectStore` | A create sent it with no remote path it completes, the database holds no complete keys of its own (one QoD placed, of managed storage, or a value that was never complete), or a change to its own keys came without the secret | Leave `--object-store` out, on a create send it with a remote `--data-path` and every key of its scheme, or on a change of a database's own keys send its secret too; a superuser may set anything else |
| `409 data_path_taken` on `database create` | The folder QoD would place the database in, or the remote path a tenant admin named, overlaps one another database holds, held within the last ten minutes, or a managed prefix within retention | Choose another name, or another path |
| `400 invalid_contract` naming no default data path on `database create` | No path named, and the deployment has no default data path to place it by, or only a bucket's root | Use `--managed-storage`, or ask a superuser to name a path |
| `403 superuser_required` on `database create` or `update`, naming `pgHost`, `pgPort`, `pgUser`, `pgPassword` or `dbName` | Only a superuser sets a database's Postgres server, and the request would add, change or remove one of these | Leave them out of a create; on an update, send them back as the database holds them, with the password left out; or ask a superuser. A database's own password is rotated by a superuser |
| `403 superuser_required` on `GET /api/database/metastore-defaults` or MCP `metastore_defaults` | They tell QoD's own Postgres address and user, for a superuser's create form | Nothing to do: anyone else's create leaves these keys out |
| A database on another server stopped connecting after an upgrade, with no error before | Its row names another server and has no login of its own: QoD's login is no longer filled in there | A superuser's `qod database update ... --metastore pgUser=... --metastore pgPassword=...` with the rest of the map |
| `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)