How to Configure Goose ClickHouse Least Privilege
You set up goose clickhouse least privilege by making a scoped SQL user for your agent. Next, you grant that user read rights only on the exact columns it needs. Many teams give AI agents broad admin keys just to get things working fast. That shortcut creates a huge risk when an agent runs a bad command. An agent with full rights can drop tables or read private user data. A tight role stops these mistakes before they hurt your system. How to set up goose clickhouse least privilege ClickHouse
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 least privilege by making a scoped SQL user for your agent. Next, you grant that user read rights only on the exact columns it needs. Many teams give AI agents broad admin keys just to get things working fast. That shortcut creates a huge risk when an agent runs a bad command. An agent with full rights can drop tables or read private user data. A tight role stops these mistakes before they hurt your system.
How to set up goose clickhouse least privilege
ClickHouse manages users, roles, row rules, settings profiles, and quotas as access items. You can build and change all of these items with SQL statements. First, you must turn on SQL access control on your server. This step needs a storage folder and one admin user with access management turned on. That admin user also needs named collection control set to one.
Making a new user takes the create user right on the server. Remember that a user can only grant the rights that they have themselves. When you make the user for Goose, you pick how it logs in. You can also limit the hosts that the agent can connect from. You then assign default roles and set per-user settings for that agent account.
To keep access tight, grant read rights only on specific columns in a table. If you grant read access on two columns, Goose can query only those two. The server will block any query that asks for a third column. A select-all query will also fail and return no data at all. Select-all works only if the table holds just the allowed columns. Since version 24.10, a trailing star lets you grant rights by table name prefix. You then pass these scoped user keys to Goose so it runs under these rules.
Why native roles are only step one
Native roles and column grants do a great job of limiting table access. They stop an AI agent from reading banned tables or changing your schema. They also lock down which hosts can open a session with the database.
Least Privilege Principle + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Still, database roles only check who logs in and which columns they ask for. They cannot inspect what happens inside the data stream while a query runs. For example, an agent might need to read a column that holds user emails. If you grant read access to that column, every email comes back in plain text. If you block the column, the agent breaks when it runs a broad query. Also, the database requires you to store a static key on the machine running Goose. Anyone who finds that local key can log in as the agent. Native roles cannot mask phone numbers or card numbers in query results on the fly.
How to plan column grants for AI agents
Start by watching the exact queries your team runs during daily work. List every database, table, and column those queries touch to do the job. Group those column grants into a clean role made just for the Goose agent. Avoid granting table-wide read access unless every single column in that table is public. Test the agent with this restricted role on a test database first. Notice if the agent tries to run select-all queries that fail under column rules. Teach the agent through its system prompt to name each column it wants to read. Keep sensitive columns like national IDs and card numbers out of the role grant. Review your role grants every month and drop any rights the agent no longer uses.
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
What happens when Goose runs a select-all query with column grants?
ClickHouse blocks the query and returns no data at all to the agent. Select-all works only when the user has rights on every column in that table by default.
Can a scoped user grant its own rights to another account?
A user in ClickHouse can only grant the privileges that they hold themselves. They also need the right to create users or grant access turned on.
How can I grant access to a group of similar tables?
Since version 24.10, you can put a trailing star at the end of a name. This lets you grant read rights across all tables that share a name prefix.
Related guides
In this post