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. Ontology app integration: docs/ONTOLOGY_APP_HOOKUP.md. Tracked by onto-app-new#25.

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 sub-site has a vitest drift test against the fixture; the package-side CI drift-guard workflow (fetch the sub-site copy via gh api and fail on mismatch) is planned but not yet created (HANDOFF item 3) — the package-side check today is tests/test_contract.py. fails on mismatch; tests/test_contract.py (via contract.validate_contract()) checks the code constants against the fixture.

Distribution & consumers

Published to PyPI as point-topic-access (current: 0.2.0, 2026-08-10) — pin it like any dependency. The GitHub repo + tags are the source home. (A private git-tag dependency was evaluated first and rejected: a repo's GITHUB_TOKEN cannot read other private repos, so the production box's uv sync couldn't clone it. Public PyPI matches how point-topic-mcp itself is already distributed.)

Secrets injection for consumers — no .env

pt_access reads its credentials from env vars only; it never touches AWS itself. Consumers must not persist those values in a .env file — inject them at process startup with tools/exec-env (installed once per box, e.g. /opt/bin/exec-env):

exec-env pt-access-credentials -- <app launch command>

This fetches the pt-access-credentials JSON secret from AWS Secrets Manager and exports its keys (CLICKHOUSE_*, CLICKHOUSE_PROVISIONING_*, SUB_SITE_MONGODB_URI, …) into the process env — never written to disk. Full design, the box's Roles Anywhere identity, and the staged per-consumer rollout: docs/secrets-injection-plan.md. Behaviour tests: bash tests/test_exec_env.sh.

Exception: jwt-only consumers (e.g. pt-dashboards — imports pt_access.jwt only) need no injection — they use none of the package's env keys (deep dive: Point-Topic/pt-dashboards#2).

Development

uv sync                        # or use any venv with the deps
uv run pytest -q               # 105 tests, no network/DB needed (all mocked)
uv run ruff check src tests

Distribution: uv build && uv publish (credentials via ~/.pypirc, fetched from AWS Secrets Manager pypirc).