How to Configure Codex ClickHouse Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up codex clickhouse least privilege by making a scoped SQL user and role. Then you grant read rights on only the exact columns the agent needs. Teams often connect AI coding agents to databases using shared admin keys. When a role has broad rights, an agent can read private tables by mistake. It can also change data or run heavy queries that slow the database down. How codex clickhouse least privilege works in SQL ClickHouse manages users, roles, row rules, settings profiles, and quot

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 codex clickhouse least privilege by making a scoped SQL user and role. Then you grant read rights on only the exact columns the agent needs. Teams often connect AI coding agents to databases using shared admin keys. When a role has broad rights, an agent can read private tables by mistake. It can also change data or run heavy queries that slow the database down.

How codex clickhouse least privilege works in SQL

ClickHouse manages users, roles, row rules, settings profiles, and quotas as SQL items. To use SQL access control, you first set a storage folder on the server. You also need at least one admin user with access management turned on. That admin can only grant rights that they already hold themselves. Creating a new account for an AI agent takes the create user right. During setup, you define how the account logs in and which hosts can connect. You also attach default roles and user settings to that account.

After you make the user, you limit what data the agent can read. ClickHouse lets you grant select rights on single columns inside a table. If an agent has rights on two columns, it can query only those two. Any query that asks for a third column will fail right away. A full table wildcard query will also fail and return no data at all. The wildcard only works if the table holds just the allowed columns. In newer versions, you can also grant rights across tables that share a name prefix.

Why you should restrict host and settings profiles

Locking down table rights is only the first step for agent safety. You must also control where the AI coding agent runs from. When you create the ClickHouse user, you can lock it to specific network hosts. This rule stops the agent key from working if someone steals it outside your network.

You can also attach a settings profile and a resource quota to the user. Analytics queries can scan billions of rows in a few seconds. An AI tool might write a bad join or scan a huge table by mistake. Quotas cap how much compute and memory a user can spend over time. Settings profiles keep the agent in read-only mode so it cannot change tables.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles leave open in flight

Native ClickHouse roles and column grants do a great job of blocking unauthorized tables. They decide who can log in and which columns a user can touch. Still, static database roles cannot inspect what happens inside allowed columns during a query. Once an agent has select rights on a text column, it gets every value inside.

Many analytics columns hold mixed data like user logs, emails, phone numbers, or card numbers. A column grant is all or nothing for the rows the user can reach. It cannot rewrite emails or national IDs inside query results before the agent reads them. Also, static credentials sit on disk where local scripts or agent tools can read them. Native grants check permissions at the start of a query, not the live data flow.

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 inline in the protocol beside the server as a sidecar for your ClickHouse. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.

First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations or hold them for review. Then hoop.dev data masking rewrites sensitive string columns in responses in memory and logs each decision per resource. Read the docs and source on GitHub.

Frequently asked questions

Why do wildcard queries fail when using ClickHouse column grants?

ClickHouse blocks wildcard select queries when a user only has rights to specific columns. The query only works if the user has rights on every column in that table by default. The AI agent must name each allowed column in its SQL statement.

How do you turn on SQL access control in ClickHouse?

You must set the access control storage path in your server settings file. You also need to enable the access management flag for at least one admin user. After that, you can create users and roles with standard SQL statements.

Can a ClickHouse user grant rights to another agent account?

A user can only grant rights if they have the access management privilege enabled. Even then, a user can only hand out permissions that they already hold themselves.

The query runs. The emails stay hidden.