How to Configure Dify ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up dify clickhouse read only access by creating a restricted database user. Then you grant that user select rights and add those credentials to your app. Many teams start by sharing an admin login during early testing. This shortcut lets an AI agent drop tables or change live rows by mistake. A dedicated read role stops write queries before they change your data. How dify clickhouse read only access works in SQL ClickHouse manages users, roles, row rules, settings profiles, and quotas

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 read only access by creating a restricted database user. Then you grant that user select rights and add those credentials to your app. Many teams start by sharing an admin login during early testing. This shortcut lets an AI agent drop tables or change live rows by mistake. A dedicated read role stops write queries before they change your data.

How dify clickhouse read only access works in SQL

ClickHouse manages users, roles, row rules, settings profiles, and quotas through SQL commands. To turn on SQL access control, the server needs a storage path on disk. You also need an admin user with access management rights turned on. That admin can only hand out rights that they already hold themselves. Creating a new login also requires the create user right on the server.

When you create the login for Dify, you pick a login method. You can also limit which client hosts can log in to the database. Next, you attach a default role and user settings to that account. For a read-only setup, you grant select rights on specific tables or columns. If you grant select on two columns, the user can read only those columns. A query for a third column will fail right away. A wildcard select query will also fail and return no data at all. It only works if the table holds just the allowed columns. You can also use a trailing asterisk to grant read rights by table prefix.

What native database roles solve and what they miss

Native roles and column grants stop an AI workflow from deleting or changing data. They also let you block access to entire tables that hold internal secrets. This setup works well when every user in the app needs the same view.

Still, static database roles only check who logs in at the start. Dify connects to ClickHouse with one shared database account for all app users. The database sees every query coming from that single app role. It cannot tell which human user triggered the prompt inside the AI app. Also, a column grant is an all or nothing rule for that login. If a support agent needs a column that holds emails or phone numbers, the AI reads the raw text. The same goes for columns with card numbers or national IDs. Native grants cannot rewrite matched fields in query results on the fly. They also do not require human approval when a bot runs a huge scan.

Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing query risks in AI workflows

When an AI agent writes SQL, it often tries broad queries first. If you use column grants, a broad wildcard query will fail right away. You must tell the Dify prompt which specific columns it can ask for. You should also set quotas on the database role to limit heavy scans. This keeps a bad prompt from using up all your server memory.

Even with good prompts and quotas, static keys inside app settings carry risk. Anyone with admin access to the AI workspace can copy the database login. Teams that handle regulated data need finer controls over every single query in flight. They need a way to inspect statements and mask fields like emails or card numbers before results reach the model.

Check ClickHouse queries and results before execution

Access control in ClickHouse is essential setup. Setup alone does not check single queries for risk or rewrite sensitive strings in returned rows. By design, hoop.dev sits beside the server as a sidecar for your ClickHouse and parses every statement before execution.

First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations. Then hoop.dev data masking rewrites sensitive string columns in responses and logs each decision per resource. Read the docs and source on GitHub.

Frequently asked questions

Why do wildcard queries fail with column grants in ClickHouse?

When you grant select on specific columns, ClickHouse blocks full wildcard queries. The query returns nothing unless the table contains only those exact granted columns. Your AI prompt must list the allowed column names in every select statement.

Can a ClickHouse user grant read rights to another Dify account?

A database user can only grant rights that they already hold themselves. They also need access management and create user rights to make new logins. A read-only account for Dify should never hold these admin rights.

Does a read-only role hide sensitive text inside allowed columns?

No, a column grant only lets you allow or block the whole column. If you allow a column, queries return the raw emails, phone numbers, or card numbers. To hide sensitive patterns inside results, you need data masking in front of the database.

The query runs. The emails stay hidden.