How to Configure Flowise ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up flowise clickhouse prevent delete controls by assigning a restricted database role to your app. This role only allows read queries and blocks all table drops or row mutations. Teams often link AI workflows to their analytical database using a default admin account. That broad account lets an automated agent alter or wipe out production data by mistake. How to set up flowise clickhouse prevent delete roles ClickHouse manages users, roles, row policies, settings profiles, and quotas

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 flowise clickhouse prevent delete controls by assigning a restricted database role to your app. This role only allows read queries and blocks all table drops or row mutations. Teams often link AI workflows to their analytical database using a default admin account. That broad account lets an automated agent alter or wipe out production data by mistake.

How to set up flowise clickhouse prevent delete roles

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities created with SQL. Before you make a restricted account, you must turn on SQL-driven access control in the server. This setup needs a storage directory and an admin user with access management turned on. Once enabled, an admin can create a new user or role just for your Flowise workflows. Remember that a database user can only grant privileges that they hold themselves.

Creating a new user requires the create user privilege on the server. When you create the account, you set the login method and the allowed client hosts. You also assign default roles and per-user settings at creation time. To stop deletions, you never grant drop, truncate, or alter privileges to the Flowise user. Instead, you grant select privileges only on the specific tables your AI agent needs to read.

Restricting queries with column grants and row policies

Even when an AI agent cannot delete rows, it can still read too much data. You can narrow what Flowise reads by granting select access on specific columns only. When you grant select on chosen columns, the user can query only those exact columns. If the agent tries to select an ungranted column, the database blocks the query. A wildcard query that selects all columns also fails and returns no data at all. That wildcard query only works if the table holds just the granted columns.

If your database runs version 24.10 or newer, you can also grant access by name prefix. A trailing asterisk in the grant statement covers all tables that share that starting name. This helps when your AI workflows read from many daily or regional log tables. You can also combine column grants with row policies and quotas to limit resource use. These native controls ensure that a bad prompt cannot erase tables or scan forbidden columns.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Where native database roles fall short for AI agents

Native database users and roles solve the basic problem of who can log in. They also stop broad command types like dropping a table or deleting rows. Still, static database credentials still sit inside your Flowise app settings. Anyone who can read the app setup can extract those credentials and run queries outside the app.

Database roles also treat every query from the Flowise account the same way. When ten different team members trigger a Flowise workflow, the database logs only one shared user. You cannot tell which human user or workflow run caused an expensive or risky query. Also, column grants are all-or-nothing rules. If a column holds customer emails or phone numbers, you must either block the column or expose it. Native grants do not rewrite matched fields like card numbers or national IDs inside query results. Teams need a way to inspect every query in flight and mask sensitive fields dynamically.

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 Flowise agent from deleting ClickHouse data?

Create a dedicated ClickHouse user for Flowise and grant it only select privileges on required tables. Without alter, delete, truncate, or drop grants, the database rejects any attempt to remove data.

Why do wildcard select queries fail after I set up column grants?

When you grant select on specific columns only, ClickHouse blocks wildcard queries that select all columns by default. The query will return no data unless the table contains only the columns you granted.

What do I need before creating SQL roles in ClickHouse?

You must turn on SQL-driven access control by setting a storage directory on the server. You also need at least one initial account with access management privileges turned on.

The query runs. The emails stay hidden.