Table-level RBAC: grants CSV & deny-by-default

The grant values#

none

Explicitly nothing. Used to revoke. Permits no operation.

read

SELECT only.

write

INSERT / UPDATE / DELETE / CREATE only.

read_write

Both read and write.

The grant CSV is a comma-separated list of table:grant pairs, e.g. users:read_write,orders:read. Table names are matched case-insensitively (lowercased). A malformed or unknown-grant entry is dropped during parsing — it grants nothing, so deny-by-default still holds. The special table name * is a wildcard fallback.

Operation classification (op_class)#

A statement is classified into exactly one of two operation classes by its first keyword: SELECT → "read"; everything else (INSERT/UPDATE/DELETE/CREATE) → "write". An UNRECOGNIZED keyword is treated as a WRITE — the more restrictive class — so an unknown statement can never slip through a read-only grant.

The tables a statement touches are resolved by grant_tables: for a SELECT it is the FROM table PLUS every JOINed table (all must be readable); for INSERT it is the INTO table; for UPDATE the updated table; for DELETE the FROM table; for CREATE TABLE the table being created; for CREATE INDEX the ON table. If the target table cannot be determined, a non-admin key is denied (fail closed — "could not determine target table").

The decision: authorize_stmt#

authorize_stmt(scope, grants_csv, qql) is the pure (no-I/O) core decision, exhaustively unit-testable. It returns Ok(()) for allow or Err(message) for deny. The order of checks is security-critical:

1. Governance-catalog write deny (runs FIRST, before the admin bypass): a WRITE targeting _sys_users / _sys_sessions / _sys_roles / _sys_api_keys is denied for EVERYONE via QQL, including admin scope.
2. Admin bypass: if scope == "admin", return Ok (admin keys bypass table grants; deploy-catalog writes are allowed).
3. Determine target tables; none/empty → deny "could not determine target table".
4. For each table: if this is a WRITE to a _sys_* table → deny ("system catalog is admin-only for writes"). Then check the grant: if the per-table grant (or the * wildcard) does not allow the op → deny "table '<t>': <op> denied".

// authorize_stmt — the per-table check after the catalog/admin gates.
if op == "write" && table.starts_with("_sys_") {
    return Err(format!(
        "table '{table}': system catalog is admin-only for writes"
    ));
}
if !table_op_allowed(&grants, table, op) {
    return Err(format!("table '{table}': {op} denied"));
}

table_op_allowed resolves precedence: an explicit per-table entry ALWAYS wins over a * wildcard; with neither present it returns false (deny).

The wildcard#

A grant entry on * is a fallback applied to any table that has no explicit entry. For example *:read,orders:read_write allows reading any table and reading+writing orders. But an explicit entry overrides the wildcard in BOTH directions — *:read_write,secrets:none permits everything except secrets, which is explicitly denied.

Where the check runs — auth.check_table_grant#

auth.check_table_grant (key auth.check_table_grant) is the before-gate that wraps authorize_stmt. It runs in the before-chain AFTER auth.api_key (or auth.signed), which injects api_key_scope + table_grants into ctx. It reads the QQL from the request body (or an explicit qql field for the inline/batch form), pulls scope + grants from explicit fields or ctx, and returns { granted: true } on allow or denies with the authorize_stmt reason on deny.

Wiring: the single-statement routes (qql.http, qql.get) use the group chain auth.api_key → auth.check_table_grant. The batch route (qql.batch) runs ONLY auth.api_key in the before-chain (gating a JSON array body as one statement would be meaningless); the batch handler then enforces EACH statement independently against the injected grants. The signed-key group chains auth.signed → auth.check_table_grant the same way.

  request ─► auth.api_key  (hash X-API-Key → row;
            |               inject scope + table_grants)
            v
          auth.check_table_grant
            |   authorize_stmt(scope, grants, body-qql)
            ├─ admin scope ─────────────► allow (catalog deny still applies)
            ├─ all tables granted? ─────► allow → handler runs
            └─ any table denied / _sys_* ► 403 forbidden (short-circuit)

Governance catalogs are write-denied to everyone#

The four GOVERNANCE catalogs — _sys_users, _sys_sessions, _sys_roles, _sys_api_keys — may NEVER be written through QQL by anyone, including an admin-scoped key. This deny tier runs before the admin bypass in authorize_stmt. These tables are mutated EXCLUSIVELY by the governance functions over the internal db.execute path, which does not pass through authorize_stmt. Denying them on the QQL surface closes the cross-surface self-promotion gap: otherwise an admin API key could INSERT INTO _sys_users { role: "custodian" } and bypass the custodian-only promotion hierarchy.

Admin keys retain QQL-write ability over the DEPLOY catalogs (_sys_routes/_sys_workers/_sys_jobs/_sys_pages/_sys_assets/_sys_functions/_sys_groups) via the admin bypass. Non-admin keys may never write ANY _sys_* table (a separate rule), so a tenant can't author scheduled jobs, mint keys, or reroute traffic. Reads of catalogs remain grant-governed.

Worked examples#

# Key minted with grants "users:read,orders:read"
curl -X POST http://localhost:8080/api/qql \
  -H 'X-API-Key: <reporter-key>' \
  --data 'INSERT INTO orders { id: 1, total: 99 }'

{
  "error": { "code": "access_denied", "message": "table 'orders': write denied" },
  "data": null,
  "root": { "took_us": 0, "request_id": "…" }
}

The key has only read on orders; the write is classified as a WRITE and denied. (HTTP 403.)

# Key grants "users:read" only (no grant on orders)
curl -X POST http://localhost:8080/api/qql \
  -H 'X-API-Key: <reporter-key>' \
  --data 'SELECT u.name FROM users u JOIN orders o ON o.user = u.id'

{ "error": { "code": "access_denied", "message": "table 'orders': read denied" }, "data": null, "root": { … } }

A SELECT needs read (or read_write) on the FROM table AND every JOINed table. The join to orders has no grant → denied. Grant users:read,orders:read and the same query returns rows.

curl -X POST http://localhost:8080/api/qql \
  -H 'X-API-Key: <admin-key>' \
  --data 'INSERT INTO _sys_users { username: "x", role: "custodian" }'

{
  "error": { "code": "access_denied", "message": "table '_sys_users': governance catalog is not writable via QQL (managed by governance fns only)" },
  "data": null,
  "root": { … }
}

The governance-catalog deny runs before the admin bypass. Use auth.create_custodian (custodian only) instead.