How to Configure Goose ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up goose clickhouse read only access by creating a restricted database user. Then you give that user read grants and pass those login details to your agent. Many teams start by giving the Goose AI agent an admin database profile. They want the agent to inspect tables, debug queries, and answer data questions fast. Broad roles let an autonomous agent change rows, drop tables, or read private records. A single bad prompt can turn a simple question into a major data incident. Locking down t

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 read only access by creating a restricted database user. Then you give that user read grants and pass those login details to your agent. Many teams start by giving the Goose AI agent an admin database profile. They want the agent to inspect tables, debug queries, and answer data questions fast. Broad roles let an autonomous agent change rows, drop tables, or read private records. A single bad prompt can turn a simple question into a major data incident. Locking down the database user is the first step toward safe AI data work.

How to set up goose clickhouse read only access in the database

ClickHouse manages users, roles, row rules, settings profiles, and quotas as SQL access items. To turn on SQL access control, the server needs a dedicated storage path. It also needs at least one initial account with user management rights turned on. An admin can only grant the exact rights that they already hold themselves. Creating a new account for Goose requires the user creation right on the server.

When you create the account, you define how the agent logs in. You can also limit which hosts the agent is allowed to connect from. Next, you attach a default role and apply user settings for read limits. Instead of granting read rights on whole tables, you can restrict specific columns. When you grant select rights on chosen columns, the agent can only query those fields. If Goose tries to query another column, the database blocks the query right away. A full wildcard query also fails unless the table only has the allowed columns. Modern ClickHouse versions even let you grant rights across tables that share a name prefix.

How to connect the Goose agent to your ClickHouse user

Goose runs on a local machine or a build server and uses extensions for tools. To query your data, the agent uses a database extension or a local client tool. You store the read only username and password in the local setup for Goose. When you ask a question, the agent writes a SQL query on its own. It sends that statement to the database using the static user login you provided.

The database checks the host rules and role grants before running the query. If the query asks for allowed columns, the server sends the rows back to Goose. The agent reads the output in its context window and writes a plain answer. If the agent tries to insert data or read a blocked column, ClickHouse stops it. The agent sees the error message and tries to write a new, valid query.

Auditor Read-Only Access + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles solve and what they miss

Native database roles and column grants stop the agent from changing or deleting your data. They also block access to whole tables or specific columns that you mark off limits. This setup decides which account can log in and which objects it can touch. Still, native setup does not inspect what happens inside the allowed data in flight.

First, the agent still needs a real database password stored on the local machine. Anyone who reads that local file can use the same login from an allowed host. Second, allowed text columns or log tables often hold mixed private details inside them. A support note column might hold emails, phone numbers, card numbers, or national IDs. Column grants are all or nothing, so the agent receives those raw strings in results. Data masking is needed to rewrite matched fields in results before the agent reads them. Third, a shared static account makes it hard to tie each query to one person. Teams need a clear log that links every AI query to a real human user.

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

Why does a wildcard query fail when Goose uses column grants?

ClickHouse blocks full wildcard queries when a user only has rights on specific columns. The query only works if the table contains nothing but the exact granted columns. You must prompt Goose to list the allowed column names in every select statement.

Can Goose grant itself more rights in ClickHouse?

No, a ClickHouse user can only grant rights that it already holds itself. As long as you do not give the agent user management rights, it cannot upgrade itself.

How do we stop Goose from reading emails or card numbers in text columns?

Native column grants only block whole columns, not private strings inside a free text field. To protect emails, phone numbers, or card numbers inside text, you need dynamic data masking. Masking rewrites matched fields in results before the output reaches the agent.

The query runs. The emails stay hidden.