How to Configure Codex ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up codex clickhouse read only access by making a restricted database user. Then you grant select permissions on chosen tables or columns to that user. Engineering teams often give AI coding agents broad database credentials to move fast. Overly broad roles let an agent alter tables, drop data, or read secret rows. A dedicated read-only user stops write commands at the database engine level. How codex clickhouse read only access works in native SQL ClickHouse manages users, roles, row

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 read only access by making a restricted database user. Then you grant select permissions on chosen tables or columns to that user. Engineering teams often give AI coding agents broad database credentials to move fast. Overly broad roles let an agent alter tables, drop data, or read secret rows. A dedicated read-only user stops write commands at the database engine level.

How codex clickhouse read only access works in native SQL

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. Before you create users with SQL, you must turn on SQL-driven access control. This setup needs a storage folder on the server and an admin user. That admin user must hold access management permissions. In ClickHouse, a user can only grant privileges that they already hold themselves.

Once SQL access control is active, you create a new user account for the agent. Creating a user requires the create user privilege on the server. During creation, you define how the user logs in and which hosts can connect. You also assign default roles and specific user settings in that same step.

Next, you grant select privileges to the new user or to its assigned role. You can grant select rights on a whole table or on specific columns. When you grant access to specific columns, the user can only query those columns. If the agent tries to query another column, ClickHouse refuses the query. A wildcard query for all columns also fails and returns no data at all. It only works if the table holds just the granted columns. You can also use a trailing asterisk to grant select rights by table prefix.

What native read-only roles solve and what they miss

Native ClickHouse roles and column grants solve the biggest risk of AI database use. They stop the agent from running insert, update, delete, or drop statements. Column grants also keep the agent away from columns you explicitly leave out. If a table has ten columns, you can expose only three safe ones.

Still, static database roles only decide who can log in and which columns load. They do not inspect what individual queries do in flight once the session starts. First, an AI agent needs a database password or key stored in its workspace. Local secret files can leak through logs, prompts, or shared project folders. Second, column grants are all-or-nothing for each column. Sometimes a text column or JSON field mixes normal app data with customer emails. If you grant select on that column, the query result returns the raw emails. Data masking that rewrites emails, phone numbers, or card numbers in results helps here. Third, native grants do not link an agent query back to a human developer. Every agent run uses the same static database user name in the server logs.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Managing settings profiles and row policies for AI agents

Analytical databases scan huge amounts of data very fast. An AI coding agent can write a bad join or scan billions of rows. Even a read-only query can use up memory and slow down production apps. ClickHouse lets you attach settings profiles and quotas to your read-only user. Quotas limit how much resource usage a user can consume over a time window. Row policies also let you filter which rows a user can read using SQL rules. Combining column grants, row policies, and quotas gives you a strong baseline in ClickHouse.

Still, managing column grants across hundreds of changing tables takes ongoing manual work. Every time a team adds a column with national IDs, admins must update grants. If someone forgets to update the grant list, a wildcard select fails or exposes data. Teams need a way to enforce read-only rules and mask sensitive fields in flight.

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 sits inline in the native TCP protocol beside the server as a sidecar for your ClickHouse. For every production query an agent or human sends, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run or hold risky calls for approval, and data masking rewrites matched string columns in results in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Why do wildcard queries fail when using ClickHouse column grants?

When you grant select on specific columns, ClickHouse blocks queries that ask for all columns by default. The database refuses the wildcard query and returns no data unless all columns are granted. Your AI agent must list the allowed column names in every select statement.

How do you turn on SQL access control in ClickHouse?

You must configure a storage directory on the server for access entities. You also need at least one user account with access management permissions enabled. Once those are set, admins with the create user privilege can manage accounts via SQL.

Does a ClickHouse read-only user hide emails inside allowed text columns?

No, native column grants only allow or block an entire column at a time. If an allowed column contains emails, phone numbers, or card numbers, queries return them. You need runtime data masking to rewrite those matched fields in query results.

The query runs. The emails stay hidden.