How to Configure Flowise ClickHouse Data Masking

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up flowise clickhouse data masking by limiting which columns the bot can read. You also use database views to rewrite emails, phone numbers, and card numbers in results. Most teams start by giving the bot a database account with wide read rights. When permissions are too broad, a bad prompt can pull raw private records into the chat. A safer setup uses strict user roles and restricted column grants inside the database. How flowise clickhouse data masking works in the database ClickHou

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 data masking by limiting which columns the bot can read. You also use database views to rewrite emails, phone numbers, and card numbers in results. Most teams start by giving the bot a database account with wide read rights. When permissions are too broad, a bad prompt can pull raw private records into the chat. A safer setup uses strict user roles and restricted column grants inside the database.

How flowise clickhouse data masking works in the database

ClickHouse manages users, roles, row rules, settings profiles, and quotas through SQL commands. To turn on SQL access management, you first set a storage path on the server. You also need at least one admin user with access management rights turned on. That admin can only hand out rights that they already hold themselves. Creating a new account for your AI app requires the create user right.

When you create the app user, you pick a login method and allowed hosts. You also assign default roles and specific user settings for that bot account. Instead of giving full table access, you grant read rights on safe columns only. If a user only has rights on two columns, queries for other columns fail. Wildcard queries that ask for all columns also fail and return no data at all. A wildcard query only works if the table holds just the allowed columns. Since version 24.10, you can also use a trailing star to grant rights by prefix.

When the AI workflow needs a column with national IDs, you can create a view. The view transforms the sensitive text before the bot reads the query output. You then grant the bot read access to the view instead of the base table.

What native database rules solve and what they miss

Native database roles and column grants stop the AI app from reading forbidden tables. They let you lock down which hosts the bot can log in from. Row rules and custom views help filter out rows or rewrite known columns. This approach works well when your data structure stays the same every day.

Still, native access rules still leave an operational gap for AI workflows. Database roles only check who logs in and which columns a query names. They do not inspect what individual statements or free-text columns hold in flight. If an engineer adds a new column with phone numbers, someone must update the view. If emails or card numbers sit inside unstructured log columns, column grants cannot split them. The database either blocks the whole text column or sends the full string back. Teams also struggle when many bots share one static database login key. Auditing which specific user prompt triggered a sensitive query becomes very hard.

Data Masking (Static) + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Best practices for safer AI database queries

Start by creating a dedicated ClickHouse role just for your Flowise workflows. Never share admin accounts or human analyst logins with an automated chat tool. Grant select rights only on the exact columns that the workflow needs to read. Avoid wildcard table grants unless every column in that table is public.

Next, audit your tables for emails, phone numbers, card numbers, and national IDs. Keep those fields out of the column list you grant to the bot role. Check your schema often so new tables do not slip past your role rules. Set quotas on the bot user so a loop cannot overload the server. Restrict the allowed host list so only the app server can log in.

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

What happens when Flowise runs a select star query on a restricted table?

ClickHouse refuses the wildcard query and returns no data at all. The query only works if the user has rights on every column in that table by default.

Can ClickHouse grant read access to tables that share a name prefix?

Yes, starting in version 24.10, you can place a trailing star on a table prefix. This grants read rights across all tables that start with that specific name.

How does ClickHouse store users and roles created with SQL?

The server saves users, roles, row rules, and quotas in a local storage directory. An admin must first enable SQL access management in the server settings.

The query runs. The emails stay hidden.