Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

trigger.dev

Post reply on HN
Print view View original

31–40 of 85 posts

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

#31

Post by cjonas »

boundlessdreamz wrote: post #18
cjonas wrote: post #12

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).

How do you copy all the relevant data? Doesn't this create unnecessary load on your source DB?

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.

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

#32

Post by mattaitken »

raw_anon_1111 wrote: post #8
jelder wrote: post #7

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.

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

In a system with organizations, projects and advanced user access permissions having separate databases doesn’t full solve the problem. You still need access control inside each tenanted database. It also makes cross-cutting queries impossible which means users can’t query across all their orgs for example.

The DSL approach has other advantages too: like rewriting queries to not expose underlying tables, doing automatic performance optimizations…

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

#33

Post by staticassertion »

weird-eye-issue wrote: post #29
staticassertion wrote: post #21

Earlier quoted context omitted.

Of course it's designed for security... that has nothing to do with my statement. No single boundary is "absolutely reliable", that's my entire point.

This is the real world not everybody can get a perfectly isolated database instance. Also you do realize that is not necessarily any more secure than RLS right? Something still has to control what database the code connects to. That could have a flaw just as much as you could have a flaw when setting up RLS.

I don't think you have any idea what you're talking about, certainly you don't know what I'm talking about.

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

#34

Post by skeeter2020 »

staticassertion wrote: post #21
weird-eye-issue wrote: post #19

Earlier quoted context omitted.

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.

Of course it's designed for security... that has nothing to do with my statement. No single boundary is "absolutely reliable", that's my entire point.

You can (and in some cases should) combine this with other boundaries, like access control or specific query logic. RLS moves the core checks closer to the data being controlled (i.e. the database) specifically to prevent the errors like forgetting to add the "where user_id = xxx" clause. It is super-valuable in compliance scenarios where someone like your DB Admin has permission to control access but not see any data, and consumers (both devs and end users) can write queries to see different levels of access but are not allowed to control data.

Obviously it's not a silver bullet and the isolation can be confusing when debugging, but generally a single point for your applying RBAC is a feature not a shortcoming. The next level of security might be how you define your roles.

I actually believe the simplest, most secure client scenario is physical isolation, where you give the user/consumer only the data they are allowed to use and then don't try to control it (someone mentioned this above, using parquet & duckdb). There's downsides here too: doesn't work for write scenarios, can be resource intensive or time delayed, doesn't handle chain of custody well, etc. You typically have two strategies:

1. pick the best approach for the specific situation.

2. pick your one tool as your hammer and be a d!ck about it.

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

#35

Post by staticassertion »

Philip-J-Fry wrote: post #30
staticassertion wrote: post #26

Earlier quoted context omitted.

> At the end of the day with all data that is colocated you're trusting that some permission feature somewhere is preventing you from accessing data you're not supposed to. Right but ideally more than one. > But it's all just mostly logical separation. Yes, ideally multiple layers of this. You don't all share one RDS instance and then get row level security.

Can you give an example of more than one layer of logical separation at the data layer? We all know that authentication should have multiple factors. But that's a different problem. Fundamentally at the point you're reading or writing data you're asking the question "does X has permission to read/write Y". I don't see what you're getting at.

I don't know their use case enough to understand what would or would not be an appropriate mitigation. For example, with regards to financial data, you could have client side encryption on values where those keys are brokered separately. I can't exactly design their system for them, but they're describing a system in which every employee has direct database access and the database holds financial information.

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

#37

Post by mattaitken »

senorrib wrote: post #2

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.

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.

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

#38

Post by mattaitken »

raw_anon_1111 wrote: post #15
DangitBobby wrote: post #9

Earlier quoted context omitted.

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?

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.

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.

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

#39

Post by mattaitken »

elnatro wrote: post #10

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

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.

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

#40

Post by skeeter2020 »

efromvt wrote: post #6
senorrib wrote: post #2

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.

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 sur…

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.

Post reply on HN