How to Configure LangChain ClickHouse Least Privilege

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up langchain clickhouse least privilege by creating a read-only database user for your agent. Next, you grant that user permission to read only specific tables and columns. Many teams connect AI tools to their data using a shared admin account. That shortcut lets an agent read private tables or run heavy queries by mistake. When a model writes its own SQL, broad roles create a big risk. Giving the agent a tiny set of permissions keeps your data safe. How to set up langchain 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 langchain clickhouse least privilege by creating a read-only database user for your agent. Next, you grant that user permission to read only specific tables and columns. Many teams connect AI tools to their data using a shared admin account. That shortcut lets an agent read private tables or run heavy queries by mistake. When a model writes its own SQL, broad roles create a big risk. Giving the agent a tiny set of permissions keeps your data safe.

How to set up langchain clickhouse least privilege

ClickHouse manages users, roles, row rules, settings profiles, and quotas through SQL commands. To use SQL access control, the server needs a storage path set up first. You also need an admin user with access management turned on. A user can only hand out permissions that they already hold themselves. Creating a new user for your agent takes the create user permission.

When you create the agent user, you set how it logs in. You can also lock down which host addresses the agent can connect from. Also, you can assign default roles and settings profiles to that user. A good setup starts with a dedicated role for your AI app. That role should never have rights to drop tables, change schemas, or add users.

Next, you restrict which columns the agent can read. ClickHouse lets you grant select rights on single columns inside a table. If a user only has rights on two columns, they can query only those two. Any query that asks for a third column will fail right away. A query that selects all columns with a star also fails and returns nothing. The star query only works if the user has rights to every column by default. Since version 24.10, you can also grant permissions across table prefixes using a trailing star.

Why native roles and column grants are not enough

Native database roles decide who can log in and which objects they can touch. Column grants stop an agent from reading restricted fields altogether. Row policies and quotas help limit how much data a user can pull. These built-in tools are a strong first step for any team.

Still, static database grants cannot inspect what a query does in flight. An agent often needs to read a customer table to answer support questions. If you grant select on the email or phone number column, the raw text returns. If you revoke that column grant, the agent fails when a query includes that field. Static grants are all or nothing for every column you allow by default.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

This creates a tough gap for teams building with AI models. You may want the agent to query a table that holds emails or card numbers. At the same time, you want masking to rewrite matched fields in results. Native user grants also require you to place a database password in the app host. If that secret leaks from the app settings, anyone on that allowed host can log in. Finally, native roles do not pause a sudden bad query for a human to check.

How to keep agent queries safe in daily work

Start by mapping the exact questions your AI agent needs to answer. List the tables and columns required for those answers and nothing more. Build a dedicated ClickHouse role that holds select grants on only those columns. Attach a settings profile and a quota so one bad prompt cannot overload the server. Restrict the user account to the specific network host where your agent runs.

Test your agent with sample prompts to see how it writes SQL queries. Remember that an AI tool will often try a star query first. Because ClickHouse blocks star queries on partial column grants, tell your prompt which columns exist. Giving the model a clear list of allowed columns stops failed runs and saves time.

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 do star queries fail after I set up column grants in ClickHouse?

ClickHouse blocks star queries when a user only has rights to some columns in a table. The query returns no data at all unless the user can read every single column. You should guide your agent to name the exact allowed columns in each select statement.

How do I turn on SQL access control for ClickHouse users and roles?

You must set the access control storage path on the ClickHouse server first. You also need at least one user account with access management and named collection control set to one. After that, you can create users, roles, and grants with standard SQL statements.

Can ClickHouse column grants mask emails or card numbers in query results?

No, native column grants only allow or block access to a column completely. If you grant select on a column with phone numbers or national IDs, the database returns the raw values. To rewrite matched fields in results while keeping queries working, you need dynamic data masking.

The query runs. The emails stay hidden.