How to Configure Make ClickHouse Prevent Delete

Published
Length
3 min read

Luis Felipe Dias

All posts

To make clickhouse prevent delete actions from Make, set up restricted database roles. Grant only read and insert rights to the account that Make uses. Many teams connect Make to their database using a shared admin login. This broad role lets a bad workflow wipe out production tables by mistake. Restricting the database user stops destructive queries before they run. How to make clickhouse prevent delete with SQL roles ClickHouse manages users, roles, row policies, settings profiles, and quot

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 make clickhouse prevent delete actions from Make, set up restricted database roles. Grant only read and insert rights to the account that Make uses. Many teams connect Make to their database using a shared admin login. This broad role lets a bad workflow wipe out production tables by mistake. Restricting the database user stops destructive queries before they run.

How to make clickhouse prevent delete with SQL roles

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You create and control all of these entities using standard SQL statements. First, you must turn on SQL-driven access control on your database server. This setup needs a storage directory set in the server access path parameter. You also need at least one initial account with access management turned on.

Once you enable SQL access control, you can create a dedicated user for Make. Creating a user requires the create user privilege on your admin account. Remember that a user can only grant privileges that they already hold themselves. When you create the Make user, you define its login method and allowed hosts. You also assign default roles and specific settings profiles to that account.

To block deletes, you build a role that omits mutation and drop rights. You grant only select and insert rights on the target tables to this role. You can even grant select rights on specific columns rather than whole tables. If a user only has column grants, a full wildcard read will fail. It returns no data unless the table contains only those granted columns. Since version 24.10, you can also grant rights by table name prefix. A trailing asterisk on the table name covers all matching tables at once. Finally, you assign this safe role as the default for your Make user.

What native roles solve and where gaps remain

Native roles are great at stopping unauthorized table drops and bulk deletes. If a Make scenario tries to delete rows, the database rejects the query. Column grants also help you limit which fields the automation tool can query. This setup keeps a broken workflow from ruining your core analytics tables.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Still, static database roles only check who logs in at the start. They do not inspect what individual statements do while they run in flight. Make still holds a static password or key that never expires on its own. If someone steals that key, they can run heavy queries from allowed hosts.

Also, column grants are an all-or-nothing rule for reading sensitive fields. Sometimes a Make workflow needs to read a table that holds customer data. If you grant read access, raw emails, phone numbers, and card numbers flow out. If you revoke access to that column, the whole Make query breaks. Native roles cannot rewrite matched fields like national IDs in query results. Teams also lack a clear log tying each Make run to one specific query. And if a person needs temporary delete rights for a fix, roles stay static. Someone has to grant the right by hand and remember to revoke it later.

Add runtime guardrails and masking beside ClickHouse

Database grants and network rules control who can reach ClickHouse. Even so, an allowed client or agent can send an alter mutation that deletes rows or read raw personal data. In practice, hoop.dev runs beside the server as a sidecar for your ClickHouse and checks native TCP queries with no change to your drivers.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run, and data masking rewrites matched string columns in results. The audit trail logs which entity types were masked and how many without storing raw values. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

How do I stop a Make workflow from deleting ClickHouse data?

Create a dedicated database user for Make and assign a custom SQL role. Grant only select and insert privileges to that role, leaving out delete rights.

Can I restrict which columns Make can read in ClickHouse?

Yes, ClickHouse lets you grant select privileges on specific columns in a table. Queries that request other columns or use a wildcard will fail and return nothing.

What is needed to turn on SQL access control in ClickHouse?

You must set a storage directory in the server access control path setting. You also need at least one user account with access management set to one.

The query runs. The emails stay hidden.