How to Configure Dify ClickHouse Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up dify clickhouse permissions by creating a dedicated database user with read-only rules. You then add those login details to your AI workflow tool. Many teams start by using a shared admin account to test their workflows fast. This broad role lets an AI agent read any table or change live data. A single bad prompt can expose private rows or drop key tables. Setting strict rules on the database side stops this risk before it starts. How to set up dify clickhouse permissions ClickHous

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 dify clickhouse permissions by creating a dedicated database user with read-only rules. You then add those login details to your AI workflow tool. Many teams start by using a shared admin account to test their workflows fast. This broad role lets an AI agent read any table or change live data. A single bad prompt can expose private rows or drop key tables. Setting strict rules on the database side stops this risk before it starts.

How to set up dify clickhouse permissions

ClickHouse manages users, roles, row rules, settings profiles, and quotas as SQL access items. To turn on SQL access control, you must set a storage folder on the server. You also need at least one user with access management turned on. That admin user can only grant rights that they already hold themselves. Creating a new user also requires the user creation privilege.

When you create the user for your Dify app, you pick the login method. You also define which hosts the user can log in from. Locking the host list to your Dify server IP blocks outside login attempts. You can also attach default roles and custom settings to this new user.

Next, you assign read rights to this role. Do not grant read access to the whole database. Instead, grant read access only on the specific columns your Dify workflow needs. When a user has column-level read grants, they can only query those named columns. If the AI tries to run a select-all query, the database refuses it. It returns no data unless the table only holds those exact granted columns. On version 24.10 and newer, a trailing star grants access by table name prefix.

Why column grants break AI workflows

Column grants work well when humans write fixed queries with known column names. Still, AI agents in Dify often generate SQL on the fly. They inspect schemas and run broad select-all queries to explore the table structure. When column grants block a select-all query, the agent gets an error and fails.

Because of this, teams often give up on column grants. They grant full table read rights so the AI agent can run queries freely. Once you grant full table rights, the database sends every column back to Dify by default. If a table holds emails, phone numbers, card numbers, or national IDs, the model reads them.

ClickHouse Access Management + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native database roles only check who logs in and which tables they touch. They cannot rewrite sensitive fields inside a query result while the query runs. They also cannot require a human to approve a slow query before it runs. Also, every Dify workflow shares the same database user account. When a bad query spikes CPU load, your logs only show that shared user. You cannot tell which Dify user or workflow sent the bad query.

What to check before going live

Before you move your Dify app to production, review every table your role can read. Remove access to system tables and internal logs that the AI does not need. Check that your user creation settings enforce strict quotas on query time and memory. Quotas stop a runaway AI loop from taking down your analytics cluster.

Test your Dify agent with the restricted user account in a staging setup first. Watch for failed select-all queries if you use column-level grants. If your tables mix public metrics with private fields like emails, split them up. You can also use a view that excludes private columns like card numbers. Grant the Dify role access to the clean view instead of the raw table. This keeps the agent working while keeping private fields out of prompts.

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 select-all queries from Dify fail on ClickHouse?

If you grant read rights on specific columns only, ClickHouse blocks select-all queries. The query only works if the user has rights on every column in that table by default.

How do I restrict where the Dify database user logs in from?

When you create the user in ClickHouse, set the allowed host parameter. This limits logins to the specific IP address or hostname of your Dify server.

Can a ClickHouse user grant extra rights to the Dify role?

A user in ClickHouse can only grant privileges that they already hold themselves. They also need the access management flag enabled on their own account.

The query runs. The emails stay hidden.