How to Configure Mastra ClickHouse Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up a mastra clickhouse connect workflow, create a dedicated database user first. Then give your AI agent that user account to run analytical queries. Teams often start by sharing an admin login during early tests. That shortcut creates big risks when agents build and run their own SQL queries. A broad role lets an agent read private tables or change stored data. How to set up mastra clickhouse connect permissions A safe setup starts inside the database before you touch the agent code.

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 connect workflow, create a dedicated database user first. Then give your AI agent that user account to run analytical queries. Teams often start by sharing an admin login during early tests. That shortcut creates big risks when agents build and run their own SQL queries. A broad role lets an agent read private tables or change stored data.

How to set up mastra clickhouse connect permissions

A safe setup starts inside the database before you touch the agent code. ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. To turn on SQL access control, you must set a storage directory on the server. You also need at least one user with access management and named collection control enabled. Once you turn this on, an admin can create a user for the agent.

Creating a new user requires the create user privilege in the database. Note that a user can only grant privileges that they hold themselves. In the user setup statement, you can pick the login method and allowed hosts. You can also assign default roles and specific user settings for the agent. Locking down the allowed host stops outside machines from using the agent login.

Next, you should limit which columns the agent can read during a run. ClickHouse lets you grant select permissions on specific columns of a table. The agent can then run queries that ask for those exact columns only. If the agent asks for a restricted column, the database blocks the query. A wildcard query that asks for all columns will also fail and return nothing. It only works if the table holds just the allowed columns. If your database runs version 24.10 or newer, you can use a trailing asterisk. That symbol grants read rights across tables that share a name prefix.

What native roles solve and where gaps remain

Native roles and column grants do a great job of blocking unauthorized table reads. They stop an agent from reading restricted columns or connecting from an unknown host. Quotas and settings profiles also help keep heavy queries from slowing down the server. These built-in tools decide who can log in and which objects they can touch.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, database grants cannot inspect what happens inside an allowed column during a query. An agent might need access to a text column to summarize customer feedback. That same column might hold emails, phone numbers, card numbers, or national IDs. Because the column grant is open, the database sends all of that raw text back. Also, static credentials sit in the app settings where scripts or logs might expose them. Native permissions check the user at login, not the live data in each query.

Why query-level controls matter for AI agents

AI agents do not act like standard web apps with fixed query templates. An agent writes new SQL statements on the fly based on user prompts. A prompt mistake can trick the agent into pulling thousands of sensitive rows at once. Even with a read-only role, the agent can still pull private customer fields. Teams need a way to check each statement and filter sensitive text in flight. Rewriting matched fields like emails or card numbers in results keeps private data safe. It lets the agent finish its task while keeping raw personal details out of prompts.

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 did my agent wildcard query fail after setting column grants?

ClickHouse blocks wildcard select queries when a user only has grants for specific columns. The query returns no data unless the table holds only the granted columns. Update your agent instructions so it asks for allowed column names by name.

How do I turn on SQL access control for agent roles in ClickHouse?

You must configure the access control storage directory on the database server first. You also need at least one user account with access management privileges turned on. After that, you can create users and roles with standard SQL statements.

Can a ClickHouse role grant permissions to another agent role?

Yes, a user or role can grant privileges if it holds the right access rights. Still, ClickHouse rules state that a user can only grant privileges they hold themselves. Keep grant rights off your agent account so it cannot give itself more access.

The query runs. The emails stay hidden.