Helicone allows users to write SQL directly to a shared ClickHouse

Allowing your users to query ClickHouse directly using raw SQL | Justin Torre We added a SQL editor to Helicone called HQL. Customers get a text box, they write SELECT statements, and the queries run directly against our ClickHouse cluster. Every org's request data lives in the same table, request_response_rmt, separated only by an organization_id column. Letting strangers run arbitrary SQL against a shared table sounds like a security incident with extra steps, so before shipping we needed the database itself to guarantee that a query from org A can never read a row belonging to org B. ClickHouse can do this with two features that don't get much attention: row policies and custom settings. Here's how we wired them together, and what the app-side code (including an AST parser) still has to do. HQL in production. Every query on this screen runs against the same shared table.The row policy A row policy in ClickHouse is a filter the server appends to every read, per table, per user. The setup is two statements, straight from our migrations: create a unique user that will have the row policy, then attach the policy to that user. CREATE USER IF NOT EXISTS hql_user;

CREATE ROW POLICY hql_organization_filter ON request_response_rmt FOR SELECT USING organization_id = getSetting('SQL_helicone_organization_id') TO hql_user; The unique user is the whole point of the first statement. hql_user exists purely to serve HQL traffic, and the policy binds to it through the TO hql_user clause. Our ingest and dashboard code connects as a different user and never sees the policy. For hql_user, every SELECT on request_response_rmt behaves as if WHERE organization_id = getSetting('SQL_helicone_organization_id') were part of the query, no matter what SQL the customer actually wrote. getSetting is the interesting part. It reads a setting from the context of the current query. ClickHouse rejects setting names it doesn't recognize, and custom ones are only legal when they start with a declared prefix. If you self-host, you pick that prefix in the server config: clickhouse> custom_settings_prefixes>SQL_custom_settings_prefixes> clickhouse> On ClickHouse Cloud, where our cluster runs, there is no picking. You can't touch server config, and the ClickHouse docs state that "it is not possible to specify a custom prefix". Every custom setting begins with SQL_, no exceptions. So ours is called SQL_helicone_organization_id instead of something cleaner, and that awkward prefix is load-bearing rather than a naming choice. You'll notice there's no custom_settings_prefixes entry anywhere in our repo; on Cloud there's nothing to configure. The prefix has a history, too. MySQL clients set session variables like SQL_AUTO_IS_NULL when they connect, and ClickHouse accepts those as no-ops (ClickHouse PR #50013) so MySQL-speaking tools like Tableau don't fall over when they connect. My guess is that's why Cloud standardized on SQL_ as the one allowed prefix, which means our tenancy filter rides on a naming convention built for MySQL drivers. Where the query parameters come in Settings in ClickHouse can be set per query. Over the HTTP interface they travel as URL query parameters, so every HQL request carries the tenant id in the query string: ?SQL_helicone_organization_id=. The official Node client hides that behind clickhouse_settings: const result = await clickHouseHqlClient.query({ query: userSql, format: "JSONEachRow", clickhouse_settings: { SQL_helicone_organization_id: organizationId, readonly: "1", allow_ddl: 0, max_execution_time: 30, max_memory_usage: "4000000000", max_result_rows: "10000", }, }); The organizationId comes from our auth layer, never from anything the customer typed. The other settings exist because customers share the cluster with our ingest pipeline and with each other: a 30 second timeout, a memory cap, a cap on result rows. readonly deserves its own paragraph. A ClickHouse SELECT can end with a SETTINGS clause, which means a customer could try SELECT * FROM request_response_rmt SETTINGS SQL_helicone_organization_id = 'victim-org' and overwrite the value we passed. With readonly = 1 the server rejects any settings change at query time, which closes that hole. On top of that, the app refuses any query that so much as mentions the setting name: const forbiddenPattern = /sql[_\s]*helicone[_\s]*organization[_\s]*id/i; if (forbiddenPattern.test(query)) { return err("Query contains a reserved setting name"); ClickHouse enforces the actual block. The regex means an attempt shows up in our logs as a rejected query rather than a database error. Revoking everything else A row policy on one table does nothing for the rest of the database, and ClickHouse ships with very readable system tables. system.query_log alone contains every query every tenant has ever run, SQL text included. So hql_user gets stripped down to exactly one grant: REVOKE ALL ON system.* FROM hql_user; REVOKE ALL ON information_schema.* FROM hql_user; REVOKE ALL ON...

Helicone allows users to write SQL directly to a shared ClickHouse

Related Articles

(no title)

Scientists reverse brain aging, with a nasal spray

AI has torched the market for junior programmers

Al Vigier: Canada's AI strategy shouldn't include secret Palantir bills

The Anatomy of an AI-Native Org