How to Configure Zapier ClickHouse Read Only Access

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up zapier clickhouse read only access by creating a limited database user. Then you grant that user select privileges on specific tables or columns. Many teams connect Zapier to ClickHouse using a default admin account to save time. This shortcut gives every automated workflow full power to change or delete production tables. A broken Zap or a leaked key can then wipe out critical business data. How to set up zapier clickhouse read only access ClickHouse manages users, roles, row poli

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 zapier clickhouse read only access by creating a limited database user. Then you grant that user select privileges on specific tables or columns. Many teams connect Zapier to ClickHouse using a default admin account to save time. This shortcut gives every automated workflow full power to change or delete production tables. A broken Zap or a leaked key can then wipe out critical business data.

How to set up zapier clickhouse read only access

ClickHouse manages users, roles, row policies, settings profiles, and quotas through SQL statements. Before you create a user, you must turn on SQL-driven access control. This step requires a storage directory on the server and an admin account. That admin account needs user management rights turned on first. Note that a database user can only grant privileges that they already hold.

Once SQL management is active, you create a dedicated user account for Zapier. The user creation process requires the privilege to create users on the server. During this step, you define how the account logs in to the database. You also restrict which hosts the Zapier user can connect from. You can assign default roles and apply custom settings profiles to this account.

Next, you grant read permissions to the new Zapier user or its role. You do not need to expose an entire database to your automation tool. ClickHouse lets you grant select rights on single tables or specific columns. When you grant access to specific columns, queries can only read those columns. Any query that asks for other columns will fail right away. A wildcard query that asks for all columns also gets refused and returns nothing. It only works if the table holds just the allowed columns. In versions since 24.10, a trailing asterisk grants rights by table name prefix.

Why static credentials leave gaps in workflow security

Native database roles and column grants solve the first layer of access control. They stop Zapier workflows from running write commands or dropping tables. They also block queries from touching tables outside the allowed list.

Still, static database accounts still leave major security gaps in daily operations. You must store the ClickHouse password inside the third-party automation tool. Anyone on your team who can edit Zaps might misuse that shared connection. Every workflow uses the exact same database user name in your server logs. When a bad query slows down the cluster, you cannot see who built it.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Column grants also require constant manual updates as your database schema grows. If a permitted column holds emails, phone numbers, card numbers, or national IDs, raw values flow out. Native grants decide who logs in, not what each query returns in flight. A single allowed column can still expose thousands of sensitive customer records to outside apps.

Managing automated queries and sensitive data

Teams need a way to inspect every query before it hits the database. Instead of relying only on broad account grants, you can check statements in flight. This approach lets you enforce rules on specific queries from outside tools.

Data masking is also vital when workflows read production tables with personal details. Masking rewrites matched fields like emails or card numbers in the query results. Your automated workflows still run on schedule, while sensitive strings stay safe. Combining strict ClickHouse grants with query-level checks keeps your analytics data secure.

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 do wildcard select queries fail for our restricted Zapier user?

ClickHouse blocks wildcard queries when a user only has grants on specific columns. You must list the exact allowed column names in your Zapier query instead.

Can we restrict which IP addresses the Zapier user connects from?

Yes, ClickHouse lets you set allowed client hosts when you create the user. The database will reject login attempts from any host outside that list.

How do we grant read access to a group of similar tables?

Starting in ClickHouse 24.10, you can use a trailing asterisk on table names. This rule grants select rights to all tables that share that name prefix.

The query runs. The emails stay hidden.