pt-access — shared permission → ClickHouse access-control logic

One place for the permission pipeline every Point Topic data app needs. Ported from the production-proven point-topic-mcp implementation (issues #100/#104/#105) so the MCP server, the ontology web app (onto-app-new) and future consumers cannot drift.

Full design context: HANDOFF.md. Tracked by onto-app-new#25 (Auth0 login + per-org dataset/geo permissions).

The pipeline

JWT claims ──► policy spec ──► SQL predicate ──► ClickHouse DDL
(what you   (normalised    (composer:      (provisioning:
 have)       dataset +      escaping,       role, grants,
             filter rows)   containment,    row policies,
                            OR-grouping)    settings profile)

Identity comes from the shared sub-site Auth0 tenant (point-topic.eu.auth0.com). The Post-Login Action stamps pt_org_id and products[] ([{name, permissions}], scalar values only). Geo filters are never in the token — list values would bloat tokens past proxy header limits (documented decision in the Action's own code) — they are read fresh from sub-site MongoDB (organisations.productPermissions.<ds> = {field: string[]}) on every login/session, then converted to ClickHouse row policies.

Modules

Instance-specific config (CH host/port/creds, grant_to service user, measurement tables, PREWHERE profile) stays in the consuming app's environment — this package takes it as parameters, never hardcodes it. provision_org_role() already takes grant_to per call; the convenience wrapper uses GRANT_TO_USER (env MCP_CLICKHOUSE_GRANT_TO_USER, default mcp_service) — rename/re-purpose per instance when wiring a new consumer.

Usage

from pt_access.jwt import Auth0TokenVerifier
from pt_access.claims import get_org_id, get_held_products, is_pt_admin
from pt_access.provisioning import provision_org_on_login

# 1. Verify the bearer token (FastAPI dependency, etc.)
claims = await Auth0TokenVerifier(
    auth0_domain="point-topic.eu.auth0.com",
    audience="<your_client_id>",   # ID token audience = client_id
).verify_token(bearer)

# 2. Provision the org's ClickHouse role + row policy (on login / session start)
if not is_pt_admin(claims):
    summary = provision_org_on_login(get_org_id(claims), get_held_products(claims))
    # summary: {"role": "org_<id>", "granted_to", "using", "tables", "warnings"}

# 3. Per-query scoping on the app's READ client (never SET ROLE on a shared
#    connection — thread-local clients are shared across users):
#    client.query(sql, settings={"role": "org_<id>"})

provision_org_on_login is a no-op when CLICKHOUSE_PROVISIONING_USER/_PASSWORD are unset; without a provisioning credential orgs fail closed on the engine (role never granted). Requires SUB_SITE_MONGODB_URI (read-only sub-site Mongo user) for the fresh per-org read.

Behaviour contract (do not "fix" — each rule exists because of a live incident)

• Fail closed: no held data-source products → deny (REVOKE role + row policy USING 0); unknown/empty filter fields → that dataset excluded, ALL excluded → deny; a held product with no stored values → bare (DATA_SOURCE='<ds>') (omit-empty contract = full access to that product's data).
• Per-dataset disjunction: (DATA_SOURCE='upc' AND <geo>) OR (DATA_SOURCE='gbs') — never a bare DATA_SOURCE IN (...) AND <geo> blob.
• OR-grouping: multiple predicates must be wrapped (A OR B) inside the DATA_SOURCE guard — SQL precedence otherwise leaks other datasets' rows at the postcode (found live on prod 2026-08-05).
• PREWHERE: row policies on non-sorting-key columns return 0 rows under PREWHERE (ClickHouse GH #85222). Provisioning creates a settings profile (optimize_move_to_prewhere = 0) and attaches it to the service user — per-query SETTINGS is blocked under readonly=1 (error 164) and role-attached profiles don't apply. Trade-off: applies to every query through that user.
• Convergence: full re-provision on every login (no drift check, ~320ms), serialised by a global lock (row-policy DROP+CREATE races → ACCESS_ENTITY_ALREADY_EXISTS, code 493). Deny orgs are always re-provisioned too (REVOKE converges).
• Escaping: every literal is single-quote-doubled (King's Lynn); the row policy is the enforcement boundary, so a quote in an admin-entered value must never escape the literal.

The contract fixture — the single evolvable artifact

src/pt_access/ontology-permission-contract.json mirrors sub-site/apps/ui/src/organisations/config/ontology-permission-contract.json (sub-site UI renders exactly what this declares). Adding a new data source = a dataSources entry; a new filter field = a fields entry (+ fieldProducts / compatibility where applicable). Both are fixture changes, never composer code changes. The package CI drift-guard workflow fetches the sub-site copy via gh api and fails on mismatch; tests/test_contract.py (via contract.validate_contract()) checks the code constants against the fixture.

Consumers

Development

uv sync                        # or use any venv with the deps
uv run pytest -q               # 93 tests, no network/DB needed (all mocked)
uv run ruff check src tests
uv run pyright                 # requires pyright install; config in pyproject

Distribution: private GitHub tag, pinned by consumers (pip install git+https://github.com/Point-Topic/point-topic-access@v0.1.0).