How to Configure Mastra ClickHouse Read Only Access
To set up mastra clickhouse read only access, create a restricted database user with SQL. Then pass those read credentials to your Mastra agent tool. Many teams start by giving agents a default admin account to move fast. That broad role lets an agent drop tables or read private columns by mistake. A strict read role stops write commands before they touch your data. How mastra clickhouse read only access works in SQL ClickHouse manages users, roles, row policies, settings profiles, and quotas
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 mastra clickhouse read only access, create a restricted database user with SQL. Then pass those read credentials to your Mastra agent tool. Many teams start by giving agents a default admin account to move fast. That broad role lets an agent drop tables or read private columns by mistake. A strict read role stops write commands before they touch your data.
How mastra clickhouse read only access works in SQL
ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, you first set a server storage directory. You also need an admin account with access management turned on. That admin can only grant rights that they already hold themselves. Creating a new user also requires the user creation privilege.
When you create the user for Mastra, you set the login method. You can also limit which hosts the agent may connect from. Next, you assign default roles and user settings for that agent account. For a read-only setup, you grant select rights on specific tables or columns. Column grants let the agent query only the exact columns you list.
If an agent runs a full table wildcard query, ClickHouse refuses it. The database returns no data unless the table holds only the allowed columns. In version 24.10 and newer, you can also grant select rights by table prefix. A trailing asterisk on the table name covers all matching tables at once. Once this role is ready, you give the login details to Mastra.
What native database roles solve and what they miss
Native roles and column grants do a great job of blocking writes. Your Mastra workflow can run analytical queries without risking accidental deletes or table drops. Host limits and login rules also help control where the agent connects from. If a prompt injection tries to alter data, the database rejects the command.
Still, static database roles only check who logs in at the start. They do not inspect what individual queries do in flight within allowed columns. If an allowed table holds emails, phone numbers, card numbers, or national IDs, queries return them. Data masking is needed to rewrite matched fields in results before the agent gets them.
Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Sharing static database passwords inside agent apps also creates secret sprawl across teams. Every app config holds a long-lived key that someone must rotate by hand. Native grants also lack a single audit trail that ties agent prompts to SQL queries. Teams need query-level controls to inspect statements and mask sensitive values in real time.
Best practices for scoping agent database roles
Start by listing the exact tables and columns your Mastra agent needs. Never grant select rights on a whole database when a few columns will do. Remember that column grants block wildcard queries on wider tables. Tell your agent in its tool instructions to ask for named columns only. This simple step prevents failed runs when the agent builds its own SQL.
Use table prefix grants with a trailing asterisk only for grouped reporting tables. Keep raw user tables outside of those prefix patterns. Set host rules on the ClickHouse user so only your app servers can log in. Apply quotas and settings profiles to cap how much memory a single agent query uses. This keeps a bad agent loop from slowing down your main analytics cluster.
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
Why do wildcard queries fail with ClickHouse column grants?
When you grant select rights on specific columns, ClickHouse blocks full wildcard queries. A wildcard query only works if the table contains nothing but your granted columns. You should configure your Mastra agent to request allowed column names in every query.
How do you turn on SQL access control in ClickHouse?
You must configure the access control storage directory in your server settings. You also need at least one user with access management and named collection control enabled. After that, you can create users, roles, and quotas with standard SQL commands.
Do read-only roles hide sensitive data inside allowed columns?
No, a select grant returns every row value inside an allowed column by default. If that column stores emails, phone numbers, or card numbers, the agent receives them. You need dynamic data masking to rewrite matched fields in results during the query.
Related guides
In this post