How to Configure LangChain ClickHouse Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a langchain clickhouse connect workflow by pairing an agent with a ClickHouse user role. That database role needs strict read rules so the AI model only runs safe queries. Teams often start by handing the agent a broad admin login to move fast. Broad database permissions let a single bad prompt alter tables or expose sensitive records. A safer setup starts inside the database before you wire up the AI tool. How to set up langchain clickhouse connect with SQL roles ClickHouse manage

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 a langchain clickhouse connect workflow by pairing an agent with a ClickHouse user role. That database role needs strict read rules so the AI model only runs safe queries. Teams often start by handing the agent a broad admin login to move fast. Broad database permissions let a single bad prompt alter tables or expose sensitive records. A safer setup starts inside the database before you wire up the AI tool.

How to set up langchain clickhouse connect with SQL roles

ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access items. To turn on SQL access control, an admin sets a storage directory on the server. At least one admin account needs access management rights enabled to create new accounts. Remember that a user can only grant rights that they already hold themselves.

When you create a user for your AI agent, you set the login method. You also define the allowed client hosts, the default roles, and the user settings. Instead of giving full table access, you can grant read rights on specific columns. If you grant read rights on two columns, the agent can query only those two. A query for all columns with an asterisk fails unless no other columns exist. Since ClickHouse version 24.10, a trailing asterisk lets you grant rights by table name prefix.

On the agent side, the framework links a chat model to one or more tools. The agent reads the user prompt and decides which tool to call to finish the job. In a SQL agent setup, the tool translates plain language into a SQL query. It then runs that query against the database and returns the output to the model.

How to scope column and table permissions for AI agents

Giving an agent access to a whole database schema invites both mistakes and slow queries. AI agents inspect table schemas to figure out how to write their SQL statements. If the database user sees hundreds of tables, the agent fills its context with noise. You can fix this by creating a dedicated role just for the AI app.

Start by picking the exact tables and columns the agent needs to answer questions. Grant select rights only on those specific columns to the new ClickHouse role. Leave out columns that hold emails, phone numbers, card numbers, or national IDs. If an agent tries to query a hidden column, the database blocks the query right away.

ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

You should also restrict the hosts that the agent user can log in from. Set the user host rule so only your app servers can open a session. Attach a quota and a settings profile to stop the agent from running huge scans. This keeps a confused AI loop from using up all your cluster memory.

What native database roles solve and what they leave open

Native ClickHouse roles and column grants do a great job of locking down static access. They decide which account can log in and which columns that account can read. Host checks and quotas also help keep basic resource use in check during normal runs.

Still, database roles only check who logs in at the start of a session. They do not inspect what individual statements or prompts try to do in flight. If an allowed column holds mixed text with emails or card numbers, the query returns them. A static role cannot rewrite matched fields in results before they reach the language model. Also, shared app credentials make it hard to tie a bad query back to one person. Teams need a way to inspect queries live and mask sensitive fields in the results.

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 does a select asterisk query fail for my ClickHouse agent user?

If you grant select rights on specific columns only, ClickHouse blocks select asterisk queries. The query only works if the table has no other columns besides the granted ones. Instruct your agent to list the exact column names in every select statement.

How does an AI agent run queries against a SQL database?

The agent pairs a chat model with a database tool that holds your login details. It turns a plain question into a SQL query, runs it, and reads the answer. The model decides when to call the database tool based on the user prompt.

Can ClickHouse grant read access to a group of tables at once?

Yes, starting in ClickHouse version 24.10, you can use a trailing asterisk on table names. This lets you grant select permissions across all tables that share the same name prefix. It helps when your agent needs to read from many daily or regional tables.

The query runs. The emails stay hidden.