14 ms·

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

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.

weird-eye-issue 6mo ago
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.
- staticassertion 6mo ago
  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.
  - weird-eye-issue 6mo ago
    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.
    - staticassertion 6mo ago
      I don't think you have any idea what you're talking about, certainly you don't know what I'm talking about.
      - weird-eye-issue 6mo ago
        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.
  - skeeter2020 6mo ago
    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.
    - staticassertion 6mo ago
      Just to be clear, I am extremely pro-RLS.
  - zie 6mo ago
    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 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).
    - staticassertion 6mo ago
      > 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.
Philip-J-Fry 6mo ago
Conceptually that's no different to any security measures that prevent you from accessing data you're not supposed to? 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. We trust that Amazon or Google or Microsoft are successful in protecting customer data for example. We trust that when you log into your bank account the money you see is yours, and when you deposit it we trust that the money goes into your account. But it's all just mostly logical separation.
- staticassertion 6mo ago
  > 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.
  - Philip-J-Fry 6mo ago
    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.
    - staticassertion 6mo ago
      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.
      - 6mo ago
skeeter2020 6mo ago
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 conjunction..."
- staticassertion 6mo ago
  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.
  - zie 6mo ago
    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?
    - staticassertion 6mo ago
      > 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.
zie 6mo ago
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/ https://pgtap.org/
- staticassertion 6mo ago
  > 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.
hrmtst93837 6mo ago
[flagged]

weird-eye-issue 6mo ago

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.

staticassertion 6mo ago
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.
- weird-eye-issue 6mo ago
  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.
  - staticassertion 6mo ago
    I don't think you have any idea what you're talking about, certainly you don't know what I'm talking about.
    - weird-eye-issue 6mo ago
      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.
- skeeter2020 6mo ago
  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.
  - staticassertion 6mo ago
    Just to be clear, I am extremely pro-RLS.
- zie 6mo ago
  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 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).
  - staticassertion 6mo ago
    > 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.

Philip-J-Fry 6mo ago

Conceptually that's no different to any security measures that prevent you from accessing data you're not supposed to? 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. We trust that Amazon or Google or Microsoft are successful in protecting customer data for example. We trust that when you log into your bank account the money you see is yours, and when you deposit it we trust that the money goes into your account. But it's all just mostly logical separation.

staticassertion 6mo ago
> 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.
- Philip-J-Fry 6mo ago
  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.
  - staticassertion 6mo ago
    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.
    - 6mo ago

skeeter2020 6mo ago

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

staticassertion 6mo ago
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.
- zie 6mo ago
  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?
  - staticassertion 6mo ago
    > 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.

zie 6mo ago

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/ https://pgtap.org/

staticassertion 6mo ago
> 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.

hrmtst93837 6mo ago

[flagged]