How to Configure ClickHouse Connection Pooling
You set up clickhouse connection pooling by keeping open database links inside your client app. Your app reuses those warm links instead of opening a new one for each query. Most teams configure the pool to log in with a single shared database role. When that shared role has broad permissions, every query in the pool gets full rights. How clickhouse connection pooling works with database roles Analytical databases handle heavy reads, and opening new network links for each query adds latency.
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 clickhouse connection pooling by keeping open database links inside your client app. Your app reuses those warm links instead of opening a new one for each query. Most teams configure the pool to log in with a single shared database role. When that shared role has broad permissions, every query in the pool gets full rights.
How clickhouse connection pooling works with database roles
Analytical databases handle heavy reads, and opening new network links for each query adds latency. A pool keeps a set number of links open and ready for incoming work. When an app thread needs data, it borrows an open link from the pool. After the query finishes, the app returns the link to the pool for reuse.
On the server side, ClickHouse manages users, roles, row policies, settings profiles, and quotas. Admins create and manage these entities through SQL commands once they set a storage path. An admin user needs access management rights enabled to create new accounts and roles. A user can only grant the privileges that they hold on their own account.
When an admin creates a user for a pool, they set the login method. They can also restrict which host addresses the pool user may connect from. Admins assign default roles and specific user settings to that shared account at creation. You can also grant read rights on specific table columns to limit data exposure.
If a role only has rights on two columns, queries for other columns fail. Running a wildcard query that asks for all columns will also fail right away. It returns no data unless the table holds only those exact allowed columns. Since version 24.10, a trailing asterisk lets admins grant permissions by table name prefix.
Why pooled roles need tight permissions
A pool works best when many app threads share the same warm database links. To make this work, teams often create one service user for the whole app. That single user needs enough permissions to run every query that any thread sends. Over time, that shared pool role gathers wide read and write rights across tables.
This setup creates a problem when engineers or internal tools also share pooled links. The database only sees the shared pool account, not the person behind the query. You lose a clear audit trail of which human or script ran a command. If one bad query runs through the pool, it carries the full role rights.
Connection Pooling Security + ClickHouse Access Management: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Column grants help restrict what a pool user can read from a given table. Yet managing column grants for dozens of tables and different team roles takes work. Every time a table schema changes, someone must update the grants for the pool. Also, anyone who gets the shared pool password can log in from allowed hosts.
What native pooling and access rules leave open
Native roles and client pools solve connection speed and basic login rules at scale. The pool stops your app from wasting time on network handshakes for every request. Meanwhile, SQL access rules decide which host and user account can log in. Together, they keep query latency low and block unknown hosts from reaching the server.
Still, this native setup only checks who logs in when the pool starts up. It does not inspect what individual queries do in flight inside an active session. Once a pooled link is open, the server trusts every command on that link. Also, column grants block whole queries rather than masking sensitive fields in the results.
If a table holds emails, phone numbers, card numbers, or national IDs, choices are limited. You must either block the column for the whole pool or expose raw text. Native pooling cannot rewrite matched fields in query results based on who asked. It also cannot tie a single pooled query back to an individual user identity.
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 inline in the protocol beside the server as a sidecar for your ClickHouse. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human approval.
First, hoop.dev guardrails block disallowed deletes, drops, and alter mutations or hold them for review. Then hoop.dev data masking rewrites sensitive string columns in responses in memory and logs each decision per resource. Read the docs and source on GitHub.
Frequently asked questions
Why do wildcard queries fail when using column grants with a pool?
ClickHouse blocks wildcard queries when a user only has grants on specific table columns. The query returns no data unless the table contains only those exact granted columns.
How do I turn on SQL role management for my pool accounts?
You must configure the access control storage directory parameter on the database server first. You also need at least one user account with access management rights set to one.
Can a pool admin user grant any permission to a new service role?
No, ClickHouse enforces a strict rule on privilege grants for all database users. A user can only grant the specific privileges that they already hold themselves.
Related guides
In this post