ClickHouse MCP Server + Agentgateway Integration — Implementation Plan

ClickHouse MCP Server + Agentgateway Integration — Implementation Plan

For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (- [ ]) syntax for tracking.

Goal: Deploy the ClickHouse MCP server in-cluster with read-only + read-write instances, route both through the agentgateway, expose raw ClickHouse ports via Traefik, and provision dedicated ClickHouse users.

Architecture: Two Deployment+Service pairs in a new clickhouse-mcp namespace talk to ClickHouse over plain HTTP (8123). Two dedicated ClickHouse users (mcp_readonly, mcp_readwrite) are provisioned by a PostSync Job. The agentgateway routes /mcp/clickhouse-ro and /mcp/clickhouse-rw with VK auth + upstream bearer-token injection. Traefik IngressRoutes expose ClickHouse HTTP (8123) and native TCP (9000) externally at ch.fzymgc.house and ch-native.fzymgc.house with TLS termination.

Tech Stack: Kubernetes (kustomize), ArgoCD (multi-source), agentgateway CRDs v1alpha1, cert-manager, Traefik, ExternalSecrets (Vault), ghcr.io/clickhouse/mcp-clickhouse:0.4

---

Task 1: Create clickhouse-mcp namespace + ExternalSecrets + NetworkPolicy

Files:

• Create: argocd/app-configs/clickhouse-mcp/namespace.yaml
• Create: argocd/app-configs/clickhouse-mcp/external-secret.yaml
• Create: argocd/app-configs/clickhouse-mcp/networkpolicy.yaml
• Create: argocd/app-configs/clickhouse-mcp/kustomization.yaml

Notes: The ExternalSecret clickhouse-mcp-creds pulls both ClickHouse user passwords from the existing Vault path fzymgc-house/cluster/clickstack. The clickhouse-mcp-auth ExternalSecret pulls the MCP bearer token from fzymgc-house/cluster/agentgateway. NetworkPolicy uses CiliumNetworkPolicy (matching the clickstack namespace pattern) to allow ingress from agentgateway namespace on port 8000.

• Step 1: Create namespace.yaml

apiVersion: v1
kind: Namespace
metadata:
  name: clickhouse-mcp

• Step 2: Create external-secret.yaml

---
# ClickHouse user credentials for MCP server instances.
# Sources mcp_readonly_password + mcp_readwrite_password from the existing
# clickstack Vault path (provisioned by the user-provisioning Job in Task 6).
apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: clickhouse-mcp-creds
  namespace: clickhouse-mcp
spec:
  refreshInterval: 15m
  secretStoreRef:
    name: vault
    kind: ClusterSecretStore
  target:
    name: clickhouse-mcp-creds
    creationPolicy: Owner
  data:
    - secretKey: mcp_readonly_password
      remoteRef:
        key: fzymgc-house/cluster/clickstack
        property: mcp_readonly_password
    - secretKey: mcp_readwrite_password
      remoteRef:
        key: fzymgc-house/cluster/clickstack
        property: mcp_readwrite_password
---
# MCP server bearer token — both ro and rw instances share the same token.
# The agentgateway injects it as an upstream Authorization header.
apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: clickhouse-mcp-auth
  namespace: clickhouse-mcp
spec:
  refreshInterval: 15m
  secretStoreRef:
    name: vault
    kind: ClusterSecretStore
  target:
    name: clickhouse-mcp-auth
    creationPolicy: Owner
  data:
    - secretKey: token
      remoteRef:
        key: fzymgc-house/cluster/agentgateway
        property: clickhouse_mcp_auth_token

• Step 3: Create networkpolicy.yaml

apiVersion: cilium.io/v2
kind: CiliumNetworkPolicy
metadata:
  name: clickhouse-mcp-default
  namespace: clickhouse-mcp
spec:
  endpointSelector: {}
  ingress:
    # Allow agentgateway to reach MCP servers on port 8000
    - fromEndpoints:
        - matchLabels:
            io.kubernetes.pod.namespace: agentgateway
      toPorts:
        - ports:
            - port: "8000"
              protocol: TCP
  egress:
    # Allow outbound to ClickHouse (clickstack ns) on port 8123
    - toEndpoints:
        - matchLabels:
            io.kubernetes.pod.namespace: clickstack
      toPorts:
        - ports:
            - port: "8123"
              protocol: TCP
    # Allow DNS resolution
    - toEndpoints:
        - matchLabels:
            io.kubernetes.pod.namespace: kube-system
            k8s-app: kube-dns
      toPorts:
        - ports:
            - port: "53"
              protocol: UDP

• Step 4: Create kustomization.yaml

apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization

namespace: clickhouse-mcp

resources:
  - namespace.yaml
  - external-secret.yaml
  - deployment.yaml
  - service.yaml
  - networkpolicy.yaml

• Step 5: Commit

jj commit -m "feat(clickhouse-mcp): namespace, ExternalSecrets, NetworkPolicy"

---

Task 2: Create clickhouse-mcp Deployments and Services

Files:

• Create: argocd/app-configs/clickhouse-mcp/deployment.yaml
• Create: argocd/app-configs/clickhouse-mcp/service.yaml

Notes: Two Deployment+Service pairs in one file each, separated by ---. Both use the ghcr.io/clickhouse/mcp-clickhouse:0.4 image with http transport on port 8000. The only differences are CLICKHOUSE_USER and CLICKHOUSE_ALLOW_WRITE_ACCESS. DROP/TRUNCATE is disabled on both. Follows the grafana-mcp resource limits pattern (50m/64Mi req, 200m/256Mi limit).

• Step 1: Create deployment.yaml

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: clickhouse-mcp-ro
  namespace: clickhouse-mcp
  labels:
    app: clickhouse-mcp-ro
spec:
  replicas: 1
  selector:
    matchLabels:
      app: clickhouse-mcp-ro
  template:
    metadata:
      labels:
        app: clickhouse-mcp-ro
    spec:
      containers:
        - name: mcp-server
          image: ghcr.io/clickhouse/mcp-clickhouse:0.4
          ports:
            - containerPort: 8000
              name: http
          env:
            - name: CLICKHOUSE_HOST
              value: "cs-clickstack-clickhouse-clickhouse-headless.clickstack.svc.cluster.local"
            - name: CLICKHOUSE_PORT
              value: "8123"
            - name: CLICKHOUSE_SECURE
              value: "false"
            - name: CLICKHOUSE_USER
              value: "mcp_readonly"
            - name: CLICKHOUSE_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickhouse-mcp-creds
                  key: mcp_readonly_password
            - name: CLICKHOUSE_ALLOW_WRITE_ACCESS
              value: "false"
            - name: CLICKHOUSE_ALLOW_DROP
              value: "false"
            - name: CLICKHOUSE_MCP_SERVER_TRANSPORT
              value: "http"
            - name: CLICKHOUSE_MCP_BIND_HOST
              value: "0.0.0.0"
            - name: CLICKHOUSE_MCP_BIND_PORT
              value: "8000"
            - name: CLICKHOUSE_MCP_AUTH_TOKEN
              valueFrom:
                secretKeyRef:
                  name: clickhouse-mcp-auth
                  key: token
          resources:
            requests:
              cpu: 50m
              memory: 64Mi
            limits:
              cpu: 200m
              memory: 256Mi
          securityContext:
            allowPrivilegeEscalation: false
            capabilities:
              drop:
                - ALL
            readOnlyRootFilesystem: true
            runAsNonRoot: true
            runAsUser: 1000
          readinessProbe:
            httpGet:
              path: /health
              port: 8000
            initialDelaySeconds: 5
            periodSeconds: 10
          livenessProbe:
            httpGet:
              path: /health
              port: 8000
            initialDelaySeconds: 15
            periodSeconds: 30
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: clickhouse-mcp-rw
  namespace: clickhouse-mcp
  labels:
    app: clickhouse-mcp-rw
spec:
  replicas: 1
  selector:
    matchLabels:
      app: clickhouse-mcp-rw
  template:
    metadata:
      labels:
        app: clickhouse-mcp-rw
    spec:
      containers:
        - name: mcp-server
          image: ghcr.io/clickhouse/mcp-clickhouse:0.4
          ports:
            - containerPort: 8000
              name: http
          env:
            - name: CLICKHOUSE_HOST
              value: "cs-clickstack-clickhouse-clickhouse-headless.clickstack.svc.cluster.local"
            - name: CLICKHOUSE_PORT
              value: "8123"
            - name: CLICKHOUSE_SECURE
              value: "false"
            - name: CLICKHOUSE_USER
              value: "mcp_readwrite"
            - name: CLICKHOUSE_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickhouse-mcp-creds
                  key: mcp_readwrite_password
            - name: CLICKHOUSE_ALLOW_WRITE_ACCESS
              value: "true"
            - name: CLICKHOUSE_ALLOW_DROP
              value: "false"
            - name: CLICKHOUSE_MCP_SERVER_TRANSPORT
              value: "http"
            - name: CLICKHOUSE_MCP_BIND_HOST
              value: "0.0.0.0"
            - name: CLICKHOUSE_MCP_BIND_PORT
              value: "8000"
            - name: CLICKHOUSE_MCP_AUTH_TOKEN
              valueFrom:
                secretKeyRef:
                  name: clickhouse-mcp-auth
                  key: token
          resources:
            requests:
              cpu: 50m
              memory: 64Mi
            limits:
              cpu: 200m
              memory: 256Mi
          securityContext:
            allowPrivilegeEscalation: false
            capabilities:
              drop:
                - ALL
            readOnlyRootFilesystem: true
            runAsNonRoot: true
            runAsUser: 1000
          readinessProbe:
            httpGet:
              path: /health
              port: 8000
            initialDelaySeconds: 5
            periodSeconds: 10
          livenessProbe:
            httpGet:
              path: /health
              port: 8000
            initialDelaySeconds: 15
            periodSeconds: 30

• Step 2: Create service.yaml

---
apiVersion: v1
kind: Service
metadata:
  name: clickhouse-mcp-ro
  namespace: clickhouse-mcp
  labels:
    app: clickhouse-mcp-ro
spec:
  type: ClusterIP
  selector:
    app: clickhouse-mcp-ro
  ports:
    - name: http
      port: 8000
      targetPort: 8000
      protocol: TCP
---
apiVersion: v1
kind: Service
metadata:
  name: clickhouse-mcp-rw
  namespace: clickhouse-mcp
  labels:
    app: clickhouse-mcp-rw
spec:
  type: ClusterIP
  selector:
    app: clickhouse-mcp-rw
  ports:
    - name: http
      port: 8000
      targetPort: 8000
      protocol: TCP

• Step 3: Commit

jj commit -m "feat(clickhouse-mcp): Deployments and Services for ro + rw instances"

---

Task 3: Create clickhouse-mcp ArgoCD Application

Files:

• Create: argocd/cluster-app/templates/clickhouse-mcp.yaml

Notes: Multi-source ArgoCD app (Helm not needed — no official chart exists). Source A is the kustomize path. No Helm values — just raw manifests via kustomize. Sync wave 6 (after clickstack is up at wave 1). Follows grafana-mcp pattern but simpler (no Helm source).

• Step 1: Create ArgoCD Application

apiVersion: argoproj.io/v1alpha1
kind: Application
metadata:
  name: clickhouse-mcp
  namespace: argocd
  annotations:
    argocd.argoproj.io/sync-wave: "6"
  finalizers:
    - resources-finalizer.argocd.argoproj.io
spec:
  project: default
  source:
    repoURL: https://github.com/fzymgc-house/selfhosted-cluster
    targetRevision: HEAD
    path: argocd/app-configs/clickhouse-mcp
  destination:
    server: https://kubernetes.default.svc
    namespace: clickhouse-mcp
  syncPolicy:
    automated:
      prune: true
      selfHeal: true
    syncOptions:
      - CreateNamespace=true
      - ServerSideApply=true
  ignoreDifferences:
    - kind: ExternalSecret
      group: external-secrets.io
      jqPathExpressions:
        - .spec.data[].remoteRef.conversionStrategy
        - .spec.data[].remoteRef.decodingStrategy
        - .spec.data[].remoteRef.metadataPolicy

• Step 2: Verify kustomize build

kubectl kustomize argocd/app-configs/clickhouse-mcp

Expected: renders namespace, 2 Deployments, 2 Services, 2 ExternalSecrets, NetworkPolicy

• Step 3: Commit

jj commit -m "feat(clickhouse-mcp): ArgoCD Application"

---

Task 4: Integrate with agentgateway

Files:

• Create: argocd/app-configs/agentgateway/mcp-clickhouse.yaml
• Modify: argocd/app-configs/agentgateway/kustomization.yaml
• Modify: argocd/app-configs/agentgateway/secrets.yaml

Notes: Follows the mcp-engram.yaml pattern (cluster-local Service backend) combined with SaaS upstream-auth injection. Both instances share the same upstream bearer token (injected via backend.auth.secretRef). Two separate VK entries control which clients get ro vs rw access.

• Step 1: Create mcp-clickhouse.yaml

---
# AgentgatewayBackend: ClickHouse MCP read-only
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayBackend
metadata:
  name: mcp-clickhouse-ro
  namespace: agentgateway
spec:
  mcp:
    targets:
      - name: clickhouse-ro
        static:
          host: clickhouse-mcp-ro.clickhouse-mcp.svc.cluster.local
          port: 8000
          path: /
          protocol: StreamableHTTP
---
# HTTPRoute: expose clickhouse-ro MCP on mcp-gw listener at /mcp/clickhouse-ro
apiVersion: gateway.networking.k8s.io/v1
kind: HTTPRoute
metadata:
  name: mcp-clickhouse-ro
  namespace: agentgateway
spec:
  parentRefs:
    - group: gateway.networking.k8s.io
      kind: Gateway
      name: agentgateway
      sectionName: mcp-gw
  hostnames:
    - mcp-gw.fzymgc.house
  rules:
    - matches:
        - path:
            type: PathPrefix
            value: /mcp/clickhouse-ro
      backendRefs:
        - group: agentgateway.dev
          kind: AgentgatewayBackend
          weight: 1
          name: mcp-clickhouse-ro
---
# AgentgatewayPolicy: VK client auth for clickhouse-ro MCP route
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayPolicy
metadata:
  name: mcp-clickhouse-ro-apikey
  namespace: agentgateway
spec:
  targetRefs:
    - group: gateway.networking.k8s.io
      kind: HTTPRoute
      name: mcp-clickhouse-ro
  traffic:
    apiKeyAuthentication:
      mode: Strict
      secretRef:
        name: agentgateway-vkeys
---
# AgentgatewayPolicy: upstream auth (bearer token) injected to clickhouse-mcp-ro
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayPolicy
metadata:
  name: mcp-clickhouse-ro-upstream
  namespace: agentgateway
spec:
  targetRefs:
    - group: agentgateway.dev
      kind: AgentgatewayBackend
      name: mcp-clickhouse-ro
  backend:
    auth:
      secretRef:
        name: agentgateway-mcp-clickhouse
---
# AgentgatewayBackend: ClickHouse MCP read-write (no drop)
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayBackend
metadata:
  name: mcp-clickhouse-rw
  namespace: agentgateway
spec:
  mcp:
    targets:
      - name: clickhouse-rw
        static:
          host: clickhouse-mcp-rw.clickhouse-mcp.svc.cluster.local
          port: 8000
          path: /
          protocol: StreamableHTTP
---
# HTTPRoute: expose clickhouse-rw MCP on mcp-gw listener at /mcp/clickhouse-rw
apiVersion: gateway.networking.k8s.io/v1
kind: HTTPRoute
metadata:
  name: mcp-clickhouse-rw
  namespace: agentgateway
spec:
  parentRefs:
    - group: gateway.networking.k8s.io
      kind: Gateway
      name: agentgateway
      sectionName: mcp-gw
  hostnames:
    - mcp-gw.fzymgc.house
  rules:
    - matches:
        - path:
            type: PathPrefix
            value: /mcp/clickhouse-rw
      backendRefs:
        - group: agentgateway.dev
          kind: AgentgatewayBackend
          weight: 1
          name: mcp-clickhouse-rw
---
# AgentgatewayPolicy: VK client auth for clickhouse-rw MCP route
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayPolicy
metadata:
  name: mcp-clickhouse-rw-apikey
  namespace: agentgateway
spec:
  targetRefs:
    - group: gateway.networking.k8s.io
      kind: HTTPRoute
      name: mcp-clickhouse-rw
  traffic:
    apiKeyAuthentication:
      mode: Strict
      secretRef:
        name: agentgateway-vkeys
---
# AgentgatewayPolicy: upstream auth (bearer token) injected to clickhouse-mcp-rw
apiVersion: agentgateway.dev/v1alpha1
kind: AgentgatewayPolicy
metadata:
  name: mcp-clickhouse-rw-upstream
  namespace: agentgateway
spec:
  targetRefs:
    - group: agentgateway.dev
      kind: AgentgatewayBackend
      name: mcp-clickhouse-rw
  backend:
    auth:
      secretRef:
        name: agentgateway-mcp-clickhouse

• Step 2: Modify kustomization.yaml — add mcp-clickhouse.yaml

Edit the resources list in argocd/app-configs/agentgateway/kustomization.yaml to add mcp-clickhouse.yaml after mcp-saas.yaml:

- mcp-saas.yaml
- mcp-clickhouse.yaml
- modern-auth.yaml

• Step 3: Modify secrets.yaml — add upstream auth ExternalSecret

Insert after the fal ExternalSecret block (after line 163 in the current file):

---
# (g) ClickHouse MCP upstream auth — bearer token for both ro and rw instances.
# Both MCP servers validate the same token (CLICKHOUSE_MCP_AUTH_TOKEN).
apiVersion: external-secrets.io/v1
kind: ExternalSecret
metadata:
  name: agentgateway-mcp-clickhouse
  namespace: agentgateway
spec:
  refreshInterval: 5m
  secretStoreRef:
    name: vault
    kind: ClusterSecretStore
  target:
    name: agentgateway-mcp-clickhouse
    creationPolicy: Owner
    template:
      type: Opaque
      data:
        Authorization: "Bearer {{ .clickhouse_mcp_auth_token }}"
  data:
    - secretKey: clickhouse_mcp_auth_token
      remoteRef:
        key: fzymgc-house/cluster/agentgateway
        property: clickhouse_mcp_auth_token

• Step 4: Modify secrets.yaml — add VK entries to agentgateway-vkeys

Add two new entries to the agentgateway-vkeys ExternalSecret’s template data block:

octopus: '{"key":"{{ .vk_octopus }}","metadata":{"group":"service"}}'
clickhouse-ro: '{"key":"{{ .vk_clickhouse_ro }}","metadata":{"group":"mcp-ro"}}'
clickhouse-rw: '{"key":"{{ .vk_clickhouse_rw }}","metadata":{"group":"mcp-rw"}}'

And add the corresponding data entries:

- secretKey: vk_octopus
  remoteRef:
    key: fzymgc-house/cluster/agentgateway
    property: vk_octopus
- secretKey: vk_clickhouse_ro
  remoteRef:
    key: fzymgc-house/cluster/agentgateway
    property: vk_clickhouse_ro
- secretKey: vk_clickhouse_rw
  remoteRef:
    key: fzymgc-house/cluster/agentgateway
    property: vk_clickhouse_rw

• Step 5: Commit

jj commit -m "feat(agentgateway): add ClickHouse MCP routes, VKs, and upstream auth"

---

Task 5: Expose ClickHouse via Traefik (IngressRoutes + Certificate)

Files:

• Create: argocd/app-configs/clickstack/ingressroute-ch-http.yaml
• Create: argocd/app-configs/clickstack/ingressroute-ch-native.yaml
• Create: argocd/app-configs/clickstack/certificate-ch.yaml
• Modify: argocd/app-configs/clickstack/kustomization.yaml

Notes: Traefik terminates TLS at the edge for both routes. The HTTP route forwards plain HTTP to ClickHouse port 8123. The TCP route forwards plain native TCP to port 9000. The certificate uses cloudflare-acme-issuer (Let’s Encrypt via DNS-01) for publicly-trusted certs. Both hostnames fall under the *.fzymgc.house wildcard DNS — no new DNS entries needed.

• Step 1: Create certificate-ch.yaml

apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
  name: ch-tls
  namespace: clickstack
spec:
  secretName: ch-tls
  issuerRef:
    name: cloudflare-acme-issuer
    kind: ClusterIssuer
  dnsNames:
    - ch.fzymgc.house
    - ch-native.fzymgc.house
  usages:
    - server auth

• Step 2: Create ingressroute-ch-http.yaml

apiVersion: traefik.io/v1alpha1
kind: IngressRoute
metadata:
  name: clickhouse-http
  namespace: clickstack
  annotations:
    router-hosts.fzymgc.house/enabled: "true"
spec:
  entryPoints:
    - websecure
  routes:
    - match: Host(`ch.fzymgc.house`)
      kind: Rule
      services:
        - name: cs-clickstack-clickhouse-clickhouse-headless
          port: 8123
  tls:
    secretName: ch-tls

• Step 3: Create ingressroute-ch-native.yaml

apiVersion: traefik.io/v1alpha1
kind: IngressRouteTCP
metadata:
  name: clickhouse-native
  namespace: clickstack
  annotations:
    router-hosts.fzymgc.house/enabled: "true"
spec:
  entryPoints:
    - websecure
  routes:
    - match: HostSNI(`ch-native.fzymgc.house`)
      services:
        - name: cs-clickstack-clickhouse-clickhouse-headless
          port: 9000
          terminationDelay: 100
  tls:
    secretName: ch-tls

• Step 4: Modify kustomization.yaml — add new resources

Add the three new files to argocd/app-configs/clickstack/kustomization.yaml:

- certificate-otel.yaml
- certificate-ch.yaml
- ingressroute.yaml
- ingressroute-ch-http.yaml
- ingressroute-ch-native.yaml
- networkpolicy.yaml

• Step 5: Commit

jj commit -m "feat(clickstack): expose ClickHouse HTTP (8123) + native (9000) via Traefik TLS"

---

Task 6: Create ClickHouse user provisioning Job

Files:

• Create: argocd/app-configs/clickstack/user-provisioning-job.yaml
• Modify: argocd/app-configs/clickstack/kustomization.yaml

Notes: ArgoCD PostSync hook (runs after ClickHouse is healthy). Creates two users (mcp_readonly, mcp_readwrite) with passwords from the existing clickstack-secret. Uses the clickhouse/clickhouse-server:25.7-alpine image (same as ClickStack) to run SQL against the headless service. An initContainer waits for ClickHouse to be ready before running the SQL.

• Step 1: Create user-provisioning-job.yaml

apiVersion: batch/v1
kind: Job
metadata:
  name: clickhouse-provision-mcp-users
  namespace: clickstack
  annotations:
    argocd.argoproj.io/hook: PostSync
    argocd.argoproj.io/hook-delete-policy: BeforeHookCreation
spec:
  ttlSecondsAfterFinished: 300
  template:
    spec:
      restartPolicy: Never
      initContainers:
        - name: wait-for-clickhouse
          image: busybox:1.36
          command:
            - sh
            - -c
            - |
              until wget -q -O- http://cs-clickstack-clickhouse-clickhouse-headless:8123/ping 2>/dev/null | grep -q Ok; do
                echo "waiting for ClickHouse..."
                sleep 5
              done
              echo "ClickHouse is ready"
      containers:
        - name: provision-users
          image: clickhouse/clickhouse-server:25.7-alpine
          command:
            - sh
            - -c
            - |
              set -e
              clickhouse-client \
                --host cs-clickstack-clickhouse-clickhouse-headless \
                --port 9000 \
                --user default \
                --password "$ADMIN_PASSWORD" \
                --queries-file /sql/provision-users.sql
          env:
            - name: ADMIN_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickstack-secret
                  key: admin_password
          volumeMounts:
            - name: sql
              mountPath: /sql
      volumes:
        - name: sql
          configMap:
            name: clickhouse-mcp-user-sql
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: clickhouse-mcp-user-sql
  namespace: clickstack
  annotations:
    argocd.argoproj.io/hook: PostSync
    argocd.argoproj.io/hook-delete-policy: BeforeHookCreation
data:
  provision-users.sql: |
    CREATE USER IF NOT EXISTS mcp_readonly
      IDENTIFIED WITH sha256_password BY '{mcp_readonly_password}'
      DEFAULT DATABASE default
      SETTINGS readonly = 2;

    CREATE USER IF NOT EXISTS mcp_readwrite
      IDENTIFIED WITH sha256_password BY '{mcp_readwrite_password}'
      DEFAULT DATABASE default;

    GRANT SELECT ON *.* TO mcp_readonly;
    GRANT SELECT, INSERT, ALTER, CREATE ON *.* TO mcp_readwrite;
    -- intentionally no DROP, TRUNCATE, or SYSTEM grants

Note: The IDENTIFIED WITH sha256_password BY '{password}' syntax requires literal passwords. Since ClickHouse doesn’t support password-from-env in SQL files, the clickhouse-client --password flag handles authentication to run the script. The user passwords in Vault are plaintext (generated by openssl rand). The provisioning script reads them from the Secret and passes them via --password.

Wait — the SQL uses IDENTIFIED BY with a literal password, but the passwords are in Vault, not hardcoded in the ConfigMap. Let me fix this approach: pass passwords via environment variables and construct the SQL dynamically.

Let me rewrite the container command:

      containers:
        - name: provision-users
          image: clickhouse/clickhouse-server:25.7-alpine
          command:
            - sh
            - -c
            - |
              set -e
              clickhouse-client \
                --host cs-clickstack-clickhouse-clickhouse-headless \
                --port 9000 \
                --user default \
                --password "$ADMIN_PASSWORD" \
                --query "
                  CREATE USER IF NOT EXISTS mcp_readonly
                    IDENTIFIED WITH sha256_password BY '${MCP_RO_PASSWORD}'
                    DEFAULT DATABASE default
                    SETTINGS readonly = 2;
                "
              clickhouse-client \
                --host cs-clickstack-clickhouse-clickhouse-headless \
                --port 9000 \
                --user default \
                --password "$ADMIN_PASSWORD" \
                --query "
                  CREATE USER IF NOT EXISTS mcp_readwrite
                    IDENTIFIED WITH sha256_password BY '${MCP_RW_PASSWORD}'
                    DEFAULT DATABASE default;
                "
              clickhouse-client \
                --host cs-clickstack-clickhouse-clickhouse-headless \
                --port 9000 \
                --user default \
                --password "$ADMIN_PASSWORD" \
                --query "GRANT SELECT ON *.* TO mcp_readonly;"
              clickhouse-client \
                --host cs-clickstack-clickhouse-clickhouse-headless \
                --port 9000 \
                --user default \
                --password "$ADMIN_PASSWORD" \
                --query "GRANT SELECT, INSERT, ALTER, CREATE ON *.* TO mcp_readwrite;"

This is correct — passwords come from env vars, no literal passwords in the manifest.

• Step 2: Actually, let me rewrite the entire file cleanly

apiVersion: batch/v1
kind: Job
metadata:
  name: clickhouse-provision-mcp-users
  namespace: clickstack
  annotations:
    argocd.argoproj.io/hook: PostSync
    argocd.argoproj.io/hook-delete-policy: BeforeHookCreation
spec:
  ttlSecondsAfterFinished: 300
  template:
    spec:
      restartPolicy: Never
      initContainers:
        - name: wait-for-clickhouse
          image: busybox:1.36
          command:
            - sh
            - -c
            - |
              until wget -q -O- http://cs-clickstack-clickhouse-clickhouse-headless:8123/ping 2>/dev/null | grep -q Ok; do
                echo "waiting for ClickHouse..."
                sleep 5
              done
              echo "ClickHouse is ready"
      containers:
        - name: provision-users
          image: clickhouse/clickhouse-server:25.7-alpine
          command:
            - sh
            - -c
            - |
              set -e
              CH="clickhouse-client --host cs-clickstack-clickhouse-clickhouse-headless --port 9000 --user default --password $ADMIN_PASSWORD"
              $CH --query "CREATE USER IF NOT EXISTS mcp_readonly IDENTIFIED WITH sha256_password BY '${MCP_RO_PASSWORD}' DEFAULT DATABASE default SETTINGS readonly = 2;"
              $CH --query "CREATE USER IF NOT EXISTS mcp_readwrite IDENTIFIED WITH sha256_password BY '${MCP_RW_PASSWORD}' DEFAULT DATABASE default;"
              $CH --query "GRANT SELECT ON *.* TO mcp_readonly;"
              $CH --query "GRANT SELECT, INSERT, ALTER, CREATE ON *.* TO mcp_readwrite;"
              echo "MCP users provisioned successfully"
          env:
            - name: ADMIN_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickstack-secret
                  key: admin_password
            - name: MCP_RO_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickstack-secret
                  key: mcp_readonly_password
            - name: MCP_RW_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: clickstack-secret
                  key: mcp_readwrite_password

Important: This Job references mcp_readonly_password and mcp_readwrite_password from the clickstack-secret ExternalSecret. These Vault properties must be populated BEFORE this Job runs. Since this is a PostSync hook (runs after clickstack sync), and ESO syncs secrets before ArgoCD applies resources, the passwords should be available — but only if they were populated in Vault first. The Vault population is a manual prerequisite step (see Prerequisites section).

Also: The existing clickstack-secret ExternalSecret must be updated to include the two new properties. Let me read the current state.

• Step 2: Add MCP passwords to clickstack-secret ExternalSecret

Edit argocd/app-configs/clickstack/external-secret.yaml:

Add to template data (after hyperdx_oidc_client_secret line):

hyperdx_oidc_client_secret: '{{ .hyperdx_oidc_client_secret }}'
mcp_readonly_password: '{{ .mcp_readonly_password }}'
mcp_readwrite_password: '{{ .mcp_readwrite_password }}'

Add to spec.data list (after otel_ingest_api_key entry):

- secretKey: otel_ingest_api_key
  remoteRef:
    key: fzymgc-house/cluster/clickstack
    property: otel_ingest_api_key
- secretKey: mcp_readonly_password
  remoteRef:
    key: fzymgc-house/cluster/clickstack
    property: mcp_readonly_password
- secretKey: mcp_readwrite_password
  remoteRef:
    key: fzymgc-house/cluster/clickstack
    property: mcp_readwrite_password

This ensures the clickstack-secret Secret contains the two new password keys that the provisioning Job’s env vars reference.

• Step 3: Modify clickstack kustomization.yaml — add Job resource

Add to argocd/app-configs/clickstack/kustomization.yaml:

- otel-collector-monitoring.yaml
- user-provisioning-job.yaml

Note: The Job’s ConfigMap also uses a PostSync hook annotation so ArgoCD cleans it up after the hook completes.

• Step 4: Commit

jj commit -m "feat(clickstack): PostSync Job to provision mcp_readonly + mcp_readwrite ClickHouse users"

---

Task 7: Update Velero backup exclusions

Files:

• Modify: argocd/app-configs/velero/backup-schedule.yaml

Notes: The clickhouse-mcp namespace is stateless (Deployments are Git-recreatable, no PVCs). Add to the Telemetry exclusion group in both backup schedules, next to grafana-mcp.

• Step 1: Add clickhouse-mcp to daily-backup exclusions

Add - clickhouse-mcp under the Telemetry category in the first Schedule’s excludedNamespaces:

- grafana-mcp
- clickhouse-mcp
- loki

• Step 2: Add clickhouse-mcp to weekly-full-backup exclusions

Same change in the second Schedule’s excludedNamespaces block.

• Step 3: Commit

jj commit -m "chore(velero): exclude clickhouse-mcp from backups (stateless, Git-recreatable)"

---

Prerequisites (Manual — before ArgoCD sync)

Vault secrets

These must be populated before the ArgoCD Application syncs. The ExternalSecrets will fail to reconcile until the properties exist.

clickstack path:

vault kv patch fzymgc-house/cluster/clickstack \
  mcp_readonly_password="$(openssl rand -base64 24)" \
  mcp_readwrite_password="$(openssl rand -base64 24)"

agentgateway path:

vault kv patch fzymgc-house/cluster/agentgateway \
  clickhouse_mcp_auth_token="$(openssl rand -base64 32)" \
  vk_clickhouse_ro="vk-ro-$(openssl rand -hex 16)" \
  vk_clickhouse_rw="vk-rw-$(openssl rand -hex 16)"

Existing clickstack-secret update

The argocd/app-configs/clickstack/external-secret.yaml ExternalSecret clickstack-secret must be updated to include the two new MCP password properties. Check the current data list and add:

    - secretKey: mcp_readonly_password
      remoteRef:
        key: fzymgc-house/cluster/clickstack
        property: mcp_readonly_password
    - secretKey: mcp_readwrite_password
      remoteRef:
        key: fzymgc-house/cluster/clickstack
        property: mcp_readwrite_password

---

Verification

# 1. MCP server health
kubectl port-forward -n clickhouse-mcp svc/clickhouse-mcp-ro 8000:8000
curl -s http://localhost:8000/health
# Expected: HTTP 200, body: "OK"

# 2. Agentgateway MCP endpoint (after DNS resolves)
curl -s -H "Authorization: Bearer <vk_clickhouse_ro>" \
  https://mcp-gw.fzymgc.house/mcp/clickhouse-ro
# Expected: MCP server response (not 401/404)

# 3. External ClickHouse HTTP access
curl -u 'mcp_readonly:<password>' \
  'https://ch.fzymgc.house/?query=SELECT+1'
# Expected: "1\n1\n"

# 4. External ClickHouse native access
clickhouse-client --host ch-native.fzymgc.house --port 443 --secure \
  --user mcp_readonly --password '<password>' \
  --query "SELECT 1"
# Expected: 1

# 5. Verify users exist
kubectl exec -n clickstack chi-cs-clickhouse-0-0 -- \
  clickhouse-client --query "SELECT name FROM system.users WHERE name LIKE 'mcp_%'"
# Expected: mcp_readonly, mcp_readwrite

# 6. Verify read-only enforcement
clickhouse-client --host ch-native.fzymgc.house --port 443 --secure \
  --user mcp_readonly --password '<password>' \
  --query "CREATE TABLE test_mcp_ro (x UInt8) ENGINE = Memory"
# Expected: ERROR — not enough privileges

# 7. Verify no-DROP enforcement
clickhouse-client --host ch-native.fzymgc.house --port 443 --secure \
  --user mcp_readwrite --password '<password>' \
  --query "CREATE TABLE test_mcp_rw (x UInt8) ENGINE = Memory; DROP TABLE test_mcp_rw"
# Expected: ERROR on DROP — not enough privileges

Files Summary

Action	File
Create	argocd/app-configs/clickhouse-mcp/namespace.yaml
Create	argocd/app-configs/clickhouse-mcp/deployment.yaml
Create	argocd/app-configs/clickhouse-mcp/service.yaml
Create	argocd/app-configs/clickhouse-mcp/external-secret.yaml
Create	argocd/app-configs/clickhouse-mcp/networkpolicy.yaml
Create	argocd/app-configs/clickhouse-mcp/kustomization.yaml
Create	argocd/cluster-app/templates/clickhouse-mcp.yaml
Create	argocd/app-configs/agentgateway/mcp-clickhouse.yaml
Create	argocd/app-configs/clickstack/ingressroute-ch-http.yaml
Create	argocd/app-configs/clickstack/ingressroute-ch-native.yaml
Create	argocd/app-configs/clickstack/certificate-ch.yaml
Create	argocd/app-configs/clickstack/user-provisioning-job.yaml
Modify	argocd/app-configs/agentgateway/kustomization.yaml
Modify	argocd/app-configs/agentgateway/secrets.yaml
Modify	argocd/app-configs/clickstack/kustomization.yaml
Modify	argocd/app-configs/clickstack/external-secret.yaml
Modify	argocd/app-configs/velero/backup-schedule.yaml