How to Configure Mastra ClickHouse Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mastra clickhouse audit log by pairing dedicated database users with strict SQL privileges. This setup records every AI agent query and limits who can read the stored history. Teams often start by giving their AI framework a broad database account. That shortcut lets agents run fast, but it creates huge security risks. An overly broad role lets a single bad prompt read or change sensitive tables. A strong setup separates the agent account from the audit reader account. Setting up

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 mastra clickhouse audit log by pairing dedicated database users with strict SQL privileges. This setup records every AI agent query and limits who can read the stored history. Teams often start by giving their AI framework a broad database account. That shortcut lets agents run fast, but it creates huge security risks. An overly broad role lets a single bad prompt read or change sensitive tables. A strong setup separates the agent account from the audit reader account.

Setting up your mastra clickhouse audit log architecture

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and change these entities with standard SQL commands. Before you start, you must turn on SQL-driven access control in the database. This step needs a storage folder set in the server access path setting. It also requires at least one admin user with access management turned on. Once active, an admin can create a specific user just for your Mastra agents.

Creating a new user requires the create user privilege on the admin account. Note that a user can only grant privileges that they already hold themselves. During user creation, you set the login method and allowed source hosts. You also assign default roles and specific settings profiles to that agent account. For the audit reader role, you can restrict read permissions down to single columns. If a role only has grants for two columns, queries on other columns fail. Wildcard queries that ask for all columns also fail and return no data at all. The only exception is when the table holds only those exact granted columns. In newer database builds, a trailing asterisk can grant read rights by table name prefix.

How native roles work for agent logging

Native database roles and column grants solve the first layer of access control. They decide which service account can log in from a given host. They also control which tables and columns an auditor or agent can read. Separating the Mastra agent role from the audit log reader keeps duties clear. The agent only gets rights to run its work, while auditors only read log tables.

Still, static database roles leave a big operational gap during live agent runs. Database grants check who logs in, not what a valid query does in flight. Once an agent logs in, it uses a shared service identity for every task. Native logs record that shared service account, not the human or workflow behind the prompt. Also, column grants are all or nothing for the columns they allow. If an allowed text column holds emails, phone numbers, or card numbers, the query returns them. Native grants cannot rewrite matched fields in results when an agent queries a mixed column.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Closing the gap for live agent queries

Teams running AI agents need more than static login rules and after-the-fact table logs. You need to tie every single database statement back to the exact agent run. You also need to inspect queries before they hit the database engine. Stopping risky commands in flight protects your data even if a prompt goes wrong. Masking sensitive values like national IDs in query results keeps raw secrets out of agent memory. Combining native ClickHouse roles with live query controls gives your team complete visibility and safety.

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

Why do wildcard queries fail on my restricted ClickHouse audit role?

When you grant read rights on specific columns, ClickHouse blocks full wildcard queries. A wildcard query only works if the table contains nothing but your allowed columns. Auditors must list the exact allowed column names in their select statements.

What server settings are needed to manage ClickHouse audit users with SQL?

You must configure a storage directory using the server access control path setting. You also need at least one initial account with access management privileges enabled. Without these, you cannot run SQL statements to create users or grant roles.

Can a Mastra service account grant audit log access to a new teammate?

No, a ClickHouse user can only grant privileges that they hold themselves. Your Mastra agent account should only have minimal rights to run its own queries. Only a dedicated admin account with user creation rights should grant audit access.

The query runs. The emails stay hidden.