7 ms·

Sanitize... Just create a sql user with the correct access rights. Don't reinvent the wheel.

by reliablereason 3y ago

bvrmn 3y ago
Access rights don't help with SELECT resource abuse. It's the reason 2-tier architecture is long dead.
- dventimi 3y ago
  But row access policies do
sausagefeet 3y ago
Do you really think this is a good option? Even if you do properly enforce row-level security (Postgresql supports this), you're still exposing a lot of power to the user. They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. Row-level security is not easy to get right, as well. Maybe there is a library that can help you set this up.
- reliablereason 3y ago
  If you need row level security for some tables you can also solve that with views that are made with the appropriate rows. QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. For small applications with a hundred to a few hundred users using the databases own user management is quite handy. You can be very sure that you don't leek information and you can be quite certain that potential bugs in your application code won't lead to many of the common security vulnerability's that exist.
  - sausagefeet 3y ago
    > QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. It seems to me that there is a large gap between "inject SQL from the user directly into the database" and "provide a limited query language that can be translated to SQL". There are plenty of situations where the latter is totally fine. Is that reinventing the wheel? I don't think so.
- dventimi 3y ago
  No, they can't write some pretty rough queries that threaten QoS. They get a budget of 100ms to run whatever query they like, no matter how bad it is.
  - sausagefeet 3y ago
    That certainly is an option and might make sense in the context of some problems. I think limits like that tend to be problematic once some operations, validly, take longer. But YMMV. I think exposing the entire database to users is a pretty big surface area to hand out.
    - dventimi 3y ago
      Valid operations that take longer can be performed with another role with more generous allowances on statement timeout, but more stringent limitations on the allowable operations (e.g. substituting procedures for a general query interface).
      - sausagefeet 3y ago
        I'm not really sure how that solves the problem. They can still do anything in that account with more generous allowances. It feels like we're just adding complexity without much benefit. It also doesn't address my concern of handing out an interface with a very large surface area. But if this works for you, great. I don't think I'd be able to sleep well operating such a service.
        dventimi 3y ago
        They can't still do anything in that account with more generous allowances on statement timeouts. They can only do what permissions and policies allow, which could be "nothing." That's not a very large surface area.
        sausagefeet 3y ago
        I think we're missing the point. Clearly we're handing out access to the database and giving the user the ability to run arbitrary SQL for a reason, so giving them access where they can do "nothing" isn't going to solve whatever need we're trying to solve. The amount of security configuration going on to make this solution viable in any real system seems significantly more costly than just writing something that translates a small DSL to SQL.

Sanitize... Just create a sql user with the correct access rights. Don't reinvent the wheel.

bvrmn 3y ago

Access rights don't help with SELECT resource abuse. It's the reason 2-tier architecture is long dead.

dventimi 3y ago
But row access policies do

sausagefeet 3y ago

Do you really think this is a good option? Even if you do properly enforce row-level security (Postgresql supports this), you're still exposing a lot of power to the user. They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. Row-level security is not easy to get right, as well. Maybe there is a library that can help you set this up.

reliablereason 3y ago
If you need row level security for some tables you can also solve that with views that are made with the appropriate rows. QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. For small applications with a hundred to a few hundred users using the databases own user management is quite handy. You can be very sure that you don't leek information and you can be quite certain that potential bugs in your application code won't lead to many of the common security vulnerability's that exist.
- sausagefeet 3y ago
  > QoS could be a problem, indeed. If QoS it is an issue in the case one is working on, exposing your data as SQL might not be best choice, and neither is exposing any type of api that causes the server to have to do hard work. It seems to me that there is a large gap between "inject SQL from the user directly into the database" and "provide a limited query language that can be translated to SQL". There are plenty of situations where the latter is totally fine. Is that reinventing the wheel? I don't think so.
dventimi 3y ago
No, they can't write some pretty rough queries that threaten QoS. They get a budget of 100ms to run whatever query they like, no matter how bad it is.
- sausagefeet 3y ago
  That certainly is an option and might make sense in the context of some problems. I think limits like that tend to be problematic once some operations, validly, take longer. But YMMV. I think exposing the entire database to users is a pretty big surface area to hand out.
  - dventimi 3y ago
    Valid operations that take longer can be performed with another role with more generous allowances on statement timeout, but more stringent limitations on the allowable operations (e.g. substituting procedures for a general query interface).
    - sausagefeet 3y ago
      I'm not really sure how that solves the problem. They can still do anything in that account with more generous allowances. It feels like we're just adding complexity without much benefit. It also doesn't address my concern of handing out an interface with a very large surface area. But if this works for you, great. I don't think I'd be able to sleep well operating such a service.
      - dventimi 3y ago
        They can't still do anything in that account with more generous allowances on statement timeouts. They can only do what permissions and policies allow, which could be "nothing." That's not a very large surface area.
        sausagefeet 3y ago
        I think we're missing the point. Clearly we're handing out access to the database and giving the user the ability to run arbitrary SQL for a reason, so giving them access where they can do "nothing" isn't going to solve whatever need we're trying to solve. The amount of security configuration going on to make this solution viable in any real system seems significantly more costly than just writing something that translates a small DSL to SQL.