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

51–60 of 85 posts

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

#51

Post by zie »

staticassertion wrote: post #16
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'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.

Well, I mean it's not only RLS, but yes it's only PostgreSQL doing the access control as far as if they can see a particular table or row.

Every user gets their own role in PG, so the rest of the PG access control system is also used.

We have your normal SSO system(Azure) and if Tootie employee doesn't need access to Asset Control, they don't get any access to the asset schema for instance.

What would be your method?

You would have some app that your dev team runs that handles access control, so your app gets unrestricted access to the DB. Now your app is the single boundary, and it forces everyone to go through your app. How is that better? It also complicates your queries, with a ton of extra where conditions.

A bunch of bespoke access control code you hope is reliable or a feature of the database that's well tested and been around for a long time. pgtap[0] is amazing for ensuring our access control (and the rest of the DB) works.

If some random utility wants to access data, you either have to do something special access wise, or have them also go through your app(let's hope you have an API and it allows for whatever the special is). For us, that random utility gets SQL access just like everyone else. They get RLS applied, etc. They can be naive and assume they have total control, because when they do select * from employees; they get access to only the employee column and rows we want that utility to have.

We have a bunch of tools over the decades that need access to various bits of our data for reason(s). Rather than make them all do wacky stuff with specialized API's, they just get bog standard PG SQL. We don't have to train vendor Tito how to deal with our stuff, we just hand them their auth info to PG and they can go to town. When people want Excel spreadsheets, they just launch excel, do a data query and their data just shows up magically. All from within Excel, using the standard excel data query tools, no SQL needed.

0: https://pgtap.org/

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

#52

Post by staticassertion »

zie wrote: post #51
staticassertion wrote: post #16

Earlier quoted context omitted.

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.

Well, I mean it's not only RLS, but yes it's only PostgreSQL doing the access control as far as if they can see a particular table or row. Every user gets their own role in PG, so the rest of the PG access control system is also used. We have your normal SSO system(Azure) and if Tootie employee doesn't need access to Asset Control, they don't get any access to the asset schema for instance. What would be your method?…

> What would be your method?

I don't know because I don't know your use case. At minimum, direct db access means that every postgres CVE something I'd have to consider deeply. Even just gating access behind an API where the API is the one that gets the role or accepts some sort of token etc would make me feel more comfortable.

> Now your app is the single boundary,

No, the app would still use RLS.

I'm not saying what you're doing is bad, but as described I'd be pretty uncomfortable with that deployment model.

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

#53

Post by zie »

lyjackal wrote: post #49
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 ap…

If you have people running crappy SQL SELECT, it can be a problem. statement-timeout[0] is your friend here. You still have to be on watch, and teach your users not to write crappy SQL.

You can also limit it by creating read-only replica's and making SELECT's happen on the replica. We don't usually bother, since 99% of our users are employees of ours, we can teach them to not be stupid. Since their usage doesn't change much over time, we can usually just hand them a SQL query and say: here run this instead.

Most of our employees don't even know they have SQL access, it's not like we force people to learn SQL to get their job done. Because of RLS and views, the ones that do SQL don't have to know much SQL, even if they do happen to use it. SELECT * from employees; gets them access to basically all the employee info they could want, but only to the employees they have access to. If you are a manager with 10 people, your select returns only your 10 people.

The payroll staff runs the same query and gets all of the employees they handle payroll for. Since our payroll is done inside of PostgreSQL(thanks plpython[1]), we can do some crazy access control stuff that most systems would never even dream about. Whenever new auditors come in and see that our payroll staff is limited to seeing only the info they need to do payroll, and only for their subset of employees they actually pay, they are awestruck.

The random vendors that can't be taught, we usually hand them a nightly SQLite dump instead. I.e let them pay the CPU cost of their crappy SQL.

Around client schema compatibility. This happens with other models too(API, etc). It's not unique to PG or SQL Databases. You have to plan for it. Since most all of our users interact with views and not with the actual underlying tables, it's not usually that big of a deal. In the extreme cases, where we can't just keep around a view for them, we have to help them along(sometimes kicking and screaming) into a new version.

0: https://www.postgresql.org/docs/current/runtime-config-clien...

1: https://www.postgresql.org/docs/current/plpython.html

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

#54

Post by zie »

staticassertion wrote: post #52
zie wrote: post #51

Earlier quoted context omitted.

Well, I mean it's not only RLS, but yes it's only PostgreSQL doing the access control as far as if they can see a particular table or row. Every user gets their own role in PG, so the rest of the PG access control system is also used. We have your normal SSO system(Azure) and if Tootie employee doesn't need access to Asset Control, they don't get any access to the asset schema for instance. What would be your method?…

> What would be your method? I don't know because I don't know your use case. At minimum, direct db access means that every postgres CVE something I'd have to consider deeply. Even just gating access behind an API where the API is the one that gets the role or accepts some sort of token etc would make me feel more comfortable. > Now your app is the single boundary, No, the app would still use RLS. I'm not saying what…

> No, the app would still use RLS.

I don't think you thought this through? The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need? It means the RLS is very wide open, since it needs to be able to see what User A and B can see. This forces your app to be the single boundary in pretty much all cases. Sure maybe you can give it a role where it has limited DDL rights(i.e not create table access or whatever).

> At minimum, direct db access means that every postgres CVE something I'd have to consider deeply.

I mean, not really, in practice? Most are just denial of service type bugs, not instant exploits. . Most of the DoS issues are not that big of a deal for us. They could affect us, but 99.9% of the time, they don't in reality, before we upgrade. RLS has been in PG for a good many years, it's quite stable. Sure, we upgrade PostgreSQL regularly, but you should do that anyway, regardless of RLS usage or not.

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

#55

Post by zie »

staticassertion wrote: post #27
skeeter2020 wrote: post #25

Earlier quoted context omitted.

row level security is not a feature specific to Postgres, but more a pretty standard and acceptable way to control access in a multitenant or multicontext environment that pretty much every data provider supports/implements. When it comes to answering a single specific question (like the one RLS targets) I believe you DO want a single, simple answer, vs. something like "it uses these n independent things working in c…

Right, RLS is great. What they are saying is this: > every employee can access our main financial/back office SQL database This means that there is no access gate other than RLS, which includes financial data. That is a lot of pressure on one control.

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 broke stuff. I can't remember a single instance of RLS causing us access control problems on a wide scale. Since we tied their job(s) to their access control many years ago, it's very rare that we even have the random fat-fingered access control issue for a single user anymore either. I think the last one was a year ago?

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

#56

Post by staticassertion »

zie wrote: post #54
staticassertion wrote: post #52

Earlier quoted context omitted.

> What would be your method? I don't know because I don't know your use case. At minimum, direct db access means that every postgres CVE something I'd have to consider deeply. Even just gating access behind an API where the API is the one that gets the role or accepts some sort of token etc would make me feel more comfortable. > Now your app is the single boundary, No, the app would still use RLS. I'm not saying what…

> No, the app would still use RLS. I don't think you thought this through? The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need? It means the RLS is very wide open, since it needs to be able to see what User A and B can see. This forces your app to be the single boundary in pretty much all cases. Sure maybe you c…

> I don't think you thought this through?

Well I'm not designing some arbitrary system. Don't expect a full spec.

> The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need?

You can still have users provide the access to the service (ie: the password to get to the role), or otherwise map between User A and the role, etc. The service just brokers and constrains access.

> Sure maybe you can give it a role where it has limited DDL rights(i.e not create table access or whatever).

Yes, of course. Just as you would with users.

> I mean, not really, in practice?

I don't think it's contentious to say that if RLS is your only security boundary then your pressure is entirely on that one boundary. How could it be any other way? If you want to say "It's an extremely good boundary", okay. There have been relevant vulnerabilities though and I really don't know that we should say that we should expect 0 vulnerabilities in RLS in the future such that every employee having access to a db containing financial data is fine. The point of layering is to avoid having to put all pressure on this one thing.

I don't even understand how this is contentious or confusing. If you have one boundary, you have one boundary. I'm suggesting that I'm uncomfortable with systems having one boundary.

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

#58

Post by staticassertion »

zie wrote: post #55
staticassertion wrote: post #27

Earlier quoted context omitted.

Right, RLS is great. What they are saying is this: > every employee can access our main financial/back office SQL database This means that there is no access gate other than RLS, which includes financial data. That is a lot of pressure on one control.

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 require one layer of defense.

I'm going to sort of skip over any comparison to SSO since I'm not going to defend the position of "SSO is fine as a single barrier", especially as SSO is rarely implemented with one policy - there's device attestation, 2FA, etc.

> RLS has been around a long time and is very stable and doesn't change much.

RLS is great, I'm a fan.

> I don't remember the last CVE or outage we had with PG that broke stuff.

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.

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

#59

Post by hrmtst93837 »

staticassertion wrote: post #16
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'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.

[flagged]

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

#60

Post by r1290 »

nlittlepoole wrote: post #48
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://p…

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