How to Configure Haystack ClickHouse Permissions

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up haystack clickhouse permissions by creating a restricted database user with SQL roles. Then you give your AI pipeline read rights only on specific tables and columns. Many teams start by giving their Haystack app a shared admin account. That broad role lets the pipeline read every table or drop data by mistake. A safer setup uses tight database rules to limit what the app can touch. How haystack clickhouse permissions work in practice ClickHouse treats users, roles, row policies, s

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 haystack clickhouse permissions by creating a restricted database user with SQL roles. Then you give your AI pipeline read rights only on specific tables and columns. Many teams start by giving their Haystack app a shared admin account. That broad role lets the pipeline read every table or drop data by mistake. A safer setup uses tight database rules to limit what the app can touch.

How haystack clickhouse permissions work in practice

ClickHouse treats users, roles, row policies, settings profiles, and quotas as access items. You can build and manage all of these items with standard SQL statements. First, you must turn on SQL-driven access control in the database server. This step needs a storage folder set in the server access path rule. You also need at least one initial account with access management turned on. That admin user also needs named collection control set to one.

Once that is active, an admin with the right to create users makes an account. The admin sets the login method and limits which hosts can log in. The admin can also set default roles and user settings during creation. Next, the admin grants specific read or write rights to the new role. In ClickHouse, a user can only grant rights that they already hold themselves.

For a Haystack pipeline, you can grant read rights on specific columns only. The app can then query those exact columns to fetch documents or vectors. If the app tries to read other columns, the database blocks 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 allowed columns. Since version 24.10, you can also grant rights across tables that share a name prefix. You do this by adding a trailing star to the table name.

What native database roles solve and what they miss

Native database roles and column grants solve the basic login problem well. They stop a Haystack app from reading secret tables or changing system settings. You can lock the pipeline to specific hosts and limit its total resource quota. If a pipeline only needs vector columns, column grants block access to other fields.

ClickHouse Access Management + AI Agent Permissions: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static database roles only check who logs in at the start. They do not inspect what each query does while it runs in flight. A Haystack text column might hold raw emails, phone numbers, or card numbers. If the pipeline has read rights on that column, it pulls everything inside. Native grants cannot mask a national ID inside a text chunk during a query. Also, every Haystack node using that role shares the exact same database identity. When a bad query runs, the database log only shows the shared role name. Teams cannot see which user prompt or app flow triggered the database read. Static credentials also sit in app files, where someone might leak or copy them.

Steps to lock down your pipeline access

Start by listing the exact tables your Haystack document store needs to read. Separate your indexing jobs from your search jobs by giving each its own role. Give the search role read rights only on the text and vector columns. Avoid using wildcard queries in your custom retrieval code so column grants work well. Restrict the host list on the user account to your app server network. Finally, review your granted roles often to remove old table rights right away.

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 runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. 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?

ClickHouse blocks wildcard queries when a user only has rights on select columns. The query returns no data unless the table contains only those allowed columns.

How do you enable SQL role management in ClickHouse?

You set the access storage folder path in the server settings file. You also enable access management and named collection control on an admin user.

Can a Haystack role grant read access to another user?

A user in ClickHouse can only grant privileges that they already hold themselves. A read-only Haystack role without grant rights cannot give access to anyone else.

The query runs. The emails stay hidden.