How to Configure Vercel AI SDK ClickHouse Connect

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up vercel ai sdk clickhouse connect by giving your app a restricted database role. Then you wire that client into a tool call inside your AI handler. Engineering teams often start this build by using a shared admin login for speed. That broad role lets a model read every table or drop data by mistake. A safer path uses SQL access rules to limit what the AI tool can read. Setting up vercel ai sdk clickhouse connect with SQL roles First, turn on SQL-driven access control in your 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 set up vercel ai sdk clickhouse connect by giving your app a restricted database role. Then you wire that client into a tool call inside your AI handler. Engineering teams often start this build by using a shared admin login for speed. That broad role lets a model read every table or drop data by mistake. A safer path uses SQL access rules to limit what the AI tool can read.

Setting up vercel ai sdk clickhouse connect with SQL roles

First, turn on SQL-driven access control in your ClickHouse server settings. This setup needs a storage path and an admin user with management rights. Once that is active, you can manage users, roles, row policies, and quotas with SQL. Creating a new user for your app requires the user creation privilege. Remember that an admin can only grant rights that they hold themselves.

When you create the app user, you set its login method and allowed hosts. You can also assign default roles and specific user settings at creation time. Next, you define what data the AI tool can read during a user chat. ClickHouse lets you grant read rights on single columns instead of full tables. If you grant access to two columns, the user can query only those two fields.

A full table wildcard query will fail and return no data at all. That wildcard query only works if the table holds just the granted columns. Starting in version 24.10, you can also grant read rights using a table name prefix. After the database role is ready, you pass those user details to your client library. The AI SDK tool function then runs queries through this restricted client during chat runs.

What native roles solve and what they leave open

Native ClickHouse roles and column grants do a great job of blocking unauthorized table reads. They make sure the AI app logs in from expected hosts with a known role. Settings profiles and quotas also help cap resource use for that single database login. Still, static database grants only check who logs in and which columns they pick.

Once a column is open to the AI role, every row value flows back by default. A shared column might hold plain text notes with customer emails or phone numbers. Static column grants cannot rewrite matched card numbers or national IDs inside those text results. Also, the database sees every query coming from the same single app user account.

ClickHouse Access Management + AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

That makes it hard to tie a slow or risky query to one end user. If a prompt tricks the model into running a bad query, native grants cannot pause it. You need a way to inspect individual statements in flight and mask sensitive fields dynamically.

Best practices for AI tool database queries

Keep the tool description in your AI code clear and narrow for the model. Tell the model the exact column names it is allowed to request in queries. Never let the model run wildcard column queries against tables with restricted columns. Build separate tool functions for specific questions instead of one open SQL runner tool.

Use ClickHouse row policies and quotas to keep big scans from hurting server speed. Review your database logs often to see which queries the AI app runs most. Combine strict database grants with runtime checks to keep customer data safe during AI chats.

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

Why do wildcard queries fail with column grants in ClickHouse?

When you grant read rights on specific columns, ClickHouse blocks full wildcard queries by default. A wildcard query only works if the user has rights on every column in that table by default.

How do you turn on SQL access control in ClickHouse?

You set the access control storage path in your server config file first. You also need at least one user account with access management rights turned on.

Can ClickHouse column grants hide emails inside a text column?

No, native column grants only allow or block access to the entire column itself. To rewrite emails, phone numbers, or card numbers in results, you need dynamic data masking.

The query runs. The emails stay hidden.