7 ms·

How we give every user SQL access to a shared ClickHouse cluster

70 points by eallam 6mo ago

senorrib 6mo ago
Reasons 1-3 could very well be done with ClickHouse policies (RLS) and good data warehouse design. In fact, that’s more secure than a compiler adding a where to a query ran by an all mighty user. Reason 4 is probably an improvement, but could probably be done with CH functions. The problem with custom DSLs like this is that tradeoff a massive ecosystem for very little benefit.
- efromvt 6mo ago
  As long as you don't deviate too much from ANSI, I think the 'light sql DSL' approach has a lot of pros when you control the UX. (so UIs, in particular, are fantastic for this approach - what they seem to be targeting with queryies and dashboards). It's more of a product experience; tables are a terrible product surface to manage. Agreed with the ecosystem cons getting much heavier as you move outside the product surface area.
  - skeeter2020 6mo ago
    Personally I think that's worse. SQL - which is almost ubiqutous - already suffers from a fragmentation problem because of the complex and dated standardization setup. When I learn a new DBMS the two questions I ask at the very start are: 1. what common but non-standard features are supported? 2. what new anchor-features (often cool but also often intended to lock me to the vendor) am I going to pick up? First I need to learn a new (even easy & familiar) language, second I need to be aware of what's proprietary & locks me to the vendor platform. I'd suspect they see the second as a benefit they get IF they can convince people to accept the first.
    - efromvt 6mo ago
      I actually 100% agree with your for a new DBMS and share your frustration with vendor-specific features and lock-in. At that level, it's often actively counterproductive for insurgent DBs - ecosystem tooling needs more work to interface with your shiny new DB, etc - and that's why we always see anyone who starts with a non-standard SQL converge on offering ANSI SQL eventually. I think an application that exposes a curated dataset through a SQL-like interface - so the dashboard/analytic query case described here - is where I think this approach has value. You actually don't want to expose raw tables, INFORMATION_SCHEMA, etc - you're offering a dedicated query language on top of a higher level data product offering, and you might as well take the best of SQL and leave the bits you don't need. (You're not offering a database as a service; you're offering data as a service).
- mattaitken 6mo ago
  You’re right RLS can go a long way here. With complex RBAC rules it can get tricky though. The main advantages of a DSL are you can expose a nicer interface to users (table names, columns, virtual columns, automatic joins, query optimization). We very intentionally kept the syntax as close to regular ClickHouse as possible but added some functions.
  - theowaway213456 6mo ago
    > table names, columns, virtual columns This sounds solvable with clickhouse views? > automatic joins Is this also not solvable with views? Also, clickhouse heavily discourages joins so I wonder how often this winds up being beneficial? For us, we only ever join against tenant metadata (i.e. resolving ID to name) > query optimization This sounds potentially interesting - clickhouse's query optimizer is not great IME, but it's definitely getting better
- hrmtst93837 6mo ago
  [flagged]
zie 6mo ago
We do the same thing, every employee can access our main financial/back office SQL database, but we just use PostgreSQL with row level security[0]. We never bothered to complicate it like the post does. 0: https://www.postgresql.org/docs/18/ddl-rowsecurity.html https://www.postgresql.org/docs/18/ddl-rowsecurity.html
- orf 6mo ago
  Back office, employee access is a completely different problem to what is described in the post. How do you enforce tenant isolation with that method, or prevent unbounded table reads?
  - tossandthrow 6mo ago
    They likely don't need tenant isolation and unbound table reads can be mitigated using timeouts. We do something similar for our backoffice - just with the difference that it is Claude that has full freedom to write queries.
    - paulryanrogers 6mo ago
      Can Claude drop tables?
  - weird-eye-issue 6mo ago
    RLS...
- staticassertion 6mo ago
  I'd be so uncomfortable with this. It sounds like you're placing the full burden of access on a single boundary. I mean, maybe there's more to it that you haven't spoken about here, but "everything rests on this one postgres feature" is an unacceptably unsafe state to me.
  - weird-eye-issue 6mo ago
    It's not like RLS is just some random feature they are misusing. It's specifically for security and is absolutely reliable. Maybe you should do a bit more research before making comments like this.
jelder 6mo ago
We did this with MotherDuck, and without introducing a new language. Every tenant has their own isolated storage and compute, so it’s trivial to grant internal users access to specific tenants as needed. DuckDB’s SQL dialect is mostly just Postgres’ with some nice ergonomic additions and a host of extra functionality.
- raw_anon_1111 6mo ago
  This is explicitly not the problem they are trying to solve. In a single tenant database you don’t have to by definition worry about multi tenant databases
  - DangitBobby 6mo ago
    I guess the question then becomes, what problem does a multi-tenancy setup solve that an isolated database setup doesn't? Are they really not solving the same problem for a user perspective, or is it only from their own engineering perspective? And how do those decisions ultimately impact the product they can surface to users?
    - raw_anon_1111 6mo ago
      Off the top of my head, managing 100 different database instances takes a lot more work from the business standpoint than managing 1 database with 100 users. The article also mentioned that they isolate by project_id. That implies one customer (assume a business) can isolate permissions more granulary.
      - mattaitken 6mo ago
        Yes it’s exactly this. There’s not a neat permission boundary when you have users, orgs, projects, environments. Let alone when you add RBAC too.
    - steveBK123 6mo ago
      With multi-tenant vs multi-database decision one driver would be the level of legal/compliance/risk/cost/resource drivers around how segregated users really are. Multi-database is more expensive generally but is a more brain dead guaranteed way to ensure the users are properly segregated, resilient across cloud/database/etc software releases that may regress something in a multi-tenant setup. Multi-tenant you always run the risk of a software update, misconfiguration or operational error exposing existence of other users / their metadata / their data / their usage / etc. You also have a lot more of a challenge engineering for resource contention.
elnatro 6mo ago
New to ClickHouse here. Would you thing this kind of database has a niche when compared to usual RDBMS like MySQL and PostgreSQL?
- mattaitken 6mo ago
  ClickHouse is a high performance OLAP database. It’s good for analytics and search. We use it (I’m the author or the article) so users can search every run they do and graph all sorts of metrics.
baalimago 6mo ago
The evolution of this is to use agents, and have users "chat with the data"
- mattaitken 6mo ago
  Yes, you can actually do this already because we expose a REST API and TypeScript SDK functions to execute the queries.
cjonas 6mo ago
We just create mini data "ponds" on the fly by copying tenant isolated gold tier data to parquet in s3. The users/agent queries are executed with duckdb. We run this process when the user start a session and generate an STS token scoped to their tenant bucket path. Its extremely simple and works well (at least with our data volumes).
- Waterluvian 6mo ago
  Is that why it’s called DuckDb? Because data ponds?
  - cjonas 6mo ago
    Idk but I named everything in the related code "duckpond" :)
  - mritchie712 6mo ago
    Hannes (one of the creators) had a pet duck
  - QuantumNomad_ 6mo ago
    The DuckDB website has the following to say about the name: > Why call it DuckDB? > Ducks are amazing animals. They can fly, walk and swim. They can also live off pretty much everything. They are quite resilient to environmental challenges. A duck's song will bring people back from the dead and inspires database research. They are thus the perfect mascot for a versatile and resilient data management system. https://duckdb.org/faq#why-call-it-duckdb https://duckdb.org/faq#why-call-it-duckdb
- boundlessdreamz 6mo ago
  How do you copy all the relevant data? Doesn't this create unnecessary load on your source DB?
  - cjonas 6mo ago
    We have various data sources (which is another benefit of this approach). Data from the application DB is currently pulled using the FE apis which handle tenant isolation and allow the application database to deal with the load. I think pg_duckdb could be a good solution here as well, but haven't gotten around to testing it. Other data come from analytics DB. Most of this is landed on an interval via pipeline scripts.
devnotes77 6mo ago
[dead]
bob1029 6mo ago
> How do you let users write arbitrary SQL against a shared multi-tenant analytical database without exposing other tenants' data or letting a rogue query take down the cluster? For query operations I would try to find a way to solve this with tools like S3 and SQLite. There are a few VFS implementations for S3 and other CDNs.
- nlittlepoole 6mo ago
  Open Table Formats (Iceberg, Delta Lake, Hudi, etc) are the approach we've taken. That let's us offer a query engine but also let's the tenant bring their preferred engine (Snowflake, Spark, DuckDB, etc). It also addresses dirty reads and some other state problems that come from trying to use the file system. It scales as much as the bucket does, so we haven't found a use case we couldn't scale to yet. We (https://prequel.co https://prequel.co) recently started offering this as a white labeled capability so anyone can offer it without building it yourself. Its a newer capability to our export product where instead of sending the data to the tenant's data warehouse, we enable you to provision an S3/GCS/ABS/etc bucket with the data formatted. Credential management, analytics, etc is all batteries included so you don't have to do that either. The initial interest from our customers was around BI integrations but agent use is starting to pick up which is kinda interesting to see.
  - r1290 6mo ago
    How does it handle large tables like. 2b rows? And how does it stay updated?
    - nlittlepoole 6mo ago
      what are the properties of the table? is the whole table 2B rows or is that the amount of row updates/inserts that happen per period (day, week, month)? We do handle a lot of large table volumes on both our products already, but there will be cost tradeoffs the larger the data volume and lower the latency you try to target.
xihe-forge 6mo ago
[dead]
datatrashfire 6mo ago
as a clickhouse architect, i can’t help but feel they would have been better served by understanding their db’s native capabilities better before jumping into implementing this. row level access control, resource quotas, scheduling policies, session settings, etc. all could have been used in concert to achieve a very similar outcome with a dozen or so ddl/dcl statements.
- theowaway213456 6mo ago
  This is my impression as well. ClickHouse has tons of useful features built in that seem like they'd work well here. Though the documentation about those features has been very scattered and hard to find in my experience
rishabhjajoriya 6mo ago
Curious howw you handle a user who accidentally writes a query that does a full table scan across billions of rows does the quota kick in fast enough or have you had cases where it still causes noticeable impact on other tenants?
gerardnico 6mo ago
For those who don’t know, that’s called a semantic layer in business intelligence https://www.datacadamia.com/data/type/cube/semantic/semantic https://www.datacadamia.com/data/type/cube/semantic/semantic It’s a sql that compiles to the real database sql based on configuration.
mergisi 6mo ago
[dead]

70 points by eallam 6mo ago

Reasons 1-3 could very well be done with ClickHouse policies (RLS) and good data warehouse design. In fact, that’s more secure than a compiler adding a where to a query ran by an all mighty user. Reason 4 is probably an improvement, but could probably be done with CH functions. The problem with custom DSLs like this is that tradeoff a massive ecosystem for very little benefit.

efromvt 6mo ago
As long as you don't deviate too much from ANSI, I think the 'light sql DSL' approach has a lot of pros when you control the UX. (so UIs, in particular, are fantastic for this approach - what they seem to be targeting with queryies and dashboards). It's more of a product experience; tables are a terrible product surface to manage. Agreed with the ecosystem cons getting much heavier as you move outside the product surface area.
- skeeter2020 6mo ago
  Personally I think that's worse. SQL - which is almost ubiqutous - already suffers from a fragmentation problem because of the complex and dated standardization setup. When I learn a new DBMS the two questions I ask at the very start are: 1. what common but non-standard features are supported? 2. what new anchor-features (often cool but also often intended to lock me to the vendor) am I going to pick up? First I need to learn a new (even easy & familiar) language, second I need to be aware of what's proprietary & locks me to the vendor platform. I'd suspect they see the second as a benefit they get IF they can convince people to accept the first.
  - efromvt 6mo ago
    I actually 100% agree with your for a new DBMS and share your frustration with vendor-specific features and lock-in. At that level, it's often actively counterproductive for insurgent DBs - ecosystem tooling needs more work to interface with your shiny new DB, etc - and that's why we always see anyone who starts with a non-standard SQL converge on offering ANSI SQL eventually. I think an application that exposes a curated dataset through a SQL-like interface - so the dashboard/analytic query case described here - is where I think this approach has value. You actually don't want to expose raw tables, INFORMATION_SCHEMA, etc - you're offering a dedicated query language on top of a higher level data product offering, and you might as well take the best of SQL and leave the bits you don't need. (You're not offering a database as a service; you're offering data as a service).
mattaitken 6mo ago
You’re right RLS can go a long way here. With complex RBAC rules it can get tricky though. The main advantages of a DSL are you can expose a nicer interface to users (table names, columns, virtual columns, automatic joins, query optimization). We very intentionally kept the syntax as close to regular ClickHouse as possible but added some functions.
- theowaway213456 6mo ago
  > table names, columns, virtual columns This sounds solvable with clickhouse views? > automatic joins Is this also not solvable with views? Also, clickhouse heavily discourages joins so I wonder how often this winds up being beneficial? For us, we only ever join against tenant metadata (i.e. resolving ID to name) > query optimization This sounds potentially interesting - clickhouse's query optimizer is not great IME, but it's definitely getting better
hrmtst93837 6mo ago
[flagged]

zie 6mo ago

We do the same thing, every employee can access our main financial/back office SQL database, but we just use PostgreSQL with row level security[0]. We never bothered to complicate it like the post does. 0: https://www.postgresql.org/docs/18/ddl-rowsecurity.html https://www.postgresql.org/docs/18/ddl-rowsecurity.html

orf 6mo ago
Back office, employee access is a completely different problem to what is described in the post. How do you enforce tenant isolation with that method, or prevent unbounded table reads?
- tossandthrow 6mo ago
  They likely don't need tenant isolation and unbound table reads can be mitigated using timeouts. We do something similar for our backoffice - just with the difference that it is Claude that has full freedom to write queries.
  - paulryanrogers 6mo ago
    Can Claude drop tables?
- weird-eye-issue 6mo ago
  RLS...
staticassertion 6mo ago
I'd be so uncomfortable with this. It sounds like you're placing the full burden of access on a single boundary. I mean, maybe there's more to it that you haven't spoken about here, but "everything rests on this one postgres feature" is an unacceptably unsafe state to me.
- weird-eye-issue 6mo ago
  It's not like RLS is just some random feature they are misusing. It's specifically for security and is absolutely reliable. Maybe you should do a bit more research before making comments like this.

jelder 6mo ago

We did this with MotherDuck, and without introducing a new language. Every tenant has their own isolated storage and compute, so it’s trivial to grant internal users access to specific tenants as needed. DuckDB’s SQL dialect is mostly just Postgres’ with some nice ergonomic additions and a host of extra functionality.

raw_anon_1111 6mo ago
This is explicitly not the problem they are trying to solve. In a single tenant database you don’t have to by definition worry about multi tenant databases
- DangitBobby 6mo ago
  I guess the question then becomes, what problem does a multi-tenancy setup solve that an isolated database setup doesn't? Are they really not solving the same problem for a user perspective, or is it only from their own engineering perspective? And how do those decisions ultimately impact the product they can surface to users?
  - raw_anon_1111 6mo ago
    Off the top of my head, managing 100 different database instances takes a lot more work from the business standpoint than managing 1 database with 100 users. The article also mentioned that they isolate by project_id. That implies one customer (assume a business) can isolate permissions more granulary.
    - mattaitken 6mo ago
      Yes it’s exactly this. There’s not a neat permission boundary when you have users, orgs, projects, environments. Let alone when you add RBAC too.
  - steveBK123 6mo ago
    With multi-tenant vs multi-database decision one driver would be the level of legal/compliance/risk/cost/resource drivers around how segregated users really are. Multi-database is more expensive generally but is a more brain dead guaranteed way to ensure the users are properly segregated, resilient across cloud/database/etc software releases that may regress something in a multi-tenant setup. Multi-tenant you always run the risk of a software update, misconfiguration or operational error exposing existence of other users / their metadata / their data / their usage / etc. You also have a lot more of a challenge engineering for resource contention.

elnatro 6mo ago

New to ClickHouse here. Would you thing this kind of database has a niche when compared to usual RDBMS like MySQL and PostgreSQL?

mattaitken 6mo ago
ClickHouse is a high performance OLAP database. It’s good for analytics and search. We use it (I’m the author or the article) so users can search every run they do and graph all sorts of metrics.

baalimago 6mo ago

The evolution of this is to use agents, and have users "chat with the data"

mattaitken 6mo ago
Yes, you can actually do this already because we expose a REST API and TypeScript SDK functions to execute the queries.

cjonas 6mo ago

We just create mini data "ponds" on the fly by copying tenant isolated gold tier data to parquet in s3. The users/agent queries are executed with duckdb. We run this process when the user start a session and generate an STS token scoped to their tenant bucket path. Its extremely simple and works well (at least with our data volumes).

Waterluvian 6mo ago
Is that why it’s called DuckDb? Because data ponds?
- cjonas 6mo ago
  Idk but I named everything in the related code "duckpond" :)
- mritchie712 6mo ago
  Hannes (one of the creators) had a pet duck
- QuantumNomad_ 6mo ago
  The DuckDB website has the following to say about the name: > Why call it DuckDB? > Ducks are amazing animals. They can fly, walk and swim. They can also live off pretty much everything. They are quite resilient to environmental challenges. A duck's song will bring people back from the dead and inspires database research. They are thus the perfect mascot for a versatile and resilient data management system. https://duckdb.org/faq#why-call-it-duckdb https://duckdb.org/faq#why-call-it-duckdb
boundlessdreamz 6mo ago
How do you copy all the relevant data? Doesn't this create unnecessary load on your source DB?
- cjonas 6mo ago
  We have various data sources (which is another benefit of this approach). Data from the application DB is currently pulled using the FE apis which handle tenant isolation and allow the application database to deal with the load. I think pg_duckdb could be a good solution here as well, but haven't gotten around to testing it. Other data come from analytics DB. Most of this is landed on an interval via pipeline scripts.

devnotes77 6mo ago

[dead]

bob1029 6mo ago

> How do you let users write arbitrary SQL against a shared multi-tenant analytical database without exposing other tenants' data or letting a rogue query take down the cluster? For query operations I would try to find a way to solve this with tools like S3 and SQLite. There are a few VFS implementations for S3 and other CDNs.

nlittlepoole 6mo ago
Open Table Formats (Iceberg, Delta Lake, Hudi, etc) are the approach we've taken. That let's us offer a query engine but also let's the tenant bring their preferred engine (Snowflake, Spark, DuckDB, etc). It also addresses dirty reads and some other state problems that come from trying to use the file system. It scales as much as the bucket does, so we haven't found a use case we couldn't scale to yet. We (https://prequel.co https://prequel.co) recently started offering this as a white labeled capability so anyone can offer it without building it yourself. Its a newer capability to our export product where instead of sending the data to the tenant's data warehouse, we enable you to provision an S3/GCS/ABS/etc bucket with the data formatted. Credential management, analytics, etc is all batteries included so you don't have to do that either. The initial interest from our customers was around BI integrations but agent use is starting to pick up which is kinda interesting to see.
- r1290 6mo ago
  How does it handle large tables like. 2b rows? And how does it stay updated?
  - nlittlepoole 6mo ago
    what are the properties of the table? is the whole table 2B rows or is that the amount of row updates/inserts that happen per period (day, week, month)? We do handle a lot of large table volumes on both our products already, but there will be cost tradeoffs the larger the data volume and lower the latency you try to target.

xihe-forge 6mo ago

[dead]

datatrashfire 6mo ago

as a clickhouse architect, i can’t help but feel they would have been better served by understanding their db’s native capabilities better before jumping into implementing this. row level access control, resource quotas, scheduling policies, session settings, etc. all could have been used in concert to achieve a very similar outcome with a dozen or so ddl/dcl statements.

theowaway213456 6mo ago
This is my impression as well. ClickHouse has tons of useful features built in that seem like they'd work well here. Though the documentation about those features has been very scattered and hard to find in my experience

rishabhjajoriya 6mo ago

Curious howw you handle a user who accidentally writes a query that does a full table scan across billions of rows does the quota kick in fast enough or have you had cases where it still causes noticeable impact on other tenants?

gerardnico 6mo ago

For those who don’t know, that’s called a semantic layer in business intelligence https://www.datacadamia.com/data/type/cube/semantic/semantic https://www.datacadamia.com/data/type/cube/semantic/semantic It’s a sql that compiles to the real database sql based on configuration.

mergisi 6mo ago

[dead]