8000 Bump opentelemetry-api from 1.41.0 to 1.45.2 by dependabot[bot] · Pull Request #9 · pxfd/mcp-clickhouse · GitHub
[go: up one dir, main page]

Skip to content
Closed
Show file tree
Hide file tree
Changes from all commits
Commits
File filter

Filter by extension

Filter by extension


Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 3 additions & 1 deletion .dockerignore
Original file line number Diff line number Diff line change
Expand Up @@ -69,4 +69,6 @@ Dockerfile
.editorconfig

# Claude AI files
.claude/
.claude/
# Serena AI files
.serena/
2 changes: 2 additions & 0 deletions .gitignore
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
.serena/
mise.toml
.envrc
.ruff_cache/

Expand Down
3 changes: 3 additions & 0 deletions CHANGELOG.md
Original file line number Diff line number Diff line change
Expand Up @@ -4,6 +4,9 @@ All notable changes to this project will be documented in this file.

## Unreleased

### Added
- `CLICKHOUSE_SERVER_INSTRUCTIONS` appends deployment-specific text to the server instructions advertised to MCP clients.

### Changed
- The minimum clickhouse-connect version is now 1.0.0. Locked development and container environments now use clickhouse-connect 1.8.0 and cryptography 50.0.1.
- Local development and README launcher examples now use Python 3.12. CI covers Python 3.10 through 3.14, with Python 3.10 retained as the supported minimum.
Expand Down
44 changes: 30 additions & 14 deletions Dockerfile
10BC8
Original file line number Diff line number Diff line change
Expand Up @@ -4,27 +4,25 @@ FROM ghcr.io/astral-sh/uv:python3.13-bookworm-slim AS builder
# Install the project into `/app`
WORKDIR /app

# Enable bytecode compilation
ENV UV_COMPILE_BYTECODE=1
# Bytecode is not precompiled: it would add ~50 MB to the image. Python compiles
# modules on first import instead, which costs a little extra startup time.
ENV UV_COMPILE_BYTECODE=0

# Copy from the cache instead of linking since it's a mounted volume
ENV UV_LINK_MODE=copy

# Install git and build dependencies for ClickHouse client
RUN --mount=type=cache,target=/var/cache/apt,sharing=locked \
--mount=type=cache,target=/var/lib/apt,sharing=locked \
apt-get update && apt-get install -y --no-install-recommends git build-essential

# Install the project's dependencies using the lockfile and settings
# Install only the dependencies first. This layer is reused until uv.lock changes.
# All locked dependencies ship prebuilt wheels, so no compiler or git is needed.
RUN --mount=type=cache,target=/root/.cache/uv \
--mount=type=bind,source=uv.lock,target=uv.lock \
--mount=type=bind,source=pyproject.toml,target=pyproject.toml \
--mount=type=bind,source=README.md,target=README.md \
uv sync --locked --no-install-project --no-dev

# Then, add the rest of the project source code and install it
# Installing separately from its dependencies allows optimal layer caching
COPY . /app
# Then install the package itself. Only the files needed to build it are copied, so
# changes to start.sh, middlewares or auth do not rebuild the venv.
COPY pyproject.toml uv.lock README.md /app/
COPY mcp_clickhouse /app/mcp_clickhouse
RUN --mount=type=cache,target=/root/.cache/uv \
uv sync --locked --no-dev --no-editable

Expand All @@ -39,11 +37,29 @@ LABEL io.modelcontextprotocol.server.name="io.github.ClickHouse/mcp-clickhouse"
# Set the working directory
WORKDIR /app

# Copy the virtual environment from the builder stage
COPY --from=builder /app/.venv /app/.venv

# Place executables in the environment at the front of the path
ENV PATH="/app/.venv/bin:$PATH"

# No-auth CH default, baked to image
ENV CLICKHOUSE_USER="default"
ENV CLICKHOUSE_PASSWORD=""

# Copy the virtual environment from the builder stage (largest layer, changes least often)
COPY --from=builder /app/.venv /app/.venv

# Small files that change most often go last, so edits only rebuild these layers.
# Entrypoint that optionally sources /secret/secret.env
COPY ./start.sh /app/start.sh

# Copy middleware code
COPY ./middlewares /app/middlewares

# Copy auth providers
COPY ./auth /app/auth

# Server instructions are not baked in: start.sh loads
# /app/instructions/clickhouse_server_instructions.md, mounted from a ConfigMap.

# Run the MCP ClickHouse server by default
ENTRYPOINT ["/app/start.sh"]
CMD ["python", "-m", "mcp_clickhouse.main"]
6 changes: 6 additions & 0 deletions README.md
Original file line number Diff line number Diff line change
Expand Up @@ -688,6 +688,12 @@ These variables control the MCP process itself, including transport, authenticat
* Default: `"10"`
* Increase if your workload requires many concurrent tool calls
* Metadata tools use a separate pool with `min(4, CLICKHOUSE_MCP_MAX_WORKERS)` threads so schema discovery cannot delay queries
* `CLICKHOUSE_SERVER_INSTRUCTIONS`: Extra deployment-specific instructions advertised to MCP clients
* Default: None
* The text is appended after the built-in server instructions. It does not replace them. Leading and trailing whitespace is trimmed, and a blank value is ignored
* Use it to describe your cluster to agents, for example which tables to query, storage tiering, retention, and required filters
* Multi-line values are supported. Quote them in `.env` files. Do not put secrets here, because every connected client receives this text
* Read once at startup
* `CLICKHOUSE_MCP_AUTH_TOKEN`: Static bearer token for HTTP/SSE transports
* Default: None
* One of `CLICKHOUSE_MCP_AUTH_TOKEN`, `FASTMCP_SERVER_AUTH`, or `CLICKHOUSE_MCP_AUTH_DISABLED=true` is **required** for HTTP/SSE transports
Expand Down
103 changes: 103 additions & 0 deletions auth/adfs.py
Original file line number Diff line number Diff line change
@@ -0,0 +1,103 @@
"""ADFS auth provider.

ADFS publishes two issuers in its discovery document:

"issuer": "https://adfs.example.com/adfs" -> ID tokens
"access_token_issuer": "http://adfs.example.com/adfs/services/trust" -> access tokens

OIDCProxy validates the upstream *access* token, but builds its verifier from
`issuer`, so every token is rejected with an issuer mismatch. ADFS does
advertise the right value; `access_token_issuer` is a Microsoft extension
rather than part of OIDC Discovery, so OIDCConfiguration drops it on parse.

This keeps the field and prefers it when building the verifier. Everything
else is inherited. Once FastMCP reads the extension itself, delete this module
and point FASTMCP_SERVER_AUTH back at the stock provider.

Configured with the FASTMCP_SERVER_AUTH_AUTH0_* variables: Auth0Provider is a
generic OIDC provider with no Auth0-specific behavior, only an unlucky name.
"""

from typing import Any

from fastmcp.server.auth.auth import TokenVerifier
from fastmcp.server.auth.oidc_proxy import OIDCConfiguration
from fastmcp.server.auth.providers.auth0 import Auth0Provider
from fastmcp.server.auth.providers.jwt import JWTVerifier
from pydantic import AnyHttpUrl

_AUTH0_PROVIDER_PATH = "fastmcp.server.auth.providers.auth0.Auth0Provider"


def _auth0_kwargs_from_env() -> dict[str, Any]:
"""Build Auth0Provider kwargs from FASTMCP_SERVER_AUTH_AUTH0_* variables.

mcp-clickhouse only maps env variables for the stock providers it knows by
class path; a custom provider is constructed without arguments. This reuses
the server's own Auth0 mapping and parsing, so the variables behave exactly
as they would for the stock Auth0Provider.
"""
# Imported lazily: mcp_server loads this module while it is being set up.
from mcp_clickhouse import mcp_server

prefix, fields = mcp_server._LEGACY_AUTH_PROVIDER_ENV[_AUTH0_PROVIDER_PATH]
file_values = mcp_server._get_legacy_auth_file_values()
kwargs: dict[str, Any] = {}
for field_name in fields:
env_name = f"FASTMCP_SERVER_AUTH_{prefix}{field_name.upper()}"
value = mcp_server._get_legacy_auth_env(env_name, file_values)
if value is None:
continue
kwargs[field_name] = mcp_server._parse_auth_provider_env_value(
_AUTH0_PROVIDER_PATH, field_name, env_name, value
)
if not kwargs.get("required_scopes"):
kwargs["required_scopes"] = mcp_server._LEGACY_AUTH_SCOPE_DEFAULTS[_AUTH0_PROVIDER_PATH]
return kwargs


class AdfsOIDCConfiguration(OIDCConfiguration):
"""OIDC configuration that retains the ADFS access token issuer.

Typed as a plain string, not AnyHttpUrl: the value is an identifier that is
compared verbatim against the token's `iss` claim, never fetched, so URL
normalization could only corrupt it.
"""

access_token_issuer: str | None = None


class PxfdAdfsProvider(Auth0Provider):
"""Auth0Provider that verifies access tokens against access_token_issuer."""

oidc_config: AdfsOIDCConfiguration

def __init__(self, **kwargs: Any):
super().__init__(**(kwargs or _auth0_kwargs_from_env()))

def get_oidc_configuration(
self,
config_url: AnyHttpUrl,
strict: bool | None,
timeout_seconds: int | None,
) -> OIDCConfiguration:
return AdfsOIDCConfiguration.get_oidc_configuration(
config_url, strict=strict, timeout_seconds=timeout_seconds
)

def get_token_verifier(
self,
*,
algorithm: str | None = None,
audience: str | None = None,
required_scopes: list[str] | None = None,
timeout_seconds: int | None = None,
) -> TokenVerifier:
issuer = self.oidc_config.access_token_issuer or self.oidc_config.issuer
return JWTVerifier(
jwks_uri=str(self.oidc_config.jwks_uri),
issuer=str(issuer),
algorithm=algorithm,
audience=audience,
required_scopes=required_scopes,
)
101 changes: 101 additions & 0 deletions instructions/clickhouse_server_instructions.md
FF9B
Original file line number Diff line number Diff line change
@@ -0,0 +1,101 @@
# PXFD ClickHouse log cluster

Read before querying.

## Layout

- Cluster `shared_cluster`. Each `vector_logs_*` database holds:
- `pxfd_distributed_table`: query **this** one. It fans out to every shard.
- `pxfd_local_table`: MergeTree on one shard only. Querying it directly returns partial data.
- `pxfd_log_allkeys_v` (prod, dev): a view that runs `ARRAY JOIN JSONAllPaths(log)`. Very expensive. Use it only with a narrow `idx_name` + `timestamp` filter.
- Databases: `vector_logs_prod`, `vector_logs_dev`, `vector_logs_dwhprod`, `vector_logs_dwhdev`, `vector_logs_ingress`, `vector_logs_gw`, `vector_logs_shared`. The `default` and `hotcold_test` databases are not relevant.
- Columns: `timestamp DateTime64(3,'UTC')`, `idx_name LowCardinality(String)`, `level`, `monolog_level`, `source_type`, `log JSON`. In gw, `level` / `monolog_level` / `source_type` are replaced by `servername`, `method`, `response_code`.
- `idx_name` = log source/app, e.g. `da-prod-server`, `ts3-prod-server`, `applogs.ts3-prod`, `kube_containers`, `login-prod-server`. Find values with a **short** window:

```sql
SELECT idx_name, count() FROM <db>.pxfd_distributed_table
WHERE timestamp >= now() - INTERVAL 15 MINUTE GROUP BY idx_name ORDER BY 2 DESC
```

## Log sources (`idx_name`)

Naming: `<project>-<env>-<type>`, env = `prod` | `dev` | `test` | `staging` | `internal`.

| Type | What it is | Fields |
|---|---|---|
| `*-server` | Backend app logs (PHP/Monolog, k8s). `level` / `monolog_level` columns are filled. | `log.message`, `log.channel`, `log.extra.player_id`, `log.extra.request_id`, `log.extra.url`, `log.extra.client_version`, `log.extra.platform_store`, `log.kubernetes.*` |
| `*-php-error` | PHP errors / php-fpm stderr. `level` is NULL. | `log.message`, `log.kubernetes.*` |
| `*-nginx-access` | Access log of the nginx container in the app pod. | `log.http_method`, `log.http_referer`, `log.http_user_agent`, `log.bytes_in/out`, `log.http_x_forwarded_for`, `log.cf_ray`, `log.client_platform` |
| `*-nginx-error` | Error log of the same nginx. | `log.message` |
| `*-frankenphp` | FrankenPHP logs (low volume). | |
| `applogs.<game>-<env>` | Game **client** logs received over HTTP (`source_type = http_server`). `level` is NULL. | `log.message`, `log.playerId`, `log.deviceId`, `log.clientVersion`, `log.clientStore`, `log.operatingSystem`, `log.logType`, `log.stack`, `log.deviceInfo` |

Project codes:

- Games: `da` (Diggy's Adventure), `da2` (Diggy's Adventure 2), `ts2`/`ts3` (TrainStation 2/3), `tsm` (TrainStation), `sp`/`sp2` (Seaport/Seaport 2), `tr` (Trucks), `ea` (Emporea), `papo` (PostApo), `rnd` (R&D).
- Platform: `login`, `portal`, `pxshop` (PixelShop), `pxbank` (PixelBank), `pxfriends` (PixelFriends), `avatar`, `forum`, `pps`, `pbs`, `hr`, `crm`, `locatool-*` (localization tool), `at`/`atc`.

Per database:

| Database | Contents |
|---|---|
| `vector_logs_prod` | Prod game + platform servers, client applogs, prod cluster infra. Largest sources: `da-prod-server` (~5M rows/hour), `ts3-prod-server`, `ts2-prod-server`, `pxshop-prod-server`, `pxbank-prod-server`. |
| `vector_logs_dev` | Same for `-dev`, `-test`, `-staging`. |
| `vector_logs_ingress` | Edge nginx ingress controller. `nginx-ingress` = access log (~15M rows/hour; access-log fields plus `log.protocol`, `log.nginx_version`, `log.ingress_click`), `nginx-error-ingress` = error log. |
| `vector_logs_gw` | Envoy Gateway. `gw-access` = access log with fields under `log.message.*` (`response_code`, `method`, `duration`, `:authority`, `upstream_cluster`, `route_name`, `response_flags`); `gw-logs` = the gateway's own logs. |
| `vector_logs_dwhprod` / `vector_logs_dwhdev` | DWH platform: `zeppelin-*`, `jupyterhub-*`, `spark-*`, `flink-*`, `airflow-launcher-*`, `datahub-*`, `tagstore-api-*`, `macaw`, `cm-tool-app-*`, `mkt-stats-*`, `dwh-*`, `leaderboards-dev` (dwhdev). `kube_containers` is the biggest (~16M rows/hour in dwhprod). |
| `vector_logs_shared` | Internal tools: `*-definition-tool-*`, `da-xml-editor-*`, `da-world-editor-*`, `crowduck-*`, `slackapp-*`, `jenkins`, `repman`, `fin-*`, `appreviews`, `ptk-*`, `shortener-*`, `phpmyadmin`, `dwh-pkgs`. |

Infra sources (present in each cluster's database):

- `kube_containers`: stdout of every other container without its own index. Filter by `log.kubernetes.pod_namespace` / `pod_name` / `container_name`.
- `events`: Kubernetes events, collected by Grafana Alloy and shipped in OTEL format (`source_type = opentelemetry`). Fields `log.reason`, `kind`, `name`, `namespace`, `msg`.
- journald node logs: `containerd`, `kubelet`, `kernel`, `systemd*`, `apiserver`, `(udev-worker)`, `host-containers@*`, Bottlerocket parts (`pluto`, `sundog`, `shibaken`, `thar-be-settings`, ...).
- Also `aws_cluster_autoscaler`, `node_local_dns`, `coredns`, `warpstream`.
- `static-servers-access` / `static-servers-error`: static nginx servers outside k8s (`source_type = fluent`).
- `unmatched-php`: PHP logs not matched to any project.
- `''`: empty `idx_name` exists in the dwh databases (unassigned logs).

## Storage tiering

All log tables use storage policy `default_s3_express_cache`, which has 3 tiers:

| # | Volume | Disk | Notes |
|---|---|---|---|
| 1 | `default` | `default` | Local disk, fastest. |
| 2 | `s3_express_cache` | `s3_express` | S3 Express One Zone: remote and slower than local. |
| 3 | `s3_cache` | `s3_cold` | Standard S3, slowest. `prefer_not_to_merge=1`, so parts stay small and many, meaning many S3 requests per query. |

- TTL moves parts to `s3_cold` after the hot period below.
- `move_factor=0.01`: when the local disk is nearly full, parts spill to `s3_express` even inside the hot period. So "recent" does not guarantee "local". The newest ~1-3 days are the only reliably fast range.
- Rough share of data on each tier (node1), local / s3_express / s3_cold: prod 385 GiB / 362 GiB / 16.6 TiB; ingress 73 GiB / 800 GiB / 2.4 TiB; dev 21 GiB / 0 / 438 GiB; dwhprod 28 GiB / 78 GiB / 108 GiB (58k tiny parts on s3_cold, so cold queries are slow even though the data is small). In short, well over 90% of the data lives on S3.

| Database | Hot period | Then `s3_cold` until | Deleted after |
|---|---|---|---|
| `vector_logs_prod` | 8 days | 120 days | 120 days |
| `vector_logs_shared` | 8 days | 120 days | 120 days |
| `vector_logs_dev` | 8 days | 90 days | 90 days |
| `vector_logs_dwhprod` | 8 days | 90 days | 90 days |
| `vector_logs_dwhdev` | 8 days | 90 days | 90 days |
| `vector_logs_gw` | 5 days | 120 days | 120 days |
| `vector_logs_ingress` | 5 days | 60 days | 60 days |

- Anything older than the hot period is read from standard S3, which is much slower than local disk.
- Data older than the retention period no longer exists. Do not query it.
- Volume (compressed, per node): prod ~19 TB / 21B rows (~110 TB uncompressed), ingress ~3.6 TB, dwhprod ~0.6 TB, dev ~0.5 TB, gw/shared/dwhdev < 30 GB. A full scan of prod is never acceptable.

## Query rules

1. A `timestamp` filter in WHERE is mandatory (the server rejects queries without one). This also blocks `system.*` tables, so use `list_databases` / `list_tables` for metadata. Start with the smallest window that answers the question: minutes or hours, not days. Widen only if needed.
2. Default to the most recent data (hours up to ~1-3 days). Stay within the hot period (5-8 days, see table) unless the user explicitly needs older data. When going to S3, say so and state the time range.
3. Always filter `idx_name` with `=` or `IN`. ORDER BY is `(idx_name, timestamp)` and partitions are daily (`toYYYYMMDD(timestamp)`), so `idx_name` + `timestamp` gives both index and partition pruning. Without `idx_name`, every source in the range is scanned.
4. Use sargable bounds on the raw column: `timestamp >= X AND timestamp < Y`. Do not wrap it (e.g. `toDate(timestamp) = ...`).
5. Never `SELECT *` and never select the whole `log` column over large ranges. Read specific JSON subcolumns, e.g. `log.message`, `log.extra.player_id`, `log.playerId` (cast if needed: `log.x::String`). Only the columns you read are fetched, which matters most on S3.
6. Always add `LIMIT` when returning rows. Prefer aggregates (`count`, `uniq`, `topK`, `groupArray(…)` with `LIMIT`) over raw rows. Run `count()` first to size a query before pulling rows.
7. Full-text matching (`LIKE` / `ILIKE` / `match` / `hasToken` on log strings) cannot use the index. Keep it to small windows and a specific `idx_name`. Filter on `level` / `monolog_level` / `source_type` first when possible.
8. For S3-range questions (e.g. "last 2 months"), check a single day first, then extend. Split long ranges into chunks (per day or week) and aggregate per chunk instead of one huge scan. Avoid `ORDER BY timestamp` over wide ranges without a tight `LIMIT`.
9. Avoid JOINs and subqueries that re-scan the log tables. Do not use the allkeys view for discovery over more than ~1 hour. Sample keys from a few rows instead: `SELECT JSONAllPaths(log) … LIMIT 100`.
10. Queries are read-only. Do not try to bypass the query validator. If a query is rejected, tell the user and propose a narrower time window or filter.
11. `level` is inconsistent: mostly uppercase (`INFO`, `ERROR`), lowercase in `kube_containers` / `kubelet` (`info`, `error`), and numeric Monolog strings (`"100"`, `"200"`) with `monolog_level = 0` in `forum`, `pbs`, `ptk`, `tsm-dev`. For errors use `upper(level) IN ('ERROR','CRITICAL','ALERT','EMERGENCY')` or `monolog_level >= 400`, whichever fits the source. `level` is NULL on `php-error`, `nginx-*`, `applogs.*` and most journald sources (`kubelet` is an exception): filter on `log.message` there.
12. `vector_logs_gw`: do not use the `servername` / `method` / `response_code` columns. Aggregating them fails with `CANNOT_CONVERT_TYPE` (code 70), even wrapped in `toString()`, because the distributed and local tables have different column types, and they are filled in only a few % of `gw-access` rows anyway. Use the JSON fields: `log.message.response_code`, `log.message.method`, `log.message.duration`, ``log.message.`:authority` ``, `log.message.upstream_cluster`.
13. Field paths differ by source type: `*-server` uses `log.extra.*` (`log.extra.player_id`), `applogs.*` uses top-level camelCase (`log.playerId`). Check the source type before choosing paths.
Loading
0