How to Configure Mastra ClickHouse Permissions
You set up mastra clickhouse permissions by creating a restricted database user and role. Then you grant read rights on specific tables and columns to that account. Many teams skip this step and give their Mastra agents a shared admin login. Broad database rights let an AI agent read private tables or run heavy queries. A single bad prompt can expose customer emails or drop a production table. How mastra clickhouse permissions work in practice ClickHouse manages users, roles, row policies, se
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 mastra clickhouse permissions by creating a restricted database user and role. Then you grant read rights on specific tables and columns to that account. Many teams skip this step and give their Mastra agents a shared admin login. Broad database rights let an AI agent read private tables or run heavy queries. A single bad prompt can expose customer emails or drop a production table.
How mastra clickhouse permissions work in practice
ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL statements. Before you create accounts with SQL, you must turn on SQL-driven access control. This setup needs a storage folder defined in the server settings. It also needs at least one account with access management turned on. Once active, an admin can create a dedicated user for the Mastra app. Remember that a database user can only grant rights that they hold themselves.
Creating a new user requires the privilege to create users. During this step, you set how the Mastra agent will log in. You can also restrict which host addresses the agent may use. You can assign default roles and apply per-user settings at the same time. Most teams create a read-only role for their Mastra workflows. They attach that role to the agent account as its default role.
Next, you limit what the Mastra role can read inside your database. ClickHouse lets you grant read rights on single columns instead of full tables. If you grant read access on two columns, the agent can query only those. Any query that asks for a third column will fail right away. A query that selects all columns with an asterisk will also fail. That wildcard query only works if the table holds only the allowed columns. In versions since 24.10, a trailing asterisk grants rights by table name prefix.
What native database roles solve and what they miss
Native ClickHouse roles and column grants stop an agent from touching blocked tables. Host rules also block logins from unknown servers outside your network. Quotas and settings profiles help cap how much memory or time an agent uses. This native setup gives you a strong baseline for database access control.
Still, database roles only check who logs in and which columns they pick. They do not inspect what individual queries do while they run in flight. Every Mastra agent that shares the same login gets the exact same rights. Your database logs will show one shared service user for every single query. You cannot tell which user prompt or workflow step triggered a specific read.
ClickHouse Access Management + AI Agent Permissions: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Column grants also present a tough trade-off when tables hold mixed data. A support column might contain plain text mixed with customer emails or phone numbers. If you block the column, the Mastra agent cannot read the support notes. If you allow the column, the query results include raw phone numbers and card numbers. Native permissions are all-or-nothing on each column and cannot rewrite fields in results. They also require you to store static database keys inside your Mastra app.
Planning a least-privilege workflow for AI agents
Start by listing every query your Mastra tools need to run. Group these tasks into clear buckets like public analytics or customer support lookups. Create a separate ClickHouse role for each bucket instead of one giant role. Grant read rights only on the exact columns required for each specific task.
Avoid using wildcard selects in the tools you build for your Mastra agents. Write explicit column names in every tool so column grants work without errors. Set strict quotas on the agent role so a loop cannot starve your cluster. Review your role grants often as you add new tools to your Mastra app.
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 after setting column permissions?
ClickHouse blocks wildcard select queries when a user only has rights on specific columns. The query only works if the user has rights on every column in the table by default. Update your Mastra tools to request each allowed column by name.
Can a Mastra database user create other users or roles?
No, unless you grant it the privilege to create users and manage access. Even then, a ClickHouse user can only grant privileges that it already holds. Always keep access management turned off for accounts used by AI agents.
How do I grant access to a group of tables that share a prefix?
In ClickHouse 24.10 and newer, you can use a trailing asterisk on the name. This lets you grant read rights across all tables that start with that prefix. It works well when your app reads from daily or monthly log tables.
Related guides
In this post