MCP server: AI agents querying DuckDB under RBAC
The manager embeds an MCP (Model Context Protocol) server at POST /mcp on the REST port (default :20900), so AI agents such as Claude Code, Claude Desktop, or Cursor can discover schemas, run SQL with full RBAC/RLS/CLS enforcement, use DuckLake time travel, and (for admin credentials) operate pools and nodes.
The transport is stateless Streamable HTTP: each POST carries one JSON-RPC message and the response is plain JSON. There is no SSE, no server push, and no session id, so any HA replica answers any request. GET /mcp returns 405.
MCP gives an agent tools on the manager; the operator skill gives it the operational playbook for the qod CLI. They combine well.
Authentication
/mcp accepts exactly one credential: a personal access token (qod_pat_...) in the Authorization: Bearer <token> header. The principal is the owning user, with that user's live tenant, kind and grants, narrowed by any scope minted onto the token.
Session JWTs, passwords, and missing headers are all 401. /mcp never admits unauthenticated requests.
Create a PAT from the profile page in the UI, or with the CLI:
qod auth pat create --name claude-code
# the token is printed ONCE; store it now
By default a PAT carries its owner's full permissions; the scope flags described under Scoped tokens and delegation can mint it narrower. A token can be revoked at any time (qod auth pat revoke --id <id>, or the profile page); revoking cascades to every token minted from it. A revoked or expired token stays visible in the listing until you discard it with qod auth pat delete --id <id> (or the profile page's Delete button); a live token must be revoked before it can be deleted. Tenant-scoped principals get their tenant inferred from the PAT owner; a superuser's PAT passes an explicit tenant argument on tools that need one.
Scoped tokens and delegation
A token can be minted narrower than its owner, so an agent holds a credential it cannot exceed even when fully compromised by an injected instruction. Every axis is optional on qod auth pat create (REST: the same field names on POST /api/auth/pat/create):
| Axis | CLI flag | Meaning |
|---|---|---|
roles | --role (repeatable) | Restrict the token to these roles |
databases | --database (repeatable) | Restrict the token to these databases |
pools | --pool (repeatable) | Restrict the token to these pools |
tools | --tool (repeatable) | Restrict the MCP tool surface the token may call |
verbCeiling | --verb-ceiling | Cap write power: RO, RW, DDL or ALL |
dropAdmin | --drop-admin | Strip the owner's superuser/admin standing from this token |
stmtTimeoutMs | --stmt-timeout-ms | Per-statement timeout, milliseconds |
maxRows | --max-rows | Row cap on this token's queries |
An axis left out is unrestricted on that axis (the token inherits the owner's reach); an axis sent as an empty array restricts the token to nothing on that axis. Scope always intersects the owner's grants and can never widen them - a mint request that tries to widen any axis is refused, naming the axis. Row-level and column-level policies that apply to the owner apply to every token they mint, untouched. Every axis also binds the catalog tools that run SQL for the owner (restore_snapshot, undrop_table, list_recoverable, and over REST the preview, data diff and Iceberg views), refused with token_scope (or write_requires_branch) before anything runs, and the branch tools check databases against the branch's parent database.
Tokens mint tokens. A PAT presented on the REST API may create a further-scoped child of itself, list its own descendants, and revoke or delete within its own subtree only - never itself, a sibling, its parent, or any other token of its owner. An axis narrowed at mint time can only narrow further down the chain, a child's expiry is clamped to its parent's, and chains are depth-capped (QOD_PAT_MAX_DEPTH, default 8). Revoking a token revokes its whole subtree in the same statement, so a stolen token cannot be rolled forward past its own revocation by minting a successor first.
A typical agent credential - read-only, one database, bounded per statement:
qod auth pat create --name analyst-agent \
--database acme_tpch --verb-ceiling RO \
--stmt-timeout-ms 30000 --max-rows 10000
Client configuration
Claude Code:
claude mcp add --transport http qod http://localhost:20900/mcp \
--header "Authorization: Bearer qod_pat_..."
Claude Desktop or any client that takes a JSON server entry:
{
"url": "https://your-manager/mcp",
"headers": { "Authorization": "Bearer qod_pat_..." }
}
Tools: data tier (every authenticated principal)
| Tool | Arguments | Returns |
|---|---|---|
run_sql | sql, database, pool?, branch?, max_rows? | Columns and rows as JSON, a truncated flag, rows affected for writes |
list_databases | superuser: tenant? | Tenant databases with kind and pools |
list_tables | database, schema?, branch? | Schemas and tables |
describe_table | database, schema, table, branch?, iceberg? | Columns and types plus a few sample rows |
table_history | database, schema, table, limit?, iceberg? | Snapshot history with change verbs |
list_snapshots | database, limit? | Snapshots and tags, for time-travel queries (AT (VERSION => n)) |
my_usage | none | Own usage counters and recent statements (PAT principals only) |
list_my_s3_credentials | none | Your own S3 credentials (scope, access key id, region, endpoint); the secret and the session token are never returned |
remove_my_s3_credential | id | Removes one of your S3 credentials; your statements stop reaching its scope at once |
create_branch | database, name, ttl_hours? | A writable zero-copy branch of the database (see Branching) |
list_branches | database, include_terminal? | Live branches, or every branch with include_terminal |
branch_changes | database, branch, counts? | Touched tables with kind and row counts, conflicts against main, merge verdict. The counts run as the PAT's owner: a table the owner may not read (or whose count fails) carries countsUnavailable: true and its zeros are no counts |
diff | database, branch, schema, table, limit?, cursor?, change_type? | Row-level diff of one table between the branch's fork and head |
propose_merge | database, branch | Records a merge request with the change set as of now; the reply's counts run as the PAT's owner, and the stored summary's per-table counts are shown to superusers only (zeroed and flagged countsUnavailable for everyone else) |
discard | database, branch | Discards a branch the caller owns (or any branch, as an admin) |
list_recoverable | database, limit?, superuser: tenant? | Dropped tables still recoverable. A regular user sees those of their own tenant they may recover: the name is free and their grants cover it |
undrop_table | database, schema, table, as_name?, admins: from_snapshot?, superuser: tenant? | Recovers a dropped table as the PAT's owner: a read grant on its last name and DDL in the schema. A regular user recovers in their own tenant, from the last live version only |
The branch argument on run_sql, list_tables and describe_table routes the call to that branch's own pool and catalog; database stays the parent name so token scopes keep applying. The iceberg argument on describe_table and table_history names an attached Iceberg source; the schema and table are then read inside that catalog. It goes through the same admin-only gate as the REST views, so it needs an admin principal, and Iceberg snapshot ids come back as strings (time travel with run_sql and AT (VERSION => <id>)). describe_table returns the current table detail and files, plus a sample run as the caller that is dropped rather than failing the call when it cannot run. There is deliberately no merge tool: merging is a human action through the admin console, the CLI or REST, by a principal other than the proposer. A token minted with branch_only is refused any write on the live database (write_requires_branch) while reads and branch writes work as granted.
There is deliberately no tool that adds an S3 credential: a secret must not pass through an agent's context. Users add their keys with qod profile s3 add or the profile page.
run_sql executes through the same in-process path as the FlightSQL edge and goes through the FlightSQL handshake's gate first (tenant and pool kill switches, user registered and enabled, pool access). It runs as the PAT owner's principal, never as the manager's system principal, and the node judges each statement against that user's grants (or the tenant's OPA policy), so RBAC verbs decide whether writes are allowed, RLS/CLS apply, and suspended pools wake on the first statement exactly as they do for FlightSQL clients. Results are capped server-side by QOD_MCP_MAX_ROWS (default 500); the tool's max_rows argument can only lower the cap, and truncated results carry truncated: true so the agent aggregates or filters instead of paginating blindly.
Tools: admin tier (admin or superuser principals)
| Tool | Notes |
|---|---|
list_pools, get_pool_status | Nodes, health, served counts, suspended flag, autoscale band |
scale_pool | Band refusals (outside_band) surface as tool errors with the reason |
suspend_pool, resume_pool | Scale-to-zero and wake |
restart_node, quarantine_node, unquarantine_node | Node lifecycle |
active_statements, kill_statement | Inspect and kill running statements |
run_maintenance, maintenance_runs | Trigger and inspect managed maintenance |
create_pool, stop_pool, delete_pool | Pool lifecycle |
set_pool_autoscale, set_pool_resources, set_pool_pod_template, set_pool_disabled, set_node_max_concurrent | Pool settings; band and quota refusals surface as tool errors |
set_pool_init_sql | Replace a pool's init SQL (empty clears it), effective at the next node start; a tenant admin's text runs under the initSql allowlist and a refused statement answers init_sql_refused |
create_tag, protect_tag, delete_tag | Deleting a protected tag is refused; there is no unprotect tool, so a protected tag stays protected over MCP |
restore_snapshot, and undrop_table / list_recoverable in any tenant | Restore and undrop run as the PAT's owner with the token's restriction, so the node judges each statement against the owner's grants and a table the owner cannot write answers acl_denied; the restore dry_run also runs as the owner and reports aggregate change counts only |
get_maintenance_policy, upsert_maintenance_policy, delete_maintenance_policy | Managed maintenance policies |
list_tenants, create_tenant, delete_tenant, set_tenant_disabled, set_tenant_auth, set_tenant_acl | Tenant lifecycle and settings; delete_tenant is refused while the tenant still has pools |
list_tenant_locations, add_tenant_location, remove_tenant_location | The local folders a tenant's users may read and write files in (an absolute folder under <QOD_TENANT_FILES_ROOT>/<tenant id>/) |
list_databases_admin, create_database, update_database, delete_database, metastore_defaults | Database (tenant-db) lifecycle; delete_database with purge_managed_data also drops manager-provisioned storage |
list_users, create_user, update_user, delete_user, user_effective_permissions | Users (see User creation over MCP) |
list_roles, create_role, delete_role, list_role_permissions, grant_role_permission, revoke_role_permission | Roles and their table permissions |
list_column_policies, create_column_policy, update_column_policy, delete_column_policy, list_row_policies, create_row_policy, update_row_policy, delete_row_policy | Column masking and row filters on a role |
list_groups, create_group, delete_group, add_membership, remove_membership, list_group_role_memberships | Groups and user, group and role memberships |
list_pool_permissions, grant_pool_permission, revoke_pool_permission | Pool access grants |
list_federated_sources, upsert_federated_source, delete_federated_source, set_federated_secret, delete_federated_secret | Federated sources and their secrets; refused with federation_disabled when federation is not enabled. A source of type sql is superuser only (superuser_required) |
create_pat, list_pats, revoke_pat, delete_pat | Self-scoped: a token mints, lists, revokes and deletes only within its own subtree, and a child's scope can only narrow (see Scoped tokens and delegation); the token value is returned once |
manifest_export, manifest_import | Control-plane manifest round-trip; manifest_import is superuser only |
get_config | The manager's configuration registry; superuser only |
audit_search, statement_history, usage_report, usage_trends | Audit log, statement history and usage |
Built-in roles and groups keep their protection over MCP: a tool call that would delete or edit one answers builtin_protected, exactly as on REST.
User creation over MCP
create_user takes username, password, tenant (omitting it creates a superuser, which only a superuser credential may do), kind (user by default, or admin: management rights, not an RBAC role), optional email and must_change_password, and the arrays roles and groups (role and group names in the tenant). An omitted array defaults to the built-in qod_all_tables / qod_all_pools, giving the user full access to every table and pool of the tenant; an empty array is refused, and so is an unknown name or either array on a superuser. update_user takes id plus any of password, kind, email, must_change_password, enabled. See Built-in roles and groups.
tools/list is computed per principal: a PAT owned by a kind=user account sees the data tier only; a tenant admin sees both tiers scoped to their tenant; a superuser's PAT sees both cross-tenant. tools/call re-checks the tier server-side. Tool calls land in the audit trail as the acting user.
Not available over MCP
The admin tier covers nearly every REST mutation. A small set of operations has no MCP tool by design, whatever the credential:
- Interactive authentication flows: login, logout, password change, forgot and reset password, SSO and OIDC callbacks, SQL-token exchange
- Branch merge: merging is a human action by a principal other than the proposer (admin console, CLI or REST)
- The OPA policy dry run (
qod tenant opa-test), a diagnostic for policy authors - Adding an S3 credential (
qod profile s3 add), so a secret never passes through an agent, and an admin's view of a user's S3 credentials (qod user s3) - Fleet server management (join, heartbeat, approve, drain, remove)
- The SCIM 2.0 provisioning endpoints, which are the identity provider's wire protocol
- Tag unprotect: once protected, a tag cannot be unprotected or deleted over MCP
Tool exposure does not bypass authorization: every tool runs through the same handlers as REST, with the tenant scope, the superuser-only checks, the built-in role and group protection and the token's own restriction applied.
Errors
Protocol failures (bad token, malformed JSON-RPC, unknown method) come back as HTTP 401 or JSON-RPC error objects. Everything that happens inside a tool (a node's QoD: ... refusal, a SQL error, an outside_band refusal, a "pool is resuming" timeout) returns a normal tools/call result with isError: true and a message written for the agent to act on. Internal exceptions are sanitized to a generic message with a correlation id in the server log.
Configuration
| Key | Default | Env | Meaning |
|---|---|---|---|
quack-on-demand.mcp.enabled | true | QOD_MCP_ENABLED | Serve POST /mcp at all |
quack-on-demand.mcp.maxRows | 500 | QOD_MCP_MAX_ROWS | Hard cap on rows returned by run_sql |
Statement execution inherits the edge's existing timeouts.
Troubleshooting
- 401 on every call: the bearer is not a live PAT (revoked, expired, owner disabled) or is a session JWT, which
/mcprefuses by design. Mint a fresh PAT. - A tool is missing from
tools/list: the credential's tier does not include it; admin tools need a PAT owned by an admin or a superuser (and not minted with--drop-admin). run_sql,restore_snapshotorundrop_tablereturnsQoD: ...: the node refused the statement for the PAT's owner; the reason names what it refused. Grant the owning roleRO/RW/DDLas needed.QoD: authorization unavailable, retrymeans the node could not get a verdict from the manager in time: retry. These tools run as the PAT's owner, never as a superuser, so an admin-owned PAT still needs the table grants.- "pool is resuming": the target pool was suspended and is waking; retry in a few seconds.