How to Configure CrewAI ClickHouse Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a crewai clickhouse audit log by giving your agent a scoped user. Then you record every query inside ClickHouse system log tables. Many teams start by giving a CrewAI agent a single shared database login. If that role has broad permissions, one bad agent prompt can read or change sensitive tables. How a crewai clickhouse audit log works in practice CrewAI agents run tasks by calling tools that talk to external data stores. When an agent queries ClickHouse, it logs in as a specific

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 a crewai clickhouse audit log by giving your agent a scoped user. Then you record every query inside ClickHouse system log tables. Many teams start by giving a CrewAI agent a single shared database login. If that role has broad permissions, one bad agent prompt can read or change sensitive tables.

How a crewai clickhouse audit log works in practice

CrewAI agents run tasks by calling tools that talk to external data stores. When an agent queries ClickHouse, it logs in as a specific database user. ClickHouse manages users, roles, row policies, settings profiles, and quotas as SQL access entities. You need a storage directory and an admin account to turn on SQL access control. A user can only grant privileges that they already hold themselves. Creating a new user for your agent requires the create user privilege. That statement sets the login method, allowed client hosts, default roles, and user settings.

Good setup starts with giving the CrewAI user only the columns it needs. ClickHouse lets admins grant read rights on specific columns in a table. If an agent only gets permission on two columns, it can query those two columns. Queries for other columns fail right away. A full table query with an asterisk also fails and returns no data at all. It only works if the table holds just the allowed columns. Admins can also grant access across tables that share a name prefix. As the agent runs queries, ClickHouse writes activity records to its internal query log tables. Teams read those log tables to see which user ran each statement and when.

Why native roles and logs leave a runtime gap

Native roles and system logs solve basic login and history tracking for your database. A scoped ClickHouse user stops an agent from touching tables outside its grant list. The database log also shows when that agent account ran a statement. Still, static grants only check who logs in at the start of a session. They do not inspect what individual SQL statements do in flight.

Every CrewAI task in one app usually shares the same database user and credentials. In the ClickHouse log, all queries look like they came from one generic account. You cannot tell which specific task, user request, or agent step triggered a query. Also, column grants are all or nothing. If an agent needs a table with emails or card numbers, native grants allow raw values. The database does not rewrite sensitive fields in query results before the agent reads them. Static credentials also sit in your agent host files until someone rotates them by hand.

Audit Log Integrity + ClickHouse Access Management: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Scoping agent permissions before queries run

To reduce risk, teams separate agent tasks into distinct database roles with strict limits. Read tasks get a role that can only select from approved reporting tables. Write tasks get a separate role that only inserts into specific output tables. Teams also set query quotas and row policies on those ClickHouse roles. Row policies filter which rows the agent user can read based on column values. Quotas cap how many queries or rows the agent can process in a time window. Even with these rules, database logs still only show the shared database role name. Linking each query back to a human user or agent run takes extra runtime controls.

Check ClickHouse queries and result columns on the wire

ClickHouse users and row policies decide who connects. They do not check each statement on the wire or redact sensitive string columns in query results. With hoop.dev, you run an open-source proxy, a sidecar for your ClickHouse. It reads ClickHouse's native TCP protocol before queries run while ClickHouse stays in charge of login and grants.

Multi-statement queries and table mutations are split and checked by hoop.dev guardrails. Banned actions are refused with a native ClickHouse error. On returned rows, hoop.dev data masking rewrites String and FixedString columns that match your rules. The audit trail logs each query, table, and verdict. See the MIT-licensed source on GitHub.

Frequently asked questions

How do I restrict which columns a CrewAI agent can read in ClickHouse?

You grant read permissions on named columns instead of the whole table. ClickHouse will block queries on other columns and reject wildcard queries on that table.

Why do all CrewAI queries look the same in native ClickHouse logs?

A CrewAI app usually connects with one shared database user and password. ClickHouse logs that single database user name for every query the crew runs.

Can ClickHouse column grants hide sensitive fields like phone numbers in results?

Column grants block an entire column if the user lacks permission to read it. To return a column with masked phone numbers or emails, you need dynamic data masking.

The query runs. The emails stay hidden.