How to Configure ClickHouse Kill Query

Published
Length
3 min read

Luis Felipe Dias

All posts

You configure a clickhouse kill query workflow by granting specific privileges to a user or role. This setup lets admins stop active queries that use too much memory or CPU. Most teams start by sharing a single admin account for daily database maintenance. That shared account often holds broad rights across every table in the cluster. When a runaway job locks up the server, an engineer logs in to stop it. Still, giving out full admin keys creates a clear risk for production data. How a clickho

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 configure a clickhouse kill query workflow by granting specific privileges to a user or role. This setup lets admins stop active queries that use too much memory or CPU. Most teams start by sharing a single admin account for daily database maintenance. That shared account often holds broad rights across every table in the cluster. When a runaway job locks up the server, an engineer logs in to stop it. Still, giving out full admin keys creates a clear risk for production data.

How a clickhouse kill query works

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

Once SQL-driven control is active, you can create a dedicated user or role. Creating a new user takes the specific create user privilege. During this step, you set how the user logs in and which hosts can connect. You can also assign default roles and specific user settings right away.

Next, you grant the right to stop running queries to that new role. In ClickHouse, a user can only grant the privileges that they hold themselves. An admin with the right level of control assigns the kill privilege to the support role. When a bad query slows down the cluster, the on-call engineer logs in. They look up the active process list to find the slow task. Then they run the command to stop that specific query by its ID. The server flags the process and frees up the locked memory and threads.

What native roles solve and what they leave open

Native roles and SQL grants solve the problem of basic login control. They let you replace one shared root password with named accounts for each person. You can also limit read rights down to single columns on a table. If a user only has grants on two columns, a full table read fails. The server refuses the request and returns no data at all. Since version 24.10, you can even grant table read rights using a name prefix.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Yet native access control mainly decides who can log in to the database. It does not inspect individual statements in flight or ask for peer review first. An engineer with rights to stop queries might kill a critical billing job by mistake. Also, static credentials still sit on laptops or inside local config files. If a developer needs to check slow queries and read tables, they might see sensitive fields. Native roles do not rewrite emails, phone numbers, card numbers, or national IDs in query results. Teams still need a way to approve sensitive actions and mask fields on the fly.

Best practices for stopping bad queries safely

Start by separating query stop privileges from broad data read or write roles. Give your on-call team a limited role meant only for checking cluster health. Combine this role with strict quotas and settings profiles to prevent bad queries first. Quotas cap how much resource a single user can spend over a set time. When you must stop a running query, verify the user and query ID first. Always log who ran the stop command and why they needed to step in. Clear audit trails help teams fix the root cause of slow reporting queries 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 sits inline in the native TCP protocol beside the server as a sidecar for your ClickHouse. For every production query an agent or human sends, hoop.dev decides whether to pass, mask, block, or escalate it for review.

With hoop.dev ClickHouse support, guardrails refuse banned queries before they run or hold risky calls for approval, and data masking rewrites matched string columns in results in memory. Furthermore, hoop.dev holds SOC 2 Type II and is open source on GitHub.

Frequently asked questions

Who can grant the privilege to stop a query in ClickHouse?

A user can only grant privileges that they already hold themselves. You need an account with access management turned on or the specific grant option.

How do you turn on SQL-driven access control for query management?

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.

Can I limit what tables an on-call engineer reads while checking queries?

Yes, you can grant read access on specific columns or use table name prefixes. If they run a full table read without column grants, ClickHouse returns no data.

The query runs. The emails stay hidden.