How we give every user SQL access to a shared ClickHouse cluster
1–10 of 85 posts
Re: How we give every user SQL access to a shared ClickHouse cluster
#2Reasons 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.
Re: How we give every user SQL access to a shared ClickHouse cluster
#3We 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.
Re: How we give every user SQL access to a shared ClickHouse cluster
#4We 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
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?
Re: How we give every user SQL access to a shared ClickHouse cluster
#5We 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
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?
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.
Re: How we give every user SQL access to a shared ClickHouse cluster
#6Reasons 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.
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.
Re: How we give every user SQL access to a shared ClickHouse cluster
#7We 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.
Re: How we give every user SQL access to a shared ClickHouse cluster
#8We 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.
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
Re: How we give every user SQL access to a shared ClickHouse cluster
#9We 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.
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
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?
Re: How we give every user SQL access to a shared ClickHouse cluster
#10New to ClickHouse here. Would you thing this kind of database has a niche when compared to usual RDBMS like MySQL and PostgreSQL?