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.
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.
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.
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.
> 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.
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.
> 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.
> I'm implementing a language that is more user friendly for users to work with than SQL
"User-friendly" is a matter of judgement not of fact.
> 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
Again, that's not a statement of fact, but setting that aside personally I'm unimpressed. It's easy to imagine multinenant databases with sensitive information providing safety guarantees in under 200 lines of DDL.
I don't even really know what we're disagreeing about at this point. My statement from the beginning has been that handing out direct SQL access to users is not a universal solution. It can work in some cases but there are definitely cases where it is not a valid solution to the problem at hand. I happen to believe that in a realistic production database your solution is going to be significantly more complicated. I have presented a crude measurement for the complexity of my solution, and your response has just been "well, I could imagine it's just as small doing it my way". Ok, I can imagine a lot of things, too.
I feel that your responses have been scoped at just trying to invalidate my most recent response rather than discussing the system as a whole and the pros and cons of various solutions, and that makes me feel frustrated.
I'm sorry you're frustrated. I admit that I am scoping my responses to your most recent response rather than trying to discuss the system as a whole. In my experience, that's common in threaded comment systems. Whatever its shortcomings, it does help combat non-sequiturs. If you'd rather discuss the system as a whole, we can do that.
As for what we're disagreeing about, maybe this will help. In this thread I have tried to say the following:
1. I believe that using a SQL role with the correct access rights, augmented by other features within modern databases, is a good option. It's not the ONLY good option, but it's not the case that it's never a good option.
2. I believe that it's not NECESSARILY true that a user could write some pretty rough queries that threaten QoS against a SQL API. There ARE steps that can be taken, right within the database.
3. I believe that exposing a SQL database to users is not NECESSARILY handing out a big surface area. Modern databases offer tools to limit the size of that surface area. They may not limit it in all possible ways to satisfy every possible person (e.g. I'm not aware of any SQL database that allows you to forbid joins, for example), but it's a mistake to believe that you can't limit the surface area AT ALL.
4. I believe that using these features (RBAC, RLS, data types, constraints, triggers, views, quotas, etc.) in development is not NECESSARILY more costly than writing something that translates a small DSL to SQL.
5. I believe it's difficult to quantify this cost or effort but in any case it's not orders-of-magnitude more difficult to use the database than to compile a DSL.
6. I believe it's presumptuous to declare that other people don't need all the power of SQL. Some people may not need it or want it, and that's fine, but if other people say that they do need it or want it, I'm not going to tell them they're wrong.
7. I believe NEITHER of us is in a position to tell other people that they're doing it wrong, or that what they're doing isn't valid, on matters of judgment. If you like compiling a DSL and it's good for you, I'm not going to say that's not valid. I'd appreciate it if you'd do likewise when I say that I like using the database because it's good for me.
8. I believe BOTH of us, however, are free to say that something is incorrect on matters of fact (e.g. numbers 1 - 4 above).
If you don't disagree with any of these things then perhaps we're not disagreeing about anything after all.
I agree with most of this. The things I don't agree with entirely are small:
3. I do think it is a big surface area. It can be reduced and, in this thread, I have never stated you cannot reduce it at all.
6. I did not state this, at least, but I did state that for my use case, the interface I am providing is more user-friendly. That does not mean all use-cases match mine. In particular, the query DSL we have expands out to some large SQL that would be difficult to write.
I think the only real disagreement we have is how expensive the various solutions are to implement. I still don't buy that for a real-world dataset, the security engineering would not be onerous relative to a DSL, and you haven't provided anything concrete to counter that. But that's fine, it's not your responsibility to convince me. Thank you for the lively discussion.
> 3. I do think it is a big surface area. It can be reduced and, in this thread, I have never stated you cannot reduce it at all.
No, but you did state, "I think exposing the entire database to users is a pretty big surface area to hand out." without qualification. Owing to the ambiguities of human language I never know for sure what people are saying online, but it seemed plausible to me that you thought the surface area couldn't be reduced at all. I welcome your clarification, so thank you for that.
> 6. I did not state [people don't need all the power of SQL].
Well, you did state, "We don't need all of the power of SQL." Sorry, but I took "we" to be "all of us." Perhaps "we" meant just you and your colleagues for your particular use-case. Again, I would be grateful for clarification though you're not obliged to provide it.
> I still don't buy that for a real-world dataset, the security engineering would not be onerous relative to a DSL.
Let's get down to brass tacks. The "security engineering" which I don't think would be onerous comprises:
- basic data types
- custom data types
- custom domains
- primary key constraints
- foreign key constraints
- check constraints
- triggers
- procedures
- views
- Row Based Access Controls (RBAC)
- Row Level Security (RLS)
- recource limits (e.g. statement timeouts)
Do you still consider these to be onerous?
> and you haven't provided anything concrete to counter that
Let me ask you this. Take your use-case of "a multi-tenant database with sensitive information in it that would be bad if somehow another user was able to access it." I don't know the details so for all I know it could be something simple. In that case, it could be handled with something like what's depicted in this demo.
grant all on tenant to tenant; -- use RBAC for the table
create policy tenant on tenant as restrictive for all using (id = current_setting('session.id')::uuid); -- RLS specific policy
create policy global on tenant as permissive for all using (true); -- RLS general policy
alter table tenant enable row level security; -- Turn on RLS for the table
Granted, your use-case probably isn't this simple. Then again, we still have 196 lines to work with before we hit the dreaded 200.
It's been a long day and this thread is getting awful deep, so I don't expect you to respond. If you'd like to continue it in different channel (e.g., GitHub discussions) I'd be happy to oblige, however. Peace.
I actually like and use database security features. However, there are generally two problems with them:
1. There is a limit to the customization
2. They are generally harder to test and change
Application code is usually optimized for both points. So for me, I actually prefer a combination: use rough database security features to reduce the blast-radius of a bug in the application code. And use application code for everything else, since it's easy to customize, easy to understand, independent of the database technology and very easy to test.
> Well, you did state, "We don't need all of the power of SQL." Sorry, but I took "we" to be "all of us." Perhaps "we" meant just you and your colleagues for your particular use-case.
The context of that sentence is talking about the specific proposal of the blog post, which states that their usecase did not require all of SQL. I can understand why it would be confusing as I switched to "we" in the next sentence. For my usecase, as well, we do not need all of the power of SQL.
> Let's get down to brass tacks. The "security engineering" which I don't think would be onerous comprises
> ...
> Do you still consider these to be onerous?
What you have listed is not an actual solution, just a list of things you'd need to solve it. The security engineering is how you actually use it. I could say the only thing you need for the DSL to SQL solution is a programming language, my list would be one item, but would that be reflective of the actual complexity in solving the problem? No. So I cannot say whether or not it is onerous. Additionally, the solution I mentioned is around 200 SLOC, but it has another 200 SLOC of tests, so how to test this is a valid question as well. My tests also don't require a database to with data in it to test, we just validate that the query looks like what we expect it to.
> Granted, your use-case probably isn't this simple. Then again, we still have 196 lines to work with before we hit the dreaded 200.
Thank you for the example, it clarifies in my head more how this would be implemented. Do you have an example of how this works if you are performing joins between tables? Does every table need to have some sort of user id in it directly for that to work?
> What you have listed is not an actual solution, just a list of things you'd need to solve it
Sorry. HN comments is a narrow channel and so I elected not to try to squeeze a full-blown actual solution through it.
> I could say the only thing you need for the DSL to SQL solution is a programming language, my list would be one item
Well...I enumerated the features of the language I'm using just as you could enumerate the features of your programming language. My list could have one item just as easily as yours can: "PostgreSQL DDL"
> how to test this is a valid question as well
My answer to that question has been to use pgTAP for testing and postgresql_faker to generate synthetic data
> My tests also don't require a database to with data in it to test
No, but they do require a runtime, be it in Scala or whatever. That's no different from my case where my test runtime is an ephemeral PostgreSQL database.
> Do you have an example of how this works if you are performing joins between tables?
> Does every table need to have some sort of user id in it directly for that to work?
It's common for single-database multi-tenant data models to add something like a "tenant_id" to every table. It's simple, more efficient, and more foolproof. You can however just join to other tables in the policy condition as I have done. Extra care should be taken as discussed in the PostgreSQL docs:
Thank you for the information. I've even started to use the statement timeout in my DSL implementation to ensure that queries are bounded. I think we view the relative costs of the implementations quite differently, probably due to our backgrounds, but this has been very enlightening for me, thank you.
