How to Configure Flowise ClickHouse Least Privilege
You set up flowise clickhouse least privilege by making a scoped SQL user and role. Then you grant read rights on only the specific tables and columns your app needs. Many teams start by giving their AI workflows a broad admin user to move fast. That shortcut lets an automated agent read private tables or change data by mistake. How flowise clickhouse least privilege works in native SQL ClickHouse treats users, roles, row policies, settings profiles, and quotas as SQL access entities. Before
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 least privilege by making a scoped SQL user and role. Then you grant read rights on only the specific tables and columns your app needs. Many teams start by giving their AI workflows a broad admin user to move fast. That shortcut lets an automated agent read private tables or change data by mistake.
How flowise clickhouse least privilege works in native SQL
ClickHouse treats users, roles, row policies, settings profiles, and quotas as SQL access entities. Before you create these entities, you must turn on SQL-driven access control on the server. This step needs a storage folder set in the server access control path setting. It also needs at least one account with access management and named collection control set to one.
Once you turn that on, an admin with the create user right can add a dedicated account for Flowise. ClickHouse enforces a strict rule here. A user can only grant the privileges that they already hold themselves. When you create the account, you can set the login method and default roles. You can also set user settings and limit which hosts the account can log in from.
To lock down what Flowise reads, you grant select rights on specific columns rather than full tables. If you grant read access on just two columns, the app can query only those two columns. Any query that asks for another column will fail. A wildcard query that asks for all columns will also fail and return no data at all. The wildcard query only works if the table holds just the allowed columns. Since version 24.10, you can also use a trailing asterisk to grant rights by table name prefix.
How to scope roles and row policies for AI workflows
Putting permissions inside a role makes your setup easier to run over time. You build a role for the Flowise workflow and attach the exact column grants to it. Then you assign that role as the default role for the Flowise database user. If the workflow changes later, you update the role instead of rebuilding the user account.
You can also add row policies and quotas as SQL access entities to tighten control. Row policies filter which rows the account can read based on a rule you write. Quotas limit how much work the account can do in a set time window. Together with host limits, these native controls stop an unauthorized machine from logging in with the app key.
Least Privilege Principle + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native grants solve and the gap they leave
Native SQL roles and column grants do a great job of limiting static table access. They make sure the Flowise account can only log in from approved hosts. They also stop the app from reading restricted columns or dropping tables.
Still, static grants only check who logs in and which columns a query names. They do not inspect the actual values returned inside an allowed column while a query runs. An allowed text column might still hold emails, phone numbers, card numbers, or national IDs. If a prompt triggers a broad select on that allowed column, raw values flow back to the model. Data masking is needed to rewrite matched fields in results before they reach the caller. Static database accounts also require you to store a password or key inside the Flowise app settings.
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
Why do wildcard queries fail after I set up column grants?
ClickHouse blocks a select-all query when a user only has grants on specific columns. It returns no data unless the table contains only the exact columns you granted.
What server settings do I need before I create SQL users and roles?
You must configure the access control path directory on the server first. You also need an initial account with access management and named collection control set to one.
Can a Flowise admin user grant permissions it does not have?
No, ClickHouse blocks any user from granting rights they do not hold themselves. The account making the grant must also hold the create user privilege to add new accounts.
Related guides
In this post