Sanitize...
Just create a sql user with the correct access rights. Don't reinvent the wheel.
Just create a sql user with the correct access rights. Don't reinvent the wheel.
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.
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.
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.
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.
> 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.
"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 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.
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.
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.
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.
https://asciinema.org/a/AVEDmoFRlciDxpXx3PYk1ERXy
It's just 4 lines:
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
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.
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.
> 1. There is a limit to the customization 2. They are generally harder to test and change
Those aren't problems that I have.
> 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?
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
https://gitlab.com/dalibo/postgresql_faker
> 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?
You bet.
https://asciinema.org/a/629243
> 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:
https://www.postgresql.org/docs/current/ddl-rowsecurity.html