How to Configure n8n ClickHouse Prevent Delete
To set up an n8n clickhouse prevent delete rule, create a limited database user. You then use that restricted account inside your workflow node or agent tool. Many teams start by pasting an admin account into their automation app. That shortcut makes testing fast, but it creates a huge risk in production. If an automated job or AI agent misfires, it can wipe out tables. Restricting privileges at the database level stops destructive statements before they run. How to set up n8n clickhouse preve
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.
To set up an n8n clickhouse prevent delete rule, create a limited database user. You then use that restricted account inside your workflow node or agent tool. Many teams start by pasting an admin account into their automation app. That shortcut makes testing fast, but it creates a huge risk in production. If an automated job or AI agent misfires, it can wipe out tables. Restricting privileges at the database level stops destructive statements before they run.
How to set up n8n clickhouse prevent delete with SQL roles
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage all of these entities with SQL statements. First, you must turn on SQL-driven access control in the database server. This setup needs a storage directory set in the access control path parameter. It also requires at least one existing user with access management turned on. That user also needs named collection control set to one.
Once SQL access control is active, a user can create new accounts. Doing this requires the create user privilege on the admin account. Remember that a user can only grant privileges that they already hold themselves. When you create the user for n8n, you set the login method. You can also limit the hosts that this new user may connect from. Finally, you assign default roles and per-user settings to lock down the account.
To prevent data deletion, do not grant delete or alter rights to n8n. Instead, grant only read and insert privileges on the target tables. You can even grant read access on specific columns of a table. If a user only has rights on two columns, other queries will fail. Even a select-all query is refused unless the table has only those columns. Since version 24.10, a trailing asterisk can grant access by table name prefix.
How n8n workflows and AI agents run database queries
In n8n, standard workflows and AI agents can both talk to your database. The n8n AI Agent node builds an agent from a chat model. You must connect at least one tool sub-node to that AI Agent node. The agent then decides which tools to call to complete a given task. Since n8n 1.82.0, all AI Agent nodes work as a Tools Agent. The old agent type setting is removed in version 3.0.
To query ClickHouse from an agent, teams often attach a sub-node tool. N8n offers a Call n8n Workflow Tool, a Custom Code Tool, and more. You can also use an HTTP Request Tool or MCP servers as tools. When the agent runs, it sends SQL queries through the attached tool. If the database user lacks delete rights, ClickHouse rejects any delete command.
ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native database roles solve and what they leave open
Native ClickHouse roles are great at deciding which account can log in. They also enforce hard limits on which tables a static account can touch. If a workflow needs only read access, a read-only role stops all deletes. Column grants also help by blocking queries on columns the user cannot read.
Still, static roles do not inspect what individual statements do in flight. Some workflows need delete rights to clean up old staging rows each night. If you grant delete rights to that n8n account, the door is open. A bad prompt or a broken loop could delete the entire production table. Native roles also cannot mask sensitive fields inside allowed columns during a query. If an allowed column holds emails, phone numbers, or card numbers, raw data flows back. Static roles cannot require a human to approve a single risky query either. They simply allow or block the whole command category for that shared login.
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
How do I stop an n8n workflow from deleting ClickHouse data?
Create a dedicated ClickHouse user for n8n using SQL access control. Grant only select or insert rights to that user, and omit delete privileges.
Can I restrict n8n to read only specific ClickHouse columns?
Yes, ClickHouse lets you grant select privileges on individual columns of a table. Queries that ask for other columns or use select-all will be refused.
Why are static ClickHouse roles not enough for AI agents in n8n?
Static roles apply the same broad permissions to every query from that connection. They cannot require human approval for one risky statement or rewrite emails in results.
Related guides
In this post