5 ms·

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.
        dventimi 3y ago
        Judging by some of the comments here it wouldn't surprise me if there are some people operating under the mistaken belief that "full privileges" is the only option. Establishing that "no privileges" (or "nothing") technically IS another option establishes the boundaries of the "surface area" that can be exposed. Evidently, there are at least two possible discrete values for that surface area and "none" is one of them (the other being "all"). That's progress--albeit incremental progress--in this discussion. To me, the obvious next step is to ask and answer the question, "OK, can the exposed surface area take on any other values in the interval [none, all]?" to which I would answer, "Yes." For example, an OLAP system might with some justification be expected to offer more than 100ms statement timeout in order to support analytical workloads, but also continue to offer arbitrary queries to support exploratory analysis. Fine. Make the statement timeout 10000ms. In Redshift, say (you are using an analytical DB for analytical workloads, aren't you?) you can also impose quotas on rows fetched and data size returned. On the other hand, in an OLTP system (you are keeping your OLTP and OLAP workloads completely separate, aren't you?) for DEV you might have more stringent limits but still preserve a general-purpose SQL query interface. On yet another hand, in an OLTP system for PROD you might lock things down further by "white-listing" blessed queries by baking them into procedures, then turning off access to the underlying relations. The point which I would like us not to miss, is that we can dial the degrees of protection and of access up or down to suit our needs, right within the database. Sure, you can't have everything. You can't have an API that BOTH offers generous quotas AND ALSO a wide-open and flexible general-purpose SQL query API. Life is about trade-offs, after all.
        3y ago

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.
      - dventimi 3y ago
        Judging by some of the comments here it wouldn't surprise me if there are some people operating under the mistaken belief that "full privileges" is the only option. Establishing that "no privileges" (or "nothing") technically IS another option establishes the boundaries of the "surface area" that can be exposed. Evidently, there are at least two possible discrete values for that surface area and "none" is one of them (the other being "all"). That's progress--albeit incremental progress--in this discussion. To me, the obvious next step is to ask and answer the question, "OK, can the exposed surface area take on any other values in the interval [none, all]?" to which I would answer, "Yes." For example, an OLAP system might with some justification be expected to offer more than 100ms statement timeout in order to support analytical workloads, but also continue to offer arbitrary queries to support exploratory analysis. Fine. Make the statement timeout 10000ms. In Redshift, say (you are using an analytical DB for analytical workloads, aren't you?) you can also impose quotas on rows fetched and data size returned. On the other hand, in an OLTP system (you are keeping your OLTP and OLAP workloads completely separate, aren't you?) for DEV you might have more stringent limits but still preserve a general-purpose SQL query interface. On yet another hand, in an OLTP system for PROD you might lock things down further by "white-listing" blessed queries by baking them into procedures, then turning off access to the underlying relations. The point which I would like us not to miss, is that we can dial the degrees of protection and of access up or down to suit our needs, right within the database. Sure, you can't have everything. You can't have an API that BOTH offers generous quotas AND ALSO a wide-open and flexible general-purpose SQL query API. Life is about trade-offs, after all.
        3y ago