How to Configure Goose ClickHouse Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a goose clickhouse connect workflow by creating a database user and linking it to your agent. Then you limit that user with SQL roles, column rules, and host restrictions. Many teams start by giving their local AI agent an existing admin account. That approach works at first, but it exposes every table and column to automated prompts. If the agent misreads a prompt, it can read private records or alter production data. A safer setup uses native database permissions to restrict the cl

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 goose clickhouse connect workflow by creating a database user and linking it to your agent. Then you limit that user with SQL roles, column rules, and host restrictions.

Many teams start by giving their local AI agent an existing admin account. That approach works at first, but it exposes every table and column to automated prompts. If the agent misreads a prompt, it can read private records or alter production data. A safer setup uses native database permissions to restrict the client from the start.

How a goose clickhouse connect workflow uses SQL access rules

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities created with SQL. Before you make a dedicated agent account, the server needs a storage folder for access rules. You also need at least one admin account that has access management rights turned on.

That admin can create a new user account just for the Goose agent. Creating this user requires the create user privilege on the database server. And a database user can only grant privileges that they already hold themselves. During creation, you pick how the account logs in and which hosts can connect. You also assign default roles and specific user settings for the agent session.

Once the user exists, you grant read rights on specific tables and columns. ClickHouse lets you grant select privileges on chosen columns rather than the whole table. If a user only has rights to two columns, queries on a third column fail. Running a wildcard select on that table is also refused and returns no data at all. A wildcard query only works when the table holds just the granted columns. Since version 24.10, a trailing asterisk in a grant matches tables by name prefix. This helps teams grant read rights across related tables without listing every single one.

What native database roles solve and what they miss

Native SQL access control is essential for any automated agent workflow. It ensures the agent logs in with a known user and a limited role. Host checks stop the credentials from working outside approved networks or machines. Column grants keep the agent away from columns it has no reason to read.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static database grants only check who logs in and which objects they touch. Once a column is granted, the database sends back every value stored in that column. Many text or JSON columns mix safe business metrics with emails, phone numbers, or card numbers. Native column grants cannot hide an email inside a granted column while showing the rest. Also, static credentials sit on the developer machine where the local agent runs. Native roles cannot ask a human to approve a risky query right before it runs.

Structuring safe queries for local AI agents

When an AI agent writes SQL on its own, it often tries to explore schemas first. It might run broad wildcard queries to see what data looks like. If you use column grants, those broad wildcard queries will fail right away. You should guide your agent instructions so the tool asks for allowed columns by name.

Teams should also separate read roles from write roles when setting up agent access. Give the default agent role read rights on safe tables only. Assign quotas and settings profiles to cap how much memory or time one query takes. This stops a bad prompt from running a massive scan that slows down the cluster. Even with these database rules, teams still need visibility into every statement the agent sends.

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 in a goose clickhouse connect setup?

When you grant select rights on specific columns only, ClickHouse blocks wildcard select queries. The query returns no data unless the table contains only the exact columns granted to that user.

What privileges are needed to create a ClickHouse user for Goose?

The admin account needs the create user privilege and access management enabled on the server. Also, an admin can only grant privileges to the new user that the admin already holds.

Can ClickHouse grant table access by name prefix for agent roles?

Yes, starting in version 24.10, you can put a trailing asterisk on a table name in grants. This grants the permission across all tables in that database that match the prefix.

The query runs. The emails stay hidden.