61–70 of 85 posts
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.
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.
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.
Quadrillions, yeah go find yourself a trino spark pipeline
row level access control, resource quotas, scheduling policies, session settings, etc. all could have been used in concert to achieve a very similar outcome with a dozen or so ddl/dcl statements.
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.
This sounds solvable with clickhouse views?
> automatic joins
Is this also not solvable with views? Also, clickhouse heavily discourages joins so I wonder how often this winds up being beneficial? For us, we only ever join against tenant metadata (i.e. resolving ID to name)
> query optimization
This sounds potentially interesting - clickhouse's query optimizer is not great IME, but it's definitely getting better
as a clickhouse architect, i can’t help but feel they would have been better served by understanding their db’s native capabilities better before jumping into implementing this. row level access control, resource quotas, scheduling policies, session settings, etc. all could have been used in concert to achieve a very similar outcome with a dozen or so ddl/dcl statements.
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…
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 here. There isn't access control defense in depth here. The best you can really do is do some data encryption of the data in the tables. We do some of that, but it's such a PITA that we relegate it to special stuff only.
> especially as SSO is rarely implemented with one policy - there's device attestation, 2FA, etc.
Sure but ALL of that relies on the SSO system behaving as advertised, so you think of it as separate policies, but it really isn't. It's once SSO CVE away from giving away the store. We use SSO with PG, that's how they authenticate to PG, we are fans of SSO too.
Earlier quoted context omitted.
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…
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 surprised with all the pushback about it being a single layer. Every other data access architecture will be a single layer too, it just can be made to look like it isn't. Or people think their bespoke access control system will be better because they have more control. Our experience taught us that's just bad thinking.
We've been doing direct access to PostgreSQL since 1993 without many issues. Though RLS is "recent" in terms of deployment(it came about in PG 10 I think). Before that we had a bespoke solution(written with lots of views and some C/pgsql code, it was slow and kind of sucked). RLS was a little buggy when it first was released, but within a year or so it was reliable and we moved everything over as quick as we could and haven't looked back.
> Encryption is an extremely powerful measure for this use case.
We do this with some data in some tables, but it's a PITA to do it right, so it's use is quite limited. We use Hashicorp Vault(now openbao) to hold the encryption/decryption keys.
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 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 it's generally not nearly as good as RLS. That's what we did before RLS and it sucked.
On top of that, you can do things like SSO, data encryption, etc, but those are not data access layers, those are different layers. We do these things too(tho very little of the data encryption part, since it's such a PITA to make work very reliably even with vault/boa holding the encryption keys for us).