How to Configure Dify ClickHouse Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a dify clickhouse audit log by creating restricted database roles and turning on query logging. Next, you link your AI app to the database with that scoped user account. Many engineering teams start with a shared admin account to save time during initial testing. That broad role lets the AI app read every table and run destructive commands across the cluster. How the native dify clickhouse audit log setup works ClickHouse manages users, roles, row policies, settings profiles, and q

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 audit log by creating restricted database roles and turning on query logging. Next, you link your AI app to the database with that scoped user account. Many engineering teams start with a shared admin account to save time during initial testing. That broad role lets the AI app read every table and run destructive commands across the cluster.

How the native dify clickhouse audit log setup works

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities created with SQL. Turning on SQL-driven access control requires a storage directory on the server and an admin user. A user can only grant the privileges that they hold on their own account. Creating a new user requires a specific creation privilege from an existing admin.

That creation statement sets the login method, allowed client hosts, default roles, and user settings. For an AI workflow in Dify, you make a dedicated role with read grants on target tables. You can also grant read rights on specific columns so the app only queries safe fields. If the app runs a wildcard select query, the database refuses the request and returns no data.

That wildcard query only works when the table holds just the granted columns. Recent database versions also let you grant read rights across tables that share a name prefix. As the AI app runs queries, the database writes each action to its internal system log tables. Your security team then reads those system tables to review what the AI app asked for.

What native database roles solve and what they miss

Native roles and column grants stop the Dify app from reading restricted tables or dropping data. The internal system logs also give you a record of which database user ran each query. This setup works well for static apps that run the same fixed queries every day.

Still, AI apps generate dynamic queries based on user prompts and changing context. Native database setup only checks who logs in at the start of the session. It does not inspect what individual statements do in flight before they hit the data. Also, when ten AI workflows share one database user, the system log shows one generic account name.

Audit Log Integrity + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

You cannot see which end user or prompt triggered a slow or risky query. Further, column grants are all or nothing for the columns you allow the app to read. If an allowed text column contains emails, phone numbers, card numbers, or national IDs, raw data returns. Native roles cannot rewrite matched fields in results while keeping the rest of the text column readable.

Why AI query auditing needs active controls

Relying only on post-incident log reviews leaves your data exposed during the actual query run. By the time an analyst checks the system log table, sensitive records have already reached the model. A strong setup pairs least-privilege database roles with controls that check queries before they run.

Teams need a way to tie every database statement back to a real user identity. They also need a way to mask sensitive patterns like card numbers or emails in query results. This approach keeps the audit trail clear and keeps private data out of AI prompt histories.

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

How do you limit which columns Dify can query in ClickHouse?

You grant read privileges on specific column names to the database role used by the Dify app. The database then blocks queries that ask for unapproved columns or use a full table wildcard.

Why do wildcard queries fail when column grants are active?

ClickHouse refuses a wildcard select query unless the target table contains only the exact columns granted. You must list the allowed column names in the query that the AI app sends.

Can a standard database user create a new role for Dify?

No, a user can only grant privileges that they hold on their own account. Creating a user or role requires an account with user creation and access management rights turned on.

The query runs. The emails stay hidden.