How to Configure Vercel AI SDK ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

To set up vercel ai sdk clickhouse read only access, create a dedicated database user. Grant that user only select rights and pass its credentials to your query tool. Teams often start by reusing an admin account during early app testing. Broad database permissions create high risk when a language model writes its own SQL. A single bad prompt can drop tables, alter data, or read private columns. How to set up vercel ai sdk clickhouse read only access ClickHouse manages users, roles, row polic

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 vercel ai sdk clickhouse read only access, create a dedicated database user. Grant that user only select rights and pass its credentials to your query tool. Teams often start by reusing an admin account during early app testing. Broad database permissions create high risk when a language model writes its own SQL. A single bad prompt can drop tables, alter data, or read private columns.

How to set up vercel ai sdk clickhouse read only access

ClickHouse manages users, roles, row policies, settings profiles, and quotas as access entities. You can create and manage all of these entities with SQL statements. Turning on SQL-driven access control requires a storage directory on the server. It also requires at least one user with access management rights turned on. A database user can only grant privileges that they hold themselves.

Creating a new user requires the create user privilege in ClickHouse. That user statement sets the login method and the allowed client hosts. It can also assign default roles and specific user settings profiles. Once you create the user, you grant select rights on target tables. You can even grant select rights on specific columns in a table. The user can then run queries that read only those allowed columns. If a query asks for other columns, the database refuses the request. A wildcard select also fails unless the table holds only the granted columns. Recent database versions also let a trailing asterisk grant rights by table prefix.

Next, you wire this restricted database user into your Vercel AI SDK tool. Your app backend holds the database login details in server variables. You define a tool function that accepts a SQL query from the model. When the model calls the tool, your backend logs in as the read-only user. The database checks the user role and runs the select query.

How column grants and row policies work in practice

Column grants help you keep sensitive columns away from model queries. You list each safe column when you grant select rights to the role. This step takes careful planning across every table in your analytics schema. Whenever your team adds a new safe column, you must update the grants. If a model generates a full wildcard select, ClickHouse rejects the query right away. You must instruct the model in your prompt to name specific columns.

Row policies and quotas add another layer of control for analytics apps. Quotas limit how much resource usage a user can consume over time. Row policies filter which rows a user can read from a table. Settings profiles let you lock down query rules for that specific user. Together, these native controls build a strong wall around your write operations.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native read-only roles solve and what they leave open

Native ClickHouse access controls solve the problem of unauthorized data changes. A user with only select grants cannot insert, update, or drop tables. Host limits and login rules also make sure only your app server connects. Column grants stop the model from reading columns you exclude in advance.

Still, static database roles only decide who logs in at the start. They do not inspect what individual queries do in flight within allowed columns. An analytics column might still hold raw emails, phone numbers, or card numbers. If a user needs that column for counts, native grants allow full reads. The database sends raw text back to the model and into third-party logs. Static roles also share one database identity across every end user in your app. Audit logs show the shared app user instead of the person behind the prompt. Teams also have to manage static database credentials across their app servers.

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 wildcard select fail on a read-only ClickHouse user?

ClickHouse blocks wildcard queries when a user only has column-level select grants. The wildcard query only works if the user has rights to every column by default.

Can a ClickHouse user grant read-only access to another user for the SDK?

Yes, if that user has the create user privilege and access management rights. Remember that a ClickHouse user can only grant privileges they already hold themselves.

Do read-only grants hide sensitive text inside allowed ClickHouse columns?

No, native select grants work at the table or column level. If a column is allowed, the query returns all raw values in it. Data masking is needed to rewrite emails, phone numbers, or national IDs.

The query runs. The emails stay hidden.