How to Configure Mastra ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a mastra clickhouse prevent delete rule, restrict the database user to read and insert privileges. You also revoke all delete and drop permissions from the role your AI agent uses. Many teams build AI agents that query analytics tables directly. Often, developers give the agent a broad database account during testing. That broad account stays in place when the app goes live. If a prompt confuses the agent, it might run a delete statement. Taking away delete rights stops the agent from

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 a mastra clickhouse prevent delete rule, restrict the database user to read and insert privileges. You also revoke all delete and drop permissions from the role your AI agent uses. Many teams build AI agents that query analytics tables directly. Often, developers give the agent a broad database account during testing. That broad account stays in place when the app goes live. If a prompt confuses the agent, it might run a delete statement. Taking away delete rights stops the agent from removing production rows.

How to set up a mastra clickhouse prevent delete role

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and change these entities with standard SQL statements. First, an admin must turn on SQL-driven access control on the server. This step needs a storage directory set in the server access control path. It also needs at least one user with access management rights turned on.

Once that is ready, an admin with user creation rights makes a new user for Mastra. The creation statement sets the login method and the allowed client hosts. It also assigns default roles and user settings. In ClickHouse, a user can only grant privileges that they hold themselves.

To prevent deletes, you create a role that only grants select and insert rights. You do not grant delete, alter, or drop privileges to this role. You can even grant select rights on specific columns in a table. The agent can then query only those named columns. If the agent runs a query for all columns with an asterisk, ClickHouse refuses it. That query returns no data unless the table only holds the granted columns. In version 24.10 and later, a trailing asterisk grants rights by table name prefix. Finally, you assign this restricted role as the default for the Mastra database user.

What native database roles solve and what they miss

Native ClickHouse roles are great for blocking destructive table actions at the storage engine level. When the Mastra agent logs in with a restricted user, the database checks its role. If the agent tries to delete rows, the server rejects the query right away. This setup protects your core tables from accidental data loss.

Still, static database roles only decide who logs in and which tables they can touch. They do not inspect what individual queries do in flight beyond basic table rules. For example, an agent with select rights can still read sensitive columns if granted table-wide read access. Maintaining column-by-column grants across hundreds of tables takes a lot of manual work. Every time a schema changes, someone must update the role grants.

ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Also, static credentials sit in the app settings for the Mastra agent. Anyone who gets that key has the same standing access as the agent. Native roles cannot require human approval for a single risky query on the fly. They also cannot rewrite sensitive fields like emails, phone numbers, card numbers, or national IDs in query results. Teams need a way to inspect queries and mask data without changing database roles every day.

Why agent workflows need statement-level controls

AI agents build and run queries based on user input and context. Unlike standard app code, an agent does not follow a fixed list of pre-written queries. One user question might trigger a simple count, while another triggers a huge table scan. Relying only on a shared database user leaves a gap between intent and execution.

When you pair native database roles with statement-level checks, you close that gap. The database enforces the baseline rule that blocks delete mutations. At the same time, runtime checks handle dynamic masking and query approvals per request. This keeps the agent fast while protecting sensitive records from unintended exposure.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

How do I stop a Mastra agent from deleting ClickHouse data?

Create a dedicated ClickHouse user and role for the Mastra agent using SQL access control. Grant only select and insert rights to that role, and leave out delete and alter permissions.

What happens if an agent runs a full table select with column grants?

If a user only has select grants on specific columns, ClickHouse blocks a select asterisk query. The query returns no data unless the table contains only those exact granted columns.

Can ClickHouse roles mask emails or card numbers in agent query results?

No, native ClickHouse roles and column grants only allow or block access to whole columns. To rewrite matched fields like emails or card numbers in results, you need inline data masking.

The query runs. The emails stay hidden.