How to Configure n8n ClickHouse Read Only Access
You set up n8n clickhouse read only access by creating a restricted database user with select grants. Then you connect that user to your n8n workflow or AI agent tool node. Many engineering teams start by reusing a shared admin account during early testing. This shortcut exposes production tables to unwanted writes, drops, or massive table scans. A dedicated read-only user limits what the workflow can do at the database level. How to set up n8n clickhouse read only access in your database Cli
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 n8n clickhouse read only access by creating a restricted database user with select grants. Then you connect that user to your n8n workflow or AI agent tool node. Many engineering teams start by reusing a shared admin account during early testing. This shortcut exposes production tables to unwanted writes, drops, or massive table scans. A dedicated read-only user limits what the workflow can do at the database level.
How to set up n8n clickhouse read only access in your database
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage these entities with SQL statements. First, you must turn on SQL-driven access control in the server settings. This step requires a storage directory and an initial user with access management rights. Remember that a user can only grant privileges that they already hold themselves.
Once SQL access control is active, you create a new user for n8n. The user creation statement sets the login method and allowed client hosts. It also assigns default roles and per-user settings profiles. To make the account read-only, you grant select privileges on specific tables. You can even grant select rights on specific columns within a table. If a user only has rights on two columns, they can query only those two. A query for all columns with an asterisk will fail and return no data. It only works if the table holds just those granted columns. You can also use a trailing asterisk to grant select rights by table name prefix.
How n8n workflows and AI agents run database queries
In n8n, workflows can query ClickHouse through an HTTP request tool or custom sub-workflow. When you build an AI agent in n8n, you attach a chat model and tools. You must connect at least one tool sub-node to the AI Agent node. The agent then decides which tools to call to finish a task. All recent AI Agent nodes work as a Tools Agent by default.
While n8n has built-in SQL agent tools for Postgres and MySQL, ClickHouse needs another path. Teams often attach an HTTP Request Tool or a Call n8n Workflow Tool to the agent. They also use custom code tools or Model Context Protocol servers to run ClickHouse queries. Each of these tools stores the ClickHouse login credentials inside the n8n credential store. When the agent runs, it sends the generated SQL query over that saved connection.
Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native read-only roles solve and what they leave open
Native ClickHouse grants stop the n8n user from running insert, alter, or drop statements. Column grants also block queries that try to read restricted columns by name. This native setup solves the basic problem of accidental data deletion or schema changes.
Still, database roles only check who logs in and which tables they can read. They do not inspect what individual queries do in flight or mask sensitive fields. If a granted column holds emails, phone numbers, or card numbers, the query returns them raw. Column grants are also all-or-nothing, refusing wildcard queries rather than rewriting matched fields in results. Also, every n8n run shares the same static database user. Your database logs will show the shared n8n user, not the human who triggered the agent. You also cannot require a quick team approval before an agent runs a heavy query.
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 does a wildcard select query fail for a restricted ClickHouse user?
When you grant select on specific columns only, ClickHouse blocks wildcard select queries. The query returns no data unless the table contains only those exact granted columns.
How do n8n AI agents run queries against ClickHouse?
The n8n AI Agent node uses attached tool sub-nodes to run external tasks. Teams connect an HTTP Request Tool, a Call n8n Workflow Tool, or an MCP server for ClickHouse.
Can ClickHouse column grants mask emails or card numbers in query results?
No, native column grants only allow or block access to an entire column. To rewrite matched emails, phone numbers, or national IDs in results, you need inline data masking.
Related guides
In this post