4 ms·

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 bot

by zie 7mo ago

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 https://www.postgresql.org/docs/18/ddl-rowsecurity.html

orf 7mo ago
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?
- tossandthrow 7mo ago
  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.
  - paulryanrogers 7mo ago
    Can Claude drop tables?
- weird-eye-issue 7mo ago
  RLS...
staticassertion 7mo ago
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 7mo 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 7mo 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 7mo 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 7mo 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 7mo 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 7mo 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.
lyjackal 7mo ago
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)
- zie 7mo ago
  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-client.html#GUC-STATEMENT-TIMEOUT https://www.postgresql.org/docs/current/runtime-config-clien... 1: https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html

by zie 7mo ago

orf 7mo ago

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?

tossandthrow 7mo ago
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.
- paulryanrogers 7mo ago
  Can Claude drop tables?
weird-eye-issue 7mo ago
RLS...

staticassertion 7mo ago

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

lyjackal 7mo ago

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)

zie 7mo ago
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-client.html#GUC-STATEMENT-TIMEOUT https://www.postgresql.org/docs/current/runtime-config-clien... 1: https://www.postgresql.org/docs/current/plpython.html https://www.postgresql.org/docs/current/plpython.html