How to Configure Haystack ClickHouse Prevent Delete
You set up a haystack clickhouse prevent delete workflow by assigning a restricted database role. You give your AI pipeline a user account that lacks mutation and drop rights. Engineering teams often connect search pipelines to analytical stores using default admin profiles. This habit creates high risk when automated agents or retrieval steps run bad queries. If a pipeline holds broad permissions, one bad prompt can wipe out production tables. How a haystack clickhouse prevent delete setup wo
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 haystack clickhouse prevent delete workflow by assigning a restricted database role. You give your AI pipeline a user account that lacks mutation and drop rights. Engineering teams often connect search pipelines to analytical stores using default admin profiles. This habit creates high risk when automated agents or retrieval steps run bad queries. If a pipeline holds broad permissions, one bad prompt can wipe out production tables.
How a haystack clickhouse prevent delete setup works
ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. Admins create and update these entities with standard SQL commands. Before you start, you must turn on SQL-driven access control in the server. This step needs a storage directory set in the server access control path. You also need at least one initial account with access management turned on. Once you enable this feature, an admin can create a dedicated user for Haystack.
Creating a new user requires the create user privilege on the admin account. Remember that a user can only grant the privileges that they have themselves. When you create the pipeline user, you define its login method and allowed hosts. You also attach default roles and specific per-user settings profiles to that account.
To block deletes, you grant only read privileges or insert privileges to the role. You can even grant select rights on specific columns in a database table. The pipeline user can then run queries that read only those allowed columns. If the app runs a wildcard select, the database refuses the query and returns nothing. That rule applies unless the table contains only the exact columns you granted. Since version 24.10, a trailing asterisk lets you grant rights by table name prefix. By leaving out delete and drop privileges, the database rejects destructive statements right away.
What native roles solve and what they leave open
Native database roles stop unauthorized users from running destructive table mutations. If a Haystack component tries to delete rows, the database blocks the command. This setup works well when a pipeline only needs to read vector embeddings. Still, many AI workflows also need to write new documents and update old records. When you grant mutation privileges for routine updates, the account can delete data again.
ClickHouse Access Management + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Database roles decide who can log in and which broad command types can run. They do not inspect the content or context of individual statements in flight. A role cannot tell a safe single-row cleanup from an accidental full-table wipe. Static roles also cannot hide sensitive data inside the columns a user can read. If an AI agent reads a customer table, it receives every granted column value. This includes emails, phone numbers, card numbers, and national IDs stored in those fields. You must manage separate users and roles across every database instance your team runs.
Why runtime query checks matter for AI pipelines
AI pipelines build and run queries based on user input and model outputs. Static credentials stored in app settings give every request the exact same power. If an engineer logs in with the pipeline key, they get those same rights. Teams need a way to inspect each SQL statement before the database runs it. Checking queries in flight lets you block delete statements while keeping normal write workflows. It also lets you rewrite sensitive fields like emails or card numbers in results.
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
How do I stop a Haystack pipeline from deleting ClickHouse rows?
Create a dedicated database user for the pipeline using SQL access control. Grant only select and insert rights to that user role, and leave out delete permissions.
Why does a wildcard select fail when using column grants in ClickHouse?
When you grant select on specific columns, ClickHouse blocks wildcard queries on that table. The query returns no data unless the table holds only the granted columns.
What happens if the pipeline needs to update records without full delete access?
Native database permissions group updates and deletes into table mutation privileges. To separate them safely, you must inspect and filter individual SQL statements before execution.
Related guides
In this post