How to Configure Goose ClickHouse Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up goose clickhouse permissions by creating a restricted database user with SQL roles. Then you give that user read grants on specific tables and columns for the agent. Many teams start by giving their AI agent an existing developer account. That broad account often holds rights to drop tables, change rows, or read all tables. When an AI agent runs with full rights, one bad prompt can cause data loss. Giving the agent its own locked-down database role stops those mistakes before they hap

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 goose clickhouse permissions by creating a restricted database user with SQL roles. Then you give that user read grants on specific tables and columns for the agent. Many teams start by giving their AI agent an existing developer account. That broad account often holds rights to drop tables, change rows, or read all tables. When an AI agent runs with full rights, one bad prompt can cause data loss. Giving the agent its own locked-down database role stops those mistakes before they happen.

How to set up goose clickhouse permissions in SQL

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities in SQL. To turn on SQL access control, the server needs a storage folder path set up. You also need at least one admin user with access management rights turned on. A database user can only grant the rights that they already hold themselves. Creating a new user for Goose requires the user creation right on the server.

When you create the user, you set the login method and allowed client hosts. You also assign default roles and specific settings profiles for that new agent account. Instead of giving full table access, you can grant read rights on specific columns. The agent can then run queries that ask for those exact allowed columns only. If the agent tries to read another column, the database blocks the query right away. A wildcard query for all columns also fails and returns no data at all. That wildcard query only works if the table holds just the allowed columns.

Since version 24.10, admins can also grant rights using a table name prefix with an asterisk. This lets Goose read a group of tables that share the same starting name. You do not need to grant rights on each new table one by one.

Why you should separate agent roles from developer accounts

AI agents like Goose write and run queries much faster than human engineers. If a developer shares their personal database login with Goose, audit logs get messy. You cannot tell if a human ran a query or if the agent did. Creating a dedicated role for Goose makes every agent action clear in the logs. You can also attach strict quotas to that role so heavy queries do not slow the cluster.

Separate roles also make key rotation much safer and simpler for the security team. If a local machine gets lost, you can turn off the agent user right away. Normal developer work goes on without any pause while you fix the agent setup.

ClickHouse Access Management + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles solve and what keeps the session active

Native SQL roles and column grants do a great job of limiting broad table access. They make sure the Goose agent logs in from known hosts with limited rights. They also stop the agent from reading restricted columns or changing production tables by mistake.

Still, static database roles only check who logs in and which columns they ask for. Once a column is allowed, the database sends back every raw value in that column. If an allowed text column holds emails, phone numbers, card numbers, or national IDs, Goose gets them. Static roles also require you to store a database password on the local developer machine. Also, native grants cannot pause a risky query to ask a human for approval first. Teams often want to rewrite sensitive fields in results and review actions in real time.

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 from Goose fail when column grants are active?

ClickHouse blocks wildcard queries when a user only has read rights on specific columns. The query only works if the user has rights on every column in that table by default. Tell Goose to list the exact allowed column names in every query it writes.

What server setup is required before creating SQL roles for Goose?

The server must have its access control storage path set up first. You also need an initial user account with access management and named collection control set to one. After that, you can create users, roles, and quotas with standard SQL statements.

Can Goose read sensitive data inside an allowed ClickHouse column?

Yes, native column grants return all rows and values inside an allowed column by default. If that column contains emails or card numbers, the database sends those raw values back. To hide those values in query results, you need data masking in front of the database.

The query runs. The emails stay hidden.