How to Configure LlamaIndex ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up llamaindex clickhouse read only access by creating a restricted database user. Then you link that user to your text-to-SQL query engine with a table list. Many teams start by giving their AI app an admin account during testing. That broad role lets generated queries alter tables or read every column by mistake by default. How to set up llamaindex clickhouse read only access LlamaIndex connects to relational databases through a light wrapper around SQLAlchemy. Its natural language q

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 llamaindex clickhouse read only access by creating a restricted database user. Then you link that user to your text-to-SQL query engine with a table list. Many teams start by giving their AI app an admin account during testing. That broad role lets generated queries alter tables or read every column by mistake by default.

How to set up llamaindex clickhouse read only access

LlamaIndex connects to relational databases through a light wrapper around SQLAlchemy. Its natural language query engine turns user questions into SQL queries and runs them. If you do not name specific tables, the engine pulls the whole database schema. LlamaIndex notes that running arbitrary generated SQL carries real security risks. The framework guides advise teams to use restricted roles, read-only databases, and sandboxing.

ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL access control. To turn this on, a server needs a storage path for access files. It also needs an admin account with user management rights enabled. That admin can create a new user for the AI app. Remember that a user can only grant rights that they already hold themselves. During setup, the admin sets the login method, allowed client hosts, and default roles.

Next, the admin grants read permissions on the specific tables the agent needs. ClickHouse also supports read grants on individual columns within a table. When a user only has rights on two columns, queries on other columns fail. A wildcard query across all columns also fails and returns no data at all. The wildcard query only works if the table holds just those granted columns. Newer ClickHouse releases also let admins grant read rights across tables sharing a prefix.

Why you should scope tables in your query engine

Database grants are only half of a clean text-to-SQL setup. You also need to tell the LlamaIndex engine which tables to inspect. By default, the engine reads schema context from every table in the database. That behaviour wastes prompt tokens and confuses the language model with unrelated columns. Worse, if a wildcard query hits a partially restricted table, ClickHouse rejects the query.

You can fix this by passing an explicit table list to the query engine. The engine then builds prompts using only the tables you named. You should also instruct the prompt to ask for specific column names. That habit avoids full-table wildcard queries that fail under column-level read grants. Scoping both the database user and the query engine keeps queries fast and predictable.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native read roles solve and what they leave open

Native ClickHouse roles and column grants stop an AI app from changing your data. They block drop statements, row deletes, and reads on unapproved tables. They also limit which client hosts can log in with the app credentials. For basic schema isolation, native database access control works very well.

Still, static database roles only check who logs in at the start. They do not inspect what individual queries do while in flight. Suppose a permitted table holds customer emails, phone numbers, card numbers, or national IDs. If the agent has read rights on those columns, raw values enter the prompt. Column grants block a column for everyone, or show it in full. They cannot dynamically rewrite matched fields in results based on context.

Static credentials also sit in app settings files or environment variables. Anyone who reads that key can run heavy analytical scans from an allowed host. Also, the database log only shows that the shared app user ran a query. It does not tie each generated statement back to the person or service asking.

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 wildcard queries fail when using ClickHouse column grants?

ClickHouse blocks wildcard queries when a user only has read rights on specific columns. The query returns no data unless the table contains only the granted columns. Your text-to-SQL engine must list the allowed column names in every query.

How does LlamaIndex pull database schema context for text-to-SQL?

LlamaIndex uses a light wrapper around SQLAlchemy to inspect your database tables. If you do not specify a table list, it pulls the entire schema context. Passing a clear list of table names limits what the query engine inspects.

Can ClickHouse restrict which hosts the LlamaIndex app logs in from?

Yes, the user creation statement in ClickHouse can define allowed client hosts. It also sets the login method, default roles, and per-user settings profiles. Requests coming from outside those approved hosts are rejected at login.

The query runs. The emails stay hidden.