How to Configure Dify ClickHouse MCP Server

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a dify clickhouse mcp server by linking your agent workflow to a database tool endpoint. You then assign a restricted database user with read permissions to run queries. Teams often start by giving the agent a broad admin account to test fast. That shortcut lets an agent read private tables or run heavy queries across the cluster. How a dify clickhouse mcp server works Dify runs workflows that let language models call outside tools through the Model Context Protocol. The ClickHouse

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up a dify clickhouse mcp server by linking your agent workflow to a database tool endpoint. You then assign a restricted database user with read permissions to run queries. Teams often start by giving the agent a broad admin account to test fast. That shortcut lets an agent read private tables or run heavy queries across the cluster.

How a dify clickhouse mcp server works

Dify runs workflows that let language models call outside tools through the Model Context Protocol. The ClickHouse tool server takes requests from the workflow and translates them into SQL queries. It logs into the database using a fixed user account and returns rows to the model.

To keep this setup safe, you must build strict access rules inside the database first. ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities using SQL. Turning on SQL access control needs a storage folder on the server. It also needs at least one user with access management turned on.

An admin with user creation rights sets up a dedicated account for the tool server. When creating this user, you set the login method, allowed client hosts, and default roles. Remember that a database user can only grant rights that they already hold themselves.

Instead of giving full table access, you can grant read rights on specific columns only. The tool user can then run queries that ask for those exact allowed columns. If the agent tries to query an unapproved column, the database blocks the query. A wildcard query across all columns also fails unless the user holds rights on every column. You can also use a trailing asterisk to grant read rights by table name prefix.

What native database roles solve and what they miss

Native database roles and column grants stop an agent from reading restricted tables. Host rules and quotas also help limit where the tool logs in and how much it reads. These steps form a solid base for any team running automated data workflows.

Still, static database roles only check who logs in at the start of a session. Every workflow using that tool server shares the same database login and the same permissions. The database cannot tell which human user or workflow triggered a specific query in flight.

ClickHouse Access Management + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Column grants also work as an all or nothing switch for data visibility. If an agent needs to query a table containing emails or phone numbers, column blocks break the query. Native grants do not rewrite sensitive patterns like card numbers or national IDs inside returned rows. You also have to store static database keys inside the tool server settings.

Planning your agent query permissions

Before going live, list every table and column your agent workflow actually needs to read. Group those exact columns into a dedicated role for the tool server account. Test your agent prompts to make sure the model asks for named columns instead of wildcards.

Review your query logs often to spot failed queries or unexpected table scans from the agent. Keep your user roles small and update them only when a workflow needs new data fields. When teams need finer checks on live queries or sensitive text fields, they look beyond static logins.

Check ClickHouse queries and result columns on the wire

ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.

Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.

Frequently asked questions

Why do wildcard queries fail on my dify clickhouse mcp server?

When you grant read rights on specific columns only, ClickHouse blocks wildcard queries across the whole table. The query only works if the user holds read rights on every column in that table by default. Prompt your Dify agent to name the exact allowed columns in every SQL query.

How do I turn on SQL role management in ClickHouse?

You must set a storage path for access entities in the server settings file. You also need at least one user account with access management rights set to one. Once enabled, you can create users, roles, row policies, and quotas with SQL commands.

Can one ClickHouse user grant extra table rights to the agent account?

A user in ClickHouse can only grant permissions that they already possess on their own account. To assign read rights to the agent role, the granting user must hold those exact rights first.

The query runs. The emails stay hidden.