How to Configure n8n ClickHouse Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up an n8n clickhouse audit log by giving n8n its own database user. Then you record every query from that user inside the database system tables. Many teams connect their automation workflows using a single shared admin account. When permissions are too broad, a bad workflow run can change or delete critical rows. A shared user also makes it hard to see which workflow ran a query. How an n8n clickhouse audit log works in native setups N8n builds an AI agent by linking a chat model to

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 an n8n clickhouse audit log by giving n8n its own database user. Then you record every query from that user inside the database system tables. Many teams connect their automation workflows using a single shared admin account. When permissions are too broad, a bad workflow run can change or delete critical rows. A shared user also makes it hard to see which workflow ran a query.

How an n8n clickhouse audit log works in native setups

N8n builds an AI agent by linking a chat model to one or more tools. You must attach at least one tool sub-node to the agent node. The agent then decides which tool to call to finish its task. To reach ClickHouse, teams attach an HTTP Request tool, custom code, or sub-workflow tool. Each tool call sends a SQL statement from n8n to the database server.

On the database side, ClickHouse manages users, roles, row policies, and quotas with SQL. Turning on SQL access control requires a storage folder on the server. It also needs an admin account with user management rights turned on. An admin can only grant rights that they already hold themselves. With the create user right, you make a dedicated account just for n8n.

During user setup, you set the login method and the allowed host addresses. You also assign default roles and specific user settings for that workflow account. When n8n runs a tool, the database logs the query under that user name. Admins then read the system query log to review what the workflow did. This links database activity back to the automation platform.

Using column grants to limit what workflows can read

An audit trail alone does not stop an agent from reading private data. You also need to limit which columns the n8n user can read. ClickHouse lets admins grant read access on specific columns of a table. If you grant read rights on two columns, the user can query only those. Any query that asks for a third column will fail right away.

A wildcard select query that asks for all columns will also fail. It returns no data unless the table holds only the permitted columns. Admins can also grant read rights across tables that share a name prefix. This helps teams manage permissions when new tables appear with the same prefix. Pairing narrow grants with your user setup keeps the log clean and focused.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native setup solves and what it leaves open

A dedicated database user and column grants solve the basic login problem. They tell the database which app logged in and which columns it can read. They also help your team filter system tables by a single user name. This gives you a baseline record of when the n8n service account ran queries.

Still, native database setup only checks who logs in at the start. It cannot control what individual statements or requests do while in flight. All workflows that share the n8n credential appear as the exact same database user. If ten workflows use that login, the database log cannot tell them apart. You cannot see which human or chat prompt triggered a specific SQL statement.

Native column grants are also all or nothing for each field. If an agent needs a table with emails, phone numbers, or card numbers, grants block the query. Native grants cannot rewrite matched fields like national IDs in the query results. Also, n8n must store the database password or key inside its own credential store. If a workflow leaks that secret, an attacker gains full access as that user.

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 select queries fail for restricted ClickHouse users?

When a user has column-level read grants, ClickHouse blocks wildcard select queries. The query only works if the user has rights to every column in the table by default.

How do n8n AI agents connect to external databases?

An n8n AI Agent node connects to at least one tool sub-node to act. Teams use built-in database tool nodes, HTTP Request tools, or custom workflow tools.

Why is a shared n8n database user bad for audit logs?

A shared database user groups all workflow runs under one identity in the log. You cannot see which workflow, agent tool, or person ran a bad query.

The query runs. The emails stay hidden.