How to Configure AutoGen ClickHouse Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up autogen clickhouse least privilege by making a scoped SQL role for your agent. Next, you grant that role read rights on only the exact columns the task needs. Many teams give their AI agents a shared admin key to move fast during testing. This wide role lets a bad prompt drop tables or read private user data. A tight role stops the agent from touching tables outside its job. How to set up autogen clickhouse least privilege with SQL roles ClickHouse manages users, roles, row rules,

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 autogen clickhouse least privilege by making a scoped SQL role for your agent. Next, you grant that role read rights on only the exact columns the task needs. Many teams give their AI agents a shared admin key to move fast during testing. This wide role lets a bad prompt drop tables or read private user data. A tight role stops the agent from touching tables outside its job.

How to set up autogen clickhouse least privilege with SQL roles

ClickHouse manages users, roles, row rules, settings profiles, and quotas as SQL access items. To turn on SQL access control, you first set a storage folder on the server. You also need at least one user with access management and named collection control enabled. An admin can only grant the rights that they hold themselves.

To start, an admin with user creation rights makes a new account for the AutoGen app. During this step, you set the login method and the exact hosts the agent uses. You also assign a default role and apply per-user settings for that agent account. This host check stops the agent key from working outside your app server.

Next, you lock down what the agent can read inside your tables. ClickHouse lets you grant read rights on single columns instead of the whole table. If you grant read rights on two columns, the agent can query only those two columns. If the agent asks for a third column, the database blocks the query right away. A wildcard query that asks for all columns also fails and returns no data at all. It only works if the table holds just the columns the user can read. If you have many tables, a trailing asterisk grants rights by table name prefix.

Why column grants make AutoGen queries fail

AutoGen agents write their own SQL queries based on the task they get. When a human asks a question, the agent often explores the schema first. Many agents run a broad wildcard query to see sample rows before writing a final query. Under strict column grants, that habit causes constant errors.

Remember that ClickHouse refuses wildcard queries when a user lacks rights on every single column. The agent gets an error, tries to fix its query, and wastes tokens in a loop. To avoid this, you have to list every safe column inside the agent system prompt. You must also update the prompt whenever your data team adds or changes a column.

Least Privilege Principle + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native roles solve and what they leave open

Native roles and column grants do a great job of limiting who logs in. They also stop an agent from reading restricted tables or dropping data by mistake. If an agent only has read rights on three columns, it stays inside that boundary.

Still, database roles check permissions at login and table level, not what the data holds. An agent often needs to read a text column or customer table to do its job. That allowed column might hold emails, phone numbers, card numbers, or national IDs. Native grants either allow the whole column or block the whole column. They do not rewrite sensitive fields inside query results on the fly. You also have to store static database keys on the machine that runs your AutoGen code.

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 for my AutoGen user?

ClickHouse blocks wildcard queries when a user only has read grants on specific columns. The query only runs if the user has rights on every column in that table by default.

How do I let an AutoGen agent read a group of tables?

You can use a trailing asterisk on the table name when you grant read rights. This lets the user read any table that starts with that exact name prefix.

What do I need to create a new ClickHouse user with SQL?

You need SQL access control turned on with a storage folder set on the server. Your admin account must also hold the user creation right and the rights it plans to grant.

The query runs. The emails stay hidden.