How to Configure Make ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up make clickhouse read only access, create a dedicated database user. Grant that user only read rights on specific tables or columns. Teams often connect automation workflows using a shared admin account to save time. This broad setup creates big risks when an automated task runs a bad query. A wrong write command can change or delete production records by mistake. How to set up make clickhouse read only access ClickHouse manages users, roles, row rules, 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.

To set up make clickhouse read only access, create a dedicated database user. Grant that user only read rights on specific tables or columns. Teams often connect automation workflows using a shared admin account to save time. This broad setup creates big risks when an automated task runs a bad query. A wrong write command can change or delete production records by mistake.

How to set up make clickhouse read only access

ClickHouse manages users, roles, row rules, settings profiles, and quotas as access objects. You can build and manage all of these objects using standard SQL commands. First, you must turn on SQL-driven access control in your server setup. This step needs a storage folder defined in the server path parameter. It also requires at least one existing user with access management turned on. That admin user also needs named collection control set to one.

Once that is ready, an admin can create a new user for Make. Creating a user requires the create user privilege on the server. A user can only grant the rights that they already hold themselves. During user creation, you can define how the account logs in. You can also limit which hosts the account can connect from. You can assign default roles and set specific settings for that user.

After creating the account, you grant read rights to the new user. You can grant select rights on a whole table or just a few columns. If you grant select on two columns, the user can query only those columns. Queries that ask for other columns will fail right away. A select all query will also fail and return no data at all. Select all only works if the table holds just the allowed columns. Since version 24.10, a trailing asterisk grants rights by table name prefix.

What native read rules solve and what they miss

Native database roles stop automation tools from running write commands or dropping tables. Column grants also help you block access to whole columns of private data. This setup works well when every user in a column shares the same rules. It keeps your basic automation workflows within a clear boundary.

Still, static database roles only check who logs in at the start. They do not inspect what individual queries return while they run in flight. Sometimes a Make workflow needs to read a text column that holds mixed data. That column might hold normal notes next to emails, phone numbers, or card numbers. If you grant select on that column, every value comes back in plain text. If you revoke select on that column, the automation workflow breaks right away.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Shared service accounts also make it hard to track who built which workflow. Every run logs in with the exact same database user name. Your server logs show the shared account, not the person who triggered the task. Inaddition, storing long-lived database keys inside third-party tools adds risk. If someone copies those keys, they can read your data from outside your network. Teams need a way to rewrite sensitive fields like national IDs in query results.

Managing credentials and query results in flight

To reduce risk, teams should separate login keys from query execution rules. You should rotate database keys often and limit host access to known addresses. You should also review your column grants whenever you add new tables. Combining strict SQL grants with result masking keeps data safe during automation runs. When masking rewrites matched fields in results, workflows run without exposing raw card numbers.

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 does a select all query fail for a read-only ClickHouse user?

When you grant select on specific columns, ClickHouse blocks select all queries by default. The query returns no data unless the table contains only the granted columns. Update your Make scenario to list the exact column names in each query.

What server settings do I need before creating SQL users in ClickHouse?

You must set up a storage directory using the access control path parameter. You also need at least one user with access management set to one. That user must also have named collection control set to one.

Can I grant read access to multiple tables at once in ClickHouse?

Yes, since version 24.10, you can use a trailing asterisk in your grant statement. This lets you grant select rights across multiple tables that share a name prefix. It saves time when your app creates new tables with the same prefix.

The query runs. The emails stay hidden.