SQL API (ClickHouse)

loom-kernel provides an injectable SqlQueryService for running SQL through named, config-driven connections, plus an optional generic REST endpoint (POST /sql/{connection}). Every query carries one or more roles derived from the caller’s verified JWT claims, intersected with a per-connection allowlist and applied as per-query settings, so roles never leak across queries sharing the connection pool.

Important

Through the REST endpoint the roles come from the identity, never from the request body: the body can only narrow what the claim already grants. The allowlist is the ceiling of the connection — the last barrier, not a per-caller permission. Read Threat model before exposing the endpoint.

The first supported backend is ClickHouse. The sql: section is backend-agnostic: each connection declares its backend explicitly.

---

Install

pip install "loom-kernel[clickhouse]"
# and, if you authenticate the endpoint with the native JWT middleware:
pip install "loom-kernel[jwt]"

Minimum versions (enforced fail-closed at startup):

Component	Minimum	Why
ClickHouse server	24.4	Per-query role HTTP parameter
clickhouse-connect	0.9.2	role as a per-query transport setting

At startup the registry asserts driver support and runs a sentinel-role probe (SELECT 1 with a nonexistent role must be rejected with code 511). If the server silently accepts the sentinel role, startup aborts — the server’s role enforcement is never assumed. A connection whose allowed_roles holds more than one role also verifies that the driver can emit repeated role HTTP parameters (see Several roles in one query); if it cannot, startup aborts instead of sending an invalid single role.

---

Configuration

Add a top-level sql: section. Absent section → zero changes: no connection is opened and no route is mounted (SqlQueryService resolves to a null implementation that raises an actionable ConfigError on first use).

sql:
  connections:
    analytics:
      backend: clickhouse
      url: ${oc.env:CLICKHOUSE_ANALYTICS_URL}   # secret via env/SSM — never inline
      # Ceiling for the CONNECTION, never a per-caller permission: the effective
      # roles are these intersected with the caller's verified roles claim.
      allowed_roles: [role_viz_reader, role_viz_sales]
      default_role: role_viz_reader   # required if sql_endpoint.enabled and no allowlist
      readonly: true                  # default: readonly=1 on every query
      default_limit: 1000
      max_limit: 10000
      max_execution_time: 30          # seconds
      max_sql_bytes: 262144
      connect_timeout: 10
      send_receive_timeout: 60
      executor_threads: null          # driver default when null
      pool_size: null                 # driver default when null
      settings: {}                    # extra ClickHouse settings — can never override policy
      sql_endpoint:
        enabled: true                 # opt-in (default false)
        auth: identity                # REQUIRED to mount: 'identity' or 'external'
        path: /sql/analytics          # default: /sql/{connection}
        include_in_schema: false

The claim carrying the caller’s roles belongs to the authentication mechanism, not to the connection — it is declared once for the whole app and is required whenever a mounted endpoint has a non-empty allowed_roles:

app:
  rest:
    auth:
      jwt:
        audience: loom-api
        roles_claim: loom_sql_roles   # verified claim carrying the caller's roles

Connections open inside the app lifespan and close on shutdown. A connection that fails its startup probe aborts the whole app start (explicit fail-fast).

Three config rules make the unsafe shapes unrepresentable — they fail at parse or at startup, never at request time:

Rule	Why
A mounted endpoint with a non-empty allowed_roles requires auth: identity	It is the only mode carrying a verified identity to bind the roles to
That endpoint also requires a mechanism that binds roles (for JWT, app.rest.auth.jwt.roles_claim)	Otherwise any authenticated caller could pick any allowlisted role
auth: identity requires an authentication mechanism, app.rest.auth.jwt.audience when that mechanism is JWT, and the mounted path must not appear in the auth exclude_paths	An identity-bound role is worthless if tokens minted for another service are accepted, or if the path skips authentication altogether

Note

The sql: section is independent from the ETL storage.clickhouse section — they serve different purposes (interactive queries vs. pipelines). Share the DSN through a single environment variable if both point at the same cluster.

Query from a use case, as the caller

Two collaborators are always registered in the container (APPLICATION scope) and are injected by constructor, like any other service:

Collaborator	Roles come from	Use it for
CallerBoundSql	The verified identity ∩ allowed_roles	Anything done on behalf of a caller — the default
SqlQueryService	A roles argument written by the calling code	System work with no caller: a scheduled job, a migration, a health probe

CallerBoundSql is the default because it is the only one whose privileges are decided by the caller’s own entitlements. It has no roles parameter in any form:

from loom.core.identity import Identity
from loom.core.sql import CallerBoundSql
from loom.core.use_case.markers import Caller
from loom.core.use_case.use_case import UseCase


class TopProductsUseCase(UseCase[Product, dict]):
    def __init__(self, sql: CallerBoundSql) -> None:
        self._sql = sql

    async def execute(self, identity: Identity = Caller()) -> dict:
        result = await self._sql.execute(
            "SELECT product, sum(amount) AS total FROM sales "
            "WHERE day >= {start:Date} GROUP BY product ORDER BY total DESC",
            connection="analytics",
            identity=identity,
            parameters={"start": "2026-01-01"},
            limit=100,
        )
        return {"rows": result.rows, "has_more": result.has_more}

Warning

The identity must be declared with Caller(). That marker is what makes the executor inject the identity the transport verified. Written without it — async def execute(self, identity: Identity) — it is an ordinary primitive parameter, which the compiler binds from the params the calling code supplies: the caller then picks its own identity, and with it the roles. On the agent path those params are tool arguments the model writes, and identity would appear in the tool schema. A query bound to a forged identity is not bound at all.

A caller holding no allowlisted role for that connection is refused with RolesNotBoundError before the backend is touched — never run as default_role. So is a connection with an empty allowed_roles: its only role is the shared one, which is derived from nobody. Use SqlQueryService for that unbound work.

Parameters are always bound server-side ({name:Type} placeholders) — never interpolated into the SQL string.

Note

SqlQueryService.roles takes a sequence: a query may carry several roles and runs with the union of their privileges. Each one is validated against allowed_roles; a single rejected role refuses the whole call instead of silently narrowing it. That service applies no identity binding: the roles are whatever the calling code wrote, bounded only by the allowlist. Identity binding is what CallerBoundSql and the REST edge add on top.

---

The REST endpoint

A connection mounts POST /sql/{connection} only with double opt-in: sql_endpoint.enabled: true and an explicit sql_endpoint.auth value. Without auth, the endpoint does not mount.

auth value	Meaning
identity	Requires a configured authentication mechanism — the built-in app.rest.auth.jwt section (with a validated audience) or a create_app(authenticator=...) of your own; startup fails with ConfigError otherwise. The only mode that can bind roles to an identity
external	Explicit acknowledgement that the operator provides authentication in front of the app. No identity the framework can read, therefore no allowlist: single-role endpoint only
jwt	Deprecated alias of identity, kept for existing configs. It named a mechanism where the contract only needs “the framework knows the caller”

auth decides who gets in; the authentication mechanism decides what they may become once in — for JWT, through app.rest.auth.jwt.roles_claim.

Request body — backend settings are rejected by schema:

{
  "sql": "SELECT id, name FROM products ORDER BY id",
  "roles": ["role_viz_reader"],
  "parameters": {"start": "2026-01-01"},
  "limit": 100,
  "offset": 0
}

roles is optional and can only narrow: it must be a subset of the roles the verified claim already grants. Omitting it runs the query with all of them. Asking for a role the identity does not hold is a 403, even if the role is allowlisted.

Response envelope

Every query returns the same tabular envelope (SqlQueryResult):

{
  "columns": [
    {"name": "id", "type": "UInt64"},
    {"name": "name", "type": "String"}
  ],
  "rows": [[1, "widget"], [2, "gadget"]],
  "row_count": 2,
  "limit": 100,
  "offset": 0,
  "has_more": false,
  "elapsed_ms": 12.4
}

Column types are the native backend names. Serialization covers datetime/date/UUID/Decimal natively, IPv4/IPv6 as strings, bytes as base64, and a documented str() fallback for exotic types — a result never produces a bodyless 500.

Pagination

• Effective limit: min(limit or default_limit, max_limit); the executor fetches limit + 1 rows to compute has_more and trims the extra row.

• offset skips rows; both are applied as native query settings — the SQL text is never rewritten.

• Backstop: max_result_rows = max_limit + 1 with result_overflow_mode='throw' — oversized results fail loudly instead of truncating silently.

Warning

Stable pagination requires an explicit ORDER BY in your SQL. Each page re-executes the query; without a deterministic order, rows can repeat or disappear between pages.

Errors

Errors use the standard framework body (detail.code, detail.message, detail.trace_id):

Status	When
401	Missing, expired or otherwise invalid token (emitted by the JWT middleware, before the endpoint)
404	Unknown connection name
403	The verified identity grants no allowed role, the body asks for a role the identity does not hold, or the role is outside the allowlist
422	SQL rejected by the backend (sanitized first line, no host/DSN), invalid body, or SQL larger than max_sql_bytes
500	Backend unreachable — generic message, no URL leaked

Every 403 caused by identity binding carries the same generic message on purpose (no oracle telling an attacker which part of their token failed); the precise reason — connection, subject and cause — is logged server-side at WARNING.

---

Role model and grants

The policy is fail-closed and resolves in this order for a request reaching a mounted endpoint whose app declares a roles_claim:

1. Identity. No verified claims in the request → 403. The claim must be a string or a list of non-empty strings; absent, empty or otherwise typed → 403. Nothing is coerced.

2. Intersection. The authorized set is the claim values ∩ allowed_roles. Roles the caller claims but the connection does not allow are dropped; if nothing survives → 403. default_role is never a fallback here.

3. Narrowing. A roles list in the body must be a subset of the authorized set; anything else → 403. Absent, it runs with the whole authorized set.

4. Allowlist, again. The service re-validates every effective role against allowed_roles before the executor is touched — the last barrier, independent of how the roles were resolved.

5. The effective roles are sent as per-query settings — nothing leaks from one query to the next across the shared connection pool.

The invariant, stated once: identity and body may only ever restrict the set of roles, never widen it.

Connections used directly from a use case (no HTTP) skip steps 1-3: there is no identity to bind, and allowed_roles plus default_role are the whole policy.

Provision ClickHouse with exactly these statements per data role and connection user:

-- The data ROLE needs:
GRANT SELECT ON <db>.* TO <role>;
-- (SHOW TABLES/COLUMNS only if callers need introspection without SELECT)

-- The connection USER needs:
GRANT <role> TO <user>;                 -- one per allowlisted role
ALTER USER <user> DEFAULT ROLE NONE;    -- no privileges when no role is applied

Principal	Grant	Purpose
Data role (role_viz_reader, …)	GRANT SELECT ON <db>.*	Read the data it exposes
Connection user	GRANT <role> TO <user> per allowlisted role	May activate exactly the allowlisted roles
Connection user	ALTER USER ... DEFAULT ROLE NONE	Fail-closed: no privileges without an explicit role
Startup probe	—	Needs no grant (sentinel role must not exist)

Note the direct consequence of row two: the connection user must hold every allowlisted role for any of them to be assumable, so ClickHouse alone would happily grant a caller the most privileged role it is asked for. What stops that is the claim binding above, not the server. Keep the allowlist as small as the roles your identity provider actually issues.

---

Several roles in one query

A caller whose claim grants more than one role runs each query with the union of their privileges — ClickHouse activates every role sent with the query:

{"sql": "SELECT * FROM sales JOIN customers USING (id)", "roles": ["role_sales", "role_crm"]}

Mechanically, roles travel as repeated HTTP parameters (role=a&role=b); a comma-joined value is read as a single role name and rejected with code 511.

Note

clickhouse-connect 0.15.1 (and upstream main) builds the request URL with urlencode(params) without doseq=True, so a sequence value would be sent as its Python repr. The driver boundary (loom.core.sql.clickhouse._client) rebinds that encoder to the doseq=True variant: output is byte-identical for the scalar settings the driver builds today and only sequences change — and their current encoding is invalid anyway. A startup check (ConfigError) and a unit test pinning the patch point make sure the workaround can never degrade silently, and it is removed as soon as upstream encodes lists correctly.

Single-role connections never depend on that workaround: one role is sent as a plain scalar, exactly as before.

---

Threat model

Danger

allowed_roles is the ceiling of the connection, not a per-caller permission. What makes it safe is the identity binding described here. Read this section in full before mounting the REST endpoint.

What the framework now guarantees

1. Roles come from a verified identity. The JWT mechanism verifies signature, exp/nbf, sub and the configured aud/iss, then projects the claims onto an Identity — roles from roles_claim, string claims as attributes. The endpoint intersects the roles that identity holds with allowed_roles. The request body cannot select a role: it can only narrow the result. A caller holding no allowlisted role gets a 403 — never default_role. Swapping the mechanism changes nothing here: the endpoint sees an identity, not a token.

2. The unsafe shapes are unrepresentable. A mounted endpoint with a non-empty allowlist and an auth mode other than identity fails at config parse; the same endpoint without a role-binding mechanism, without audience under JWT, or with a mounted path listed in the auth exclude_paths, fails at startup. There is no runtime path into the old behaviour.

3. audience is mandatory. Without a validated aud, any token signed by the same key — including one minted for a different service, carrying its own idea of a roles claim — would be accepted and could name your ClickHouse roles.

4. Auditability. Each request emits a span labelled with the effective roles and the caller subject; denials are logged at WARNING with connection, subject and cause. CallerBoundSql opens a span of its own for every query it accepts, carrying the same roles, subject and mechanism labels. It is a different span from the endpoint’s, under a narrower scope and its own name, so a use case acting on behalf of a caller is auditable on the same terms rather than through the same record.

Where this comes from. Measured on ClickHouse 25.3 with a single credential and only the role parameter changed: the default role returned 497 ACCESS_DENIED, while role=role_viz_reader returned 1,171,206 rows from a table holding PII. When role travelled in the request body, that was one JSON edit away for any bearer of any valid token.

What is still on you

1. The claim is only as good as its issuer. The framework verifies the signature and the audience; it cannot tell whether your identity provider was right to put role_viz_sales in that token. Issue the claim from the same system that owns the business authorization, and keep token lifetimes short — verification is stateless, so a role revoked at the IdP stays usable until the token expires.

2. There is still no per-route authorization. JwtAuthMiddleware is mounted for the whole application. A valid token reaches every mounted SQL route; what it can do there is now bounded per route by that connection’s allowlist ∩ the claim, but the authentication decision itself remains all-or-nothing.

3. ClickHouse is the floor, not the ceiling. The server rejects unknown roles (511 UNKNOWN_ROLE) and non-granted roles (512 SET_NON_GRANTED_ROLE), which protects against typos and role injection. It cannot distinguish callers: the connection user holds every allowlisted role by design. Identity separation exists only because the framework applies the intersection above.

4. The unbound path is still reachable. SqlQueryService remains registered, and its roles argument is bound to nobody: a use case that injects it queries with any allowlisted role, whatever its caller holds. That is the point — system work has no caller — but nothing stops it being injected where CallerBoundSql belongs. Treat the choice as a review item: inside a request or an agent tool, the bound one is the answer.

5. allowed_roles should list composite roles, one per exposed profile, not individual data roles — so the union any caller can reach matches a declared profile.

app:
  rest:
    auth:
      jwt:
        audience: loom-analytics
        roles_claim: loom_sql_roles                   # what each caller may use

sql:
  connections:
    analytics:
      backend: clickhouse
      url: ${oc.env:CLICKHOUSE_ANALYTICS_URL}
      allowed_roles: [role_api_viz, role_api_sales]   # ceiling of the connection
      sql_endpoint:
        enabled: true
        auth: identity

Without an identity provider that can issue the claim, the single-role shape stays valid and needs no binding: allowed_roles: [] plus a fixed default_role runs every caller with exactly one role — no escalation, and no per-caller distinction either. The binding is decided per connection: a single-role connection keeps working unchanged in an application that also serves a multi-role one.

---

Hardening

• Never expose the endpoint without authentication. The auth field is mandatory to mount; external is an explicit acknowledgement, not a bypass. Every mounted endpoint emits a startup warning naming its path, connection, readonly flag, auth mode, role count and the mechanism its roles are bound to.

• Keep the allowlist to the roles your IdP actually issues. It is the ceiling of what any claim can unlock on that connection: a role nobody should reach through HTTP does not belong in it (see Threat model).

• Issue the roles claim from the system that owns the authorization, and keep token lifetimes short: verification is stateless, so a revoked role remains usable until the token expires.

• No SOURCES grants for the connection user. Table functions such as url(), s3(), remote(), mysql() and postgresql() enable SSRF and data exfiltration even under readonly=1. Do not grant them.

• No broad system.* access. Introspection tables leak topology, settings and query history.

• Keep DEFAULT ROLE NONE on the connection user so a query without an applied role has no privileges.

• readonly: true is the default. Disabling it while the endpoint is enabled emits a startup warning, and the driver runs with query_retries=0 to avoid re-executing non-idempotent statements.

• Credentials via ${oc.env:...} / SSM / Secrets Manager — never inline. The password is redacted from logs, reprs and errors; SQL is logged only at DEBUG (truncated), parameters never.

• Rate limiting is the operator’s responsibility (reverse proxy / API gateway).

• Minimum versions: ClickHouse ≥ 24.4 and clickhouse-connect ≥ 0.9.2 — older combinations fail the startup probe.

---

Authentication

sql_endpoint.auth: identity requires an authentication mechanism. Any Authenticator satisfies it — see Caller identity and authorization for a non-JWT one. The batteries-included mechanism is the framework’s native stateless JWT, configured under app.rest.auth.jwt:

app:
  rest:
    auth:
      jwt:
        secret_path: ${oc.env:LOOM_JWT_SECRET_PATH}   # HS256 — a path, not the value
        # public_keys:                      # RS256/ES256/EdDSA — one PEM per key id
        #   "2026-08": ${oc.env:LOOM_JWT_PUBLIC_KEY_PEM}
        algorithms: [HS256]                 # explicit allowlist; 'none' always rejected
        audience: loom-api                  # REQUIRED by any sql_endpoint with auth: identity
        issuer: null                        # validated only when set
        leeway_seconds: 0
        exclude_paths: [/docs, /redoc, /openapi.json, /metrics, /health]
        roles_claim: loom_sql_roles         # REQUIRED by any mounted sql_endpoint whose
                                            # connection has a non-empty allowed_roles

• Stateless — signature, exp and sub (both required) plus nbf, and aud/iss when configured, are verified per request. No Redis or server-side session store is needed; early token revocation is out of scope.

• sub is mandatory. A token without a subject carries no identity to authorize against, nor to audit afterwards, so it is rejected with 401.

• Exactly one of secret_path (HS*) or public_keys (RS*/ES*/EdDSA static PEMs, keyed by key id) must be set, and the algorithm allowlist must match the key type — validated fail-fast at startup.

• secret_path is a path, not the value. The config is a msgspec.Struct, so any serializer emits its fields verbatim: holding an HS secret would let a config dump publish the key that both verifies and signs. Public keys are safe to inline because they only verify. The container has to materialize the value to a file: Kubernetes mounts a Secret as a volume directly, while ECS/Fargate injects task-definition secrets as environment variables only, so there an entrypoint must write the value to a path on a tmpfs volume before the app starts.

• With public_keys, the key is chosen by the token’s kid header, never by trying each key in turn: exhaustive trial would decouple every algorithm from its key family, which is what makes algorithm confusion impossible here. A token with no kid verifies only when a single key is configured, so a rotation overlap needs the issuer to stamp one.

• On success the verified claims are projected onto an Identity published for the request; on failure the response is a 401 with the standard error body, a WWW-Authenticate challenge and no hint about the cryptographic reason. Read the caller anywhere with current_identity() — the raw claims are deliberately not exposed.

• The identity roles are consumed by the SQL endpoints when roles_claim is set: that is where an authenticated caller becomes a set of ClickHouse roles (Threat model).

• exclude_paths bypasses authentication for exact paths (docs, scrape endpoints). A mounted SQL path listed there aborts startup — it would serve SQL unauthenticated.

• When the section is present, create_app mounts the middleware for the whole app — authentication is all-or-nothing, there is no per-route authorization. A missing pyjwt extra fails at startup with an install hint, so the API never starts silently unauthenticated.

• audience is required by any sql_endpoint with auth: identity (startup fails otherwise): without a validated aud, any token signed by the same key is accepted whatever it was issued for, and could carry the roles claim. Set issuer too when several issuers share the key material.

Example token

{
  "sub": "svc-reporting@example.com",
  "aud": "loom-api",
  "exp": 1785600000,
  "loom_sql_roles": ["role_api_viz"]
}

The claim may also be a single string ("loom_sql_roles": "role_api_viz"). Anything else — a number, an object, a list holding non-strings — is refused with 403 rather than coerced.