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

71–80 of 85 posts

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

#71

Post by zie »

steveBK123 wrote: post #20
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?

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…

Agreed, in Multi-tenant, where the user/customer owns the data, I always reach for SQLite first. Each user/customer gets their own SQLite DB. Then you have a common PG/SQLite DB for any common metadata, billing, etc.

That way when a customer leaves or they want a backup copy of their data, it's a rm .sqlite3 or .backup away.

Sometimes you can't do that for various (almost always non-technical) reason(s), but it's always my 1st choice.

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

#72

Post by nlittlepoole »

r1290 wrote: post #60
nlittlepoole wrote: post #48

Earlier quoted context omitted.

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://p…

How does it handle large tables like. 2b rows? And how does it stay updated?

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.

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

#73

Post by staticassertion »

zie wrote: post #68
staticassertion wrote: post #58

Earlier quoted context omitted.

> Your SSO system is a lot of pressure on one control too. Nobody seems to have problems with Azure or Okta or whatever SSO system you use having every key to the kingdom. Some do, which is why they want MFA on the target side as well as on their SSO. But yes, SSO is very scary and there's a ton of security pressure on it. I don't think that's a very good argument for why we should think that every system should only…

> It doesn't really matter. The fact is that you're one CVE away from every employee having access to arbitrary data, including financial data. I feel a bit like a broken record saying this. Sure, but it's the same with pretty much any other app architecture. Either your app has all the data access and you put your access control there, or you do the access control in the database. There really aren't other options h…

> Either your app has all the data access and you put your access control there, or you do the access control in the database. There really aren't other options here.

That's odd, I just clearly delineated an option in which this is not the case. The DB enforces RLS, users provide the RLS token, and an app gates access to the db.

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

#74

Post by staticassertion »

zie wrote: post #69
staticassertion wrote: post #47

Earlier quoted context omitted.

Well, I think we basically agree? My suggestion is merely that a database holding financial data should have more than a single layer of security. Granting direct access to a database is a pretty scary thing. A simple example would be that any vulnerability in the database is directly accessible, even just by placing a broker in between users and the database I'd likely start to feel a lot better, and now I'd have a…

> My suggestion is merely that a database holding financial data should have more than a single layer of security. We require SSO(Azure via vault) to authenticate to the DB. We also don't expose PostgreSQL to the public internet. We aren't complete monsters :) > Granting direct access to a database is a pretty scary thing. For you maybe, because you were taught it's scary or it just seems different? I dunno. I'm very…

I'm not sure where this "it's always one layer" thing is coming from, that's just not true. Nor do I see where I've said you should toss out RLS for a bespoke system - I see myself saying the opposite a few times.

> For you maybe, because you were taught it's scary or it just seems different?

Over a decade in computer security and software engineering. Nothing I'm saying is contentious. For some reason when I say "Having one boundary is bad" you say "There's only ever one boundary", which... is not true.

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

#75

Post by staticassertion »

zie wrote: post #70
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.

Correct, but you have yet to mention any other solution where there is more than one boundary at the data access layer. Because there really isn't any. You can code it yourself in your bespoke app, have your vendor maintain it with their bespoke access control, or let RLS do it. There aren't really any other options that I'm aware of. Personally, having done the "code it yourself in your bespoke app" it's a PITA and…

> Correct, but you have yet to mention any other solution where there is more than one boundary at the data access layer. Because there really isn't any.

I have. I gave the example of using RLS where users still provide the token to gain RLS privileges but an app brokers and constraints the connections. I have also given the example of encryption, to which your response is that encryption is hard, which I don't think is true but doesn't really change anything. Encryption is absolutely a data access layer control.

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

#76

Post by zie »

staticassertion wrote: post #73
zie wrote: post #68

Earlier quoted context omitted.

> It doesn't really matter. The fact is that you're one CVE away from every employee having access to arbitrary data, including financial data. I feel a bit like a broken record saying this. Sure, but it's the same with pretty much any other app architecture. Either your app has all the data access and you put your access control there, or you do the access control in the database. There really aren't other options h…

> Either your app has all the data access and you put your access control there, or you do the access control in the database. There really aren't other options here. That's odd, I just clearly delineated an option in which this is not the case. The DB enforces RLS, users provide the RLS token, and an app gates access to the db.

I must have missed that in the comment stream.

It's not really any different than using pgbouncer or something similar. All it buys you is not having to use the PG protocol on the client.

There is no extra security here though. One could even argue you lose security here, since now you have to rely on the app to not get tokens confused, since they hold and use the tokens on behalf of the user. A single bad CVE in the app and one can become any user the app currently knows about.

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

#77

Post by staticassertion »

zie wrote: post #76
staticassertion wrote: post #73

Earlier quoted context omitted.

> Either your app has all the data access and you put your access control there, or you do the access control in the database. There really aren't other options here. That's odd, I just clearly delineated an option in which this is not the case. The DB enforces RLS, users provide the RLS token, and an app gates access to the db.

I must have missed that in the comment stream. It's not really any different than using pgbouncer or something similar. All it buys you is not having to use the PG protocol on the client. There is no extra security here though. One could even argue you lose security here, since now you have to rely on the app to not get tokens confused, since they hold and use the tokens on behalf of the user. A single bad CVE in the…

I think the conversation is suffering from trying to solve a problem that's underspecified, which is going to just lead to a lot of "what if" questions. I've explained my concern with relying on a single boundary, which should not be contentious and I have given multiple examples of potential additional controls.

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

#78

Post by leetrout »

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

I built something on top of DuckDB last year but it never got deployed. They wanted to trust Postgres.

I didn't use the in browser WASM but I did expose an api endpoint that passed data exploration queries directly to the backend like a knock off of what new relic does. I also use that same endpoint for all the graphs and metrics in the UI. Just filtered out the write / delete statements in a rudimentary way.

DuckDB is phenomenal tech and I love to use it with data ponds instead of data lakes although it is very capable of large sets as well.

And "data pond"? Glad I am not alone using this term! Somewhere between a data lake and warehouse - still unstructured but not _everything_ in one place. For instance, if I have a multi-tenant app I might choose to have a duckdb setup for each customer with pre-filtered data living alongside some global unstructured data.

Maybe there's already a term that covers this but I like the imagery of the metaphor... "smaller, multiple data but same idea as the big one".

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

#79

Post by paulryanrogers »

tossandthrow wrote: post #5
orf wrote: post #4

Earlier quoted context omitted.

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.

Can Claude drop tables?

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

#80

Post by array_key_first »

staticassertion wrote: post #58
zie wrote: post #55

Earlier quoted context omitted.

Your SSO system is a lot of pressure on one control too. Nobody seems to have problems with Azure or Okta or whatever SSO system you use having every key to the kingdom. RLS has been around a long time and is very stable and doesn't change much. SSO providers keep adding stuff ALL the time, and they regularly have issues. PG RLS is very boring in comparison. I don't remember the last CVE or outage we had with PG that…

> Your SSO system is a lot of pressure on one control too. Nobody seems to have problems with Azure or Okta or whatever SSO system you use having every key to the kingdom. Some do, which is why they want MFA on the target side as well as on their SSO. But yes, SSO is very scary and there's a ton of security pressure on it. I don't think that's a very good argument for why we should think that every system should only…

Row level security is absolutely fine.

Most companies, or at least the ones I've worked at, don't use row level security at all. Instead, the application just accesses the multi tenant database.

It's absolutely littered with broken access control vulnerabilities. You have to remember to put the user key and group in every query across the entire application. And then there's dynamic queries and ORMs, which make everything worse. Now you cant even audit the source code, you have to observe behavior.

Most people don't know their applications have these vulnerabilities, but they're very common.

Post reply on HN