How to Configure Dify ClickHouse Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a dify clickhouse connect workflow by creating a scoped database user and adding its credentials to your Dify tool setup. This links your AI agent to your analytical tables so it can run queries and return answers. Many teams start by plugging an admin account into the app to get things working fast. That shortcut creates a huge risk when an AI model generates and runs SQL on its own. If a prompt tricks the agent, broad permissions let it read private columns or drop tables. A safe s

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 connect workflow by creating a scoped database user and adding its credentials to your Dify tool setup. This links your AI agent to your analytical tables so it can run queries and return answers.

Many teams start by plugging an admin account into the app to get things working fast. That shortcut creates a huge risk when an AI model generates and runs SQL on its own. If a prompt tricks the agent, broad permissions let it read private columns or drop tables. A safe setup needs strict database roles before the agent sends its first query.

How to set up a dify clickhouse connect workflow

Start inside your database before you touch the AI app settings. ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities using SQL. To use SQL-driven access control, you need a storage directory set in the server settings. You also need at least one user with access management and named collection control turned on.

With those pieces in place, an admin with user creation rights can make a dedicated account for Dify. Remember that a user can only grant the privileges that they have themselves. When you create the user, you set the login method and the allowed client hosts. You also assign default roles and per-user settings to keep the agent in check.

Next, grant read rights only on the exact tables and columns the agent needs. ClickHouse lets you grant select rights on specific columns in a table. If you grant access to only two columns, the agent can query only those two columns. If the agent tries to select a third column, the database blocks the query. A select-all query with an asterisk also fails unless the table holds only the granted columns. In ClickHouse 24.10 and newer, you can also use a trailing asterisk to grant access by table name prefix.

Once the database user is ready, open your Dify workspace and add your ClickHouse integration. Enter the host address, port, database name, and the new scoped user credentials. The AI workflow can now send queries to the database under those strict rules.

What native roles solve and where gaps remain

Native database roles and column grants do a great job of limiting static access. They decide which account can log in and which tables or columns that account can read. If an AI agent tries to touch a blocked table, ClickHouse stops the query right away. Host limits also stop outside servers from using those same login keys.

ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static database grants cannot inspect what happens inside allowed queries in flight. First, the AI app still holds a long-lived password or key in its tool settings. Second, all users who share that Dify app run queries through the exact same database user. Your database logs will show the shared app account instead of the person who asked the question.

Third, column grants are all or nothing for the columns you allow. Sometimes an agent needs to read a customer table to count rows or check status codes. If an allowed text column holds emails, phone numbers, card numbers, or national IDs, raw queries return them. Standard grants cannot rewrite matched fields in query results on the fly. They also cannot pause a risky query to ask a human for approval before it runs.

Managing AI database queries in production

Running AI agents against production data takes more than basic login checks. Teams need to link every query back to the human user who triggered the prompt. They also need to filter sensitive strings out of query results before the model reads them. Keeping static passwords out of third-party app settings also cuts down the risk of leaked keys. Adding runtime controls on top of native ClickHouse roles gives you both safe logins and clean data.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Can I use a read-only user for my Dify ClickHouse tool?

Yes, and you should always use a read-only role for AI workflows. Create a dedicated user in ClickHouse and grant select rights only on the specific columns the agent needs.

Why do select-all queries fail after I set up column grants?

ClickHouse blocks select-all queries with an asterisk when a user only has access to specific columns. The query will only work if the table contains nothing but the granted columns, or if the agent names the allowed columns directly.

How can I stop Dify from reading emails or card numbers in allowed columns?

Native ClickHouse column grants either allow or block the whole column. To hide emails, phone numbers, card numbers, or national IDs inside allowed columns, use data masking that rewrites matched fields in results.

The query runs. The emails stay hidden.