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

41–50 of 85 posts

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

#41

Post by weird-eye-issue »

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

Earlier quoted context omitted.

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.

RLS is one of the simplest things to set up properly, if you can't figure it out I don't think I'm the one who doesn't know what they're talking about.

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

#42

Post by staticassertion »

skeeter2020 wrote: post #34
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.

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

Just to be clear, I am extremely pro-RLS.

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

#43

Post by mattaitken »

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

This is cool. I think for our use case this wouldn’t work. We’re dealing with billions of rows for some tenants.

We’re about to introduce alerts where users can write their own TRQL queries and then define alerts from them. Which requires evaluating them regularly so effectively the data needs to be continuously up to date.

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

#44

Post by otterley »

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 large are these data volumes? How long does it take to prepare the data when a customer request comes in?

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

#45

Post by Philip-J-Fry »

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

Earlier quoted context omitted.

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.

Right, encryption would protect the data. But still, at the end of the day you're trusting the permission model of the database. Encryption won't prevent you updating a row or deleting a row if the database permission model failed.

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

#46

Post by bob1029 »

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

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

#47

Post by staticassertion »

Philip-J-Fry wrote: post #45
staticassertion wrote: post #35

Earlier quoted context omitted.

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.

Right, encryption would protect the data. But still, at the end of the day you're trusting the permission model of the database. Encryption won't prevent you updating a row or deleting a row if the database permission model failed.

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 primitive for layering on additional security measures.

Encryption is an extremely powerful measure for this use case. If the data does not need to be indexed, you could literally take over the database process entirely and still not have access, it definitely doesn't rely on the permission model of the db because the keys would be brokered elsewhere.

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

#48

Post by nlittlepoole »

bob1029 wrote: post #46

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

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

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

#49

Post by lyjackal »

zie wrote: post #3

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

I want to build a shared postgres db with hundreds of small apps (OLTP) accessing shared tables using a RLS model model against well defined tables.

What are other limitations and mitigations folks have used or encountered to support stability and security? Things like

  - Query timeouts to prevent noisy neighbors
  - connection pooling (e.g. pgbouncer) also for noisy neighbors
  - client schema compatibility (e.g. some applications running older versions, have certain assumptions about the schema that may change over time)

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

#50

Post by cjonas »

otterley wrote: post #44
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 large are these data volumes? How long does it take to prepare the data when a customer request comes in?

Small. We're dealing with financial accounts, holdings and transactions. So a user might have 10 accounts, thousands of holdings, 10s of thousands of transactions. Plus a handful of supplemental data tables. Then there is market data that is shared across tenants and updated on interval. This data is maybe 10-20M rows.

Just to clarify, the data is prepared when the user (agent) analytics session starts. Right now it takes 5-10s, which means it's typically ready well before the agent has actually determined it needs to run any queries. I think for larger volumes, pg_duckdb would allow this to scale to 10s of millions rows pretty efficiently.

Post reply on HN