How to Configure Haystack ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up haystack clickhouse read only access by creating a dedicated database user. Then you grant that user select rights on specific tables and columns. Teams often start by connecting their Haystack pipelines with a shared admin account. That shortcut lets an automated pipeline change or drop tables by mistake. A strict read-only role stops write queries before they alter your data. How to set up haystack clickhouse read only access ClickHouse manages users, roles, row policies, setting

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 read only access by creating a dedicated database user. Then you grant that user select rights on specific tables and columns. Teams often start by connecting their Haystack pipelines with a shared admin account. That shortcut lets an automated pipeline change or drop tables by mistake. A strict read-only role stops write queries before they alter your data.

How to set up haystack clickhouse read only access

ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL statements. Before you create a role for Haystack, you must enable SQL access control. This step needs a storage folder on the server for access data. You also need an admin user with access management rights turned on. Remember that a user can only grant rights that they already hold.

Once SQL access control is active, you create a new user for Haystack. Creating a user requires the create user privilege on the database server. During this step, you pick the login method and allowed client hosts. You also assign default roles and user settings for the pipeline account. Next, you grant select rights on the exact tables Haystack needs to read.

You can also limit select rights to specific columns in a table. If you grant select on two columns, Haystack can only read those two. A query that asks for all columns with an asterisk will fail. It returns no data unless the table only has those granted columns. You can also use a trailing asterisk to grant access by table prefix.

What native read-only roles solve and what they miss

Native database roles and column grants solve a major security problem for teams. They stop a Haystack pipeline from inserting, updating, or deleting production records. Host limits also stop outside servers from logging in with the pipeline key. If a prompt injection hits your app, the database blocks any write command.

Still, static database roles only check who logs in at the start. They do not inspect what individual queries do while they run in flight. A read-only user can still read every row in an allowed column by default. If that column holds emails or phone numbers, the pipeline reads them all. Column grants are all-or-nothing and cannot mask card numbers or national IDs.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static credentials also create key rotation work for your engineering team. You must store the database password inside the Haystack app settings. If a developer copies that key, they get the same read access. Database logs also show every query coming from one shared service account. You cannot tell which user request triggered a slow or broad query.

Why column grants can break automated queries

Automated retrieval pipelines often build SQL queries on the fly from user prompts. Many text-to-SQL components default to selecting all columns from a target table. In ClickHouse, column-level select grants reject full-table asterisk queries right away. This behavior is safe, but it breaks pipelines that do not list columns.

To fix this, teams must hardcode allowed column names in the Haystack prompt. Or they end up granting select rights on the whole table instead. Granting the whole table brings back the risk of exposing sensitive fields. Teams need a way to keep queries working while rewriting sensitive fields in 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

Can a ClickHouse read-only user still run select asterisk queries?

Yes, if the user has select rights on every column in the table by default. If you restrict select rights to specific columns, select asterisk queries will fail.

How do you limit which servers can log in as the Haystack user?

When you create the user in ClickHouse, you set the allowed host rules. The database will reject login attempts from any IP address not on that list.

Does a read-only role hide emails or card numbers inside allowed columns?

No, native select grants only control whether a query can read a column. To hide emails or card numbers in results, you need dynamic data masking.

The query runs. The emails stay hidden.