5 ms·

Valid operations that take longer can be performed with another role with more generous allowances on statement timeout, but more stringent limitations on the a

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.
      - sausagefeet 3y ago
        > You can't have an API that BOTH offers generous quotas AND ALSO a wide-open and flexible general-purpose SQL query API. But that's not what's being proposed here. What's being proposed is a query language that users can write that compiles to SQL. We don't need all of the power of SQL, all of the functions of SQL. We can wrap the query the user provided in some extra checks to limit what they can see. This is orders of magnitude simpler to implement with a simple compiler than all of the security engineering you've proposed.
        dventimi 3y ago
        I'm not evaluating what's being proposed here. In this thread anyway, I'm responding to this comment, which I regard as untrue: > They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. However, were I to evaluate what's being proposed I would say it's predicated on a matter of judgement rather than a matter of fact: you may not feel you need "all the power of SQL", you may feel that the proposal is "orders of magnitude simpler", and you may feel that things like "row level security are tricky to get right", and that's fine, but others are not obliged to share your feelings. I'm sorry, but I don't. I just don't believe that using the security engineering affordances offered by modern databases is difficult to do, no matter how many times you tell me I should believe that.
        sausagefeet 3y ago
        But several things are a matter of fact. > you may not feel you need "all the power of SQL" It's specified in the blog post. It does depend on the specific use case you have. In my case, I am implementing a language that is more user friendly for users to work with than SQL but compiles down to it, so in my case giving them straight SQL access would not fit the feature requirements. But, of course, it depends. > you may feel that the proposal is "orders of magnitude simpler" We can debate if it's literal "orders of magnitude" but I have a hard time seeing how for any complicated database, correctly implementing, testing, and guaranteeing future correctness, of handing access to the database out to users is less complicated than compiling some SQL. For my usecase, we have a multi-tenant database with sensitive information in it that would be bad if somehow another user was able to access it. The solution of compiling to SQL I have implemented in around 200 lines of code plus another 200 (and growing) of tests. I am making a concrete statement here: that 200 lines of code is a lot simpler than whatever you will have to do for security engineering. That does not mean what you're proposing is not the right choice given whatever constraints you have, but tell me how it is simpler.

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.
    - sausagefeet 3y ago
      > You can't have an API that BOTH offers generous quotas AND ALSO a wide-open and flexible general-purpose SQL query API. But that's not what's being proposed here. What's being proposed is a query language that users can write that compiles to SQL. We don't need all of the power of SQL, all of the functions of SQL. We can wrap the query the user provided in some extra checks to limit what they can see. This is orders of magnitude simpler to implement with a simple compiler than all of the security engineering you've proposed.
      - dventimi 3y ago
        I'm not evaluating what's being proposed here. In this thread anyway, I'm responding to this comment, which I regard as untrue: > They could write some pretty rough queries that could make guaranteeing QoS difficult or impossible. However, were I to evaluate what's being proposed I would say it's predicated on a matter of judgement rather than a matter of fact: you may not feel you need "all the power of SQL", you may feel that the proposal is "orders of magnitude simpler", and you may feel that things like "row level security are tricky to get right", and that's fine, but others are not obliged to share your feelings. I'm sorry, but I don't. I just don't believe that using the security engineering affordances offered by modern databases is difficult to do, no matter how many times you tell me I should believe that.
        sausagefeet 3y ago
        But several things are a matter of fact. > you may not feel you need "all the power of SQL" It's specified in the blog post. It does depend on the specific use case you have. In my case, I am implementing a language that is more user friendly for users to work with than SQL but compiles down to it, so in my case giving them straight SQL access would not fit the feature requirements. But, of course, it depends. > you may feel that the proposal is "orders of magnitude simpler" We can debate if it's literal "orders of magnitude" but I have a hard time seeing how for any complicated database, correctly implementing, testing, and guaranteeing future correctness, of handing access to the database out to users is less complicated than compiling some SQL. For my usecase, we have a multi-tenant database with sensitive information in it that would be bad if somehow another user was able to access it. The solution of compiling to SQL I have implemented in around 200 lines of code plus another 200 (and growing) of tests. I am making a concrete statement here: that 200 lines of code is a lot simpler than whatever you will have to do for security engineering. That does not mean what you're proposing is not the right choice given whatever constraints you have, but tell me how it is simpler.