How to Configure Mastra ClickHouse MCP Server

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a mastra clickhouse mcp server by pairing an agent with a read-only database user. Next, you pass those database login details to the agent tool process. Teams often start by reusing a shared admin account to test queries fast. That broad role lets the AI agent read every table or alter data by mistake. How a mastra clickhouse mcp server uses native roles A safe setup starts inside the database before you run the agent tool. ClickHouse treats users, roles, row rules, settings profi

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 mcp server by pairing an agent with a read-only database user. Next, you pass those database login details to the agent tool process. Teams often start by reusing a shared admin account to test queries fast. That broad role lets the AI agent read every table or alter data by mistake.

How a mastra clickhouse mcp server uses native roles

A safe setup starts inside the database before you run the agent tool. ClickHouse treats users, roles, row rules, settings profiles, and quotas as SQL access items. To turn on SQL access control, an admin sets a storage path on the server. At least one admin user must also hold access management rights in their account.

That admin can then create a dedicated user for the Mastra agent workflow. Creating a user requires the create user privilege in the database. Remember that a database user can only grant rights that they already hold. When you create the agent user, you set its login method and default roles. You can also limit which network hosts the agent process may log in from.

After you make the user, you grant read rights on specific tables or columns. Column grants let the agent query only the exact fields you pick. If the agent runs a query on other columns, the database blocks it. A wildcard query that asks for all columns also fails and returns no data. That wildcard query only works if the table holds just the allowed columns. Since version 24.10, you can also grant read rights using a table name prefix.

Why native roles leave a gap for AI agents

Native database users and column grants solve the first part of agent safety. They decide which process can log in and which tables it can read. Quotas and settings profiles also help cap how much work a query can do. This stops an agent from dropping tables or reading hidden tables by mistake.

Still, static database grants do not inspect what happens inside allowed columns in flight. An agent often needs to read a customer table to answer a support ticket. If that table holds emails, phone numbers, or card numbers, the query returns them. Static grants are all or nothing for any column the user can read. If you block the column, the agent breaks when it runs a broad query. If you allow the column, raw emails and national IDs flow into the model prompt.

ClickHouse Access Management + Kubernetes API Server Access: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static users also rely on long-lived keys stored in the agent app host. Every query from every user of your Mastra app shares the same database login. When a bad query runs, the database log only shows that one shared user. Teams cannot see which human user or agent task triggered the database read. They also cannot pause a single risky query for human review before it runs.

How to scope queries and protect sensitive fields

Before you put your agent in front of users, map every table it needs. List the exact columns required for each agent task and drop the rest. Create a dedicated role for the agent and bind it to those columns only. Tell the agent prompt which columns exist so it avoids full wildcard selects.

Next, check the allowed columns for sensitive text like card numbers or phone numbers. Native grants will return those values in plain text whenever the agent reads them. You need a way to rewrite matched fields in results before the agent reads them. You also need a record that links each SQL query to the active task.

Check ClickHouse queries and results before execution

Access control in ClickHouse is essential setup. Setup alone does not check single queries for risk or rewrite sensitive strings in returned rows. By design, hoop.dev sits beside the server as a sidecar for your ClickHouse and parses every statement before execution.

First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations. Then hoop.dev data masking rewrites sensitive string columns in responses and logs each decision per resource. Read the docs and source on GitHub.

Frequently asked questions

What happens if the Mastra agent runs SELECT * on a restricted table?

ClickHouse refuses the query and returns no data at all. A wildcard select only works when the user has rights on every column by default.

Can I restrict which hosts the MCP server logs in from?

Yes, the create user statement lets you define allowed client hosts. The database will reject login attempts from any other network address.

How can I grant read access to a group of ClickHouse tables at once?

Since version 24.10, you can add a trailing asterisk to a table name prefix. This grants the select right across all tables that match that starting name.

The query runs. The emails stay hidden.