SQL as API
valentin.willscher.de
valentin.willscher.de
Not to mention this practically invites dumb string interpolation in SQL preparation, undoing decades of work to rid the world of this terrible habit. (Edit: This problem is solvable.)
Don’t try to be clever, use a structured format (JSON, protobuf, etc.) with a well documented schema.
Note that I’m only talking about APIs. It’s an entirely different matter if your product allows users to type in and send complex queries interactively.
* Forget about nascent LLM-interpreted natural language as programming language.
"and": [ { "property": "material", "operator": "equals", "value": "carbon" },
Would be;
["and", ["=", "material", "carbon"], ...]
https://developers.google.com/google-ads/api/docs/query/over... https://developer.intuit.com/app/developer/qbo/docs/learn/ex... https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.... https://carto.com/developers/sql-api/
From an end user standpoint, I preferred working with such API than with the ones creating nested json filters structures to model the filtering logic. It also provides a convenient way to select fields in the response, better in my opinion than "field mask" alternative that you can find on other APIs.
I don't see an issue in failing to optimize queries. The database does query planning on the fly. You do have to be a bit careful that you don't generate queries that can DoS your DB, but all solvable and not drastically different than a developer adding a new query to the product. What we do does create a prepare statement on the fly. We process the input query and generate SQL and collect each value the user as input into arrays for the types the correspond to and then index those arrays. So, for example, the query "pr:123 and (user:foo or user:bar)" would turn to SQL like: "pull_request = ($1)[1] and (user = ($2)[1] or user = ($2)[2])" (this allows us to statically type our queries).
In short, if your requirements that users should be able to generate complex queries, I think this is significantly better than JSON. And you can process an input language into JSON and the problem is effectively the same from that point.
Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.
Even if there are three applications, this blog post isn't saying every single problem has to be solved this way, so if you're one of the people solving one of those three problems, this could be a great solution for you.
> Databases do query planning, but some of them cache query plans based on the SQL text before substituting arguments, and may optimise reoccurring queries more aggressively.
Ok, and? Even if we go with your JSON solution, you still need to query the database at the end of the day, and your JSON query is not going to translate into the exact same SQL every time (unless you're doing very limited set of operations). I'm not sure what the real advice or information is here. Are you saying we should just never do some query language that translates to SQL at all? All SQL queries should just be dynamic on the input parameters? Even if your statement on query plan optimization is true, how do we know it matters for the product? Maybe slightly longer queries are totally fine in this context?
I used it for this exact usecase, we had some data in postgress and front-end had limited api, and readonly postgrest was simpler than adding the api I needed to the frontend.
This means, extending the API by allowing more and more parts of SQL is easy and still backwards compatible.
It's much harder to come up with a custom language / structure from the get-go that doesn't cause backwards-compatibility issues down the road. I guess I should have emphasized this point in the article a bit more, thank you for the comment.
Okay, so now you find some way to bundle many queries, with allowances for connecting the result of one query to another, into a single request with all the results in a single response. Congratulations, you just recreated GraphQL.
I'm not saying that I suggest to do that, but for the specific point that you mention, I don't see any theoretical advantage of GraphQL in terms of performance.
I'm not sure I understand. GraphQL doesn't need to be, nor is it meant to be, powerful. Its only purpose in life is to roll up multiple actions into a single response made from a single request. It was originally designed for those actions to be REST/RPC endpoints, but if you have an SQL API endpoint the same applies.
SQL as an API isn't some novel thing. Your DBMS already does exactly that! We've been using SQL as an API for 50-some-odd years and will probably still be using it for APIs in 50 more. But, there is good reason why we have added layers on top in the datacenter. Don't expect SQL to be a suitable API for the entire world to use.
> which allows to aggregate all necessary data within the same SQL query and being returned as a single response.
If you push SQL to really pained lengths it can be done, but with a whole lot of added overhead elsewhere. There is no free lunch. Best to leave SQL to the job it was designed for; the job it is good at. It's okay to let it have help.
I still don't get your point. Maybe a conrete example helps: listing the contents of a user's posts. In graphql:
users(id: "x") {
postIds # don't really need those
posts {
text
}
}
If the backend is half stupid, it will run 2 SQLs here. If it's smart, it will resolve the whole thing with one join.
So far there is no performance benefit over writing a single SQL with a join and having the backend execute it directly.
Still, I want to emphasize that I'm not advocating to do the latter - it has various drawbacks. But just im terms of performance, there is no disadvantage for the SQL solution.
Even if we look at running 2 completely different semantical queries in the same graphql request, we can do the same in SQL with a width-clause. With the technique shown in the article, it's really easy to support that in the API.
Yes, as a practical engineer that is what you would do to deal with the overhead you have encountered in the datacenter, but you would realize that you are abusing SQL to hack around overhead issues. That is not what you would do in a less constrained environment. Logically, users and posts are distinct relations as it pertains to that example. They have no business being joined here. There is a place for joins, but that's not it.
So already, for the simplest case imaginable, you are using SQL outside of the way it was designed to be used. Which is fine if it gets the job done. Purity can step to the side for the sake of engineering, of course. We are not purists here. But as the complexity ramps up, there will be a point where it stops being a decent tradeoff and enters a point of being ridiculous – where you've ended up recreating GraphQL... but poorly.
So what would you do in your world? Run one SQL to get the post's ids and then run another query with that list to get the content for those posts?
I know that's not exactly practical in the real world. Maybe if you dataset is tiny you can get away with it, but any more you'll soon be killed by the overhead of all those queries in the datacenter, let alone over flaky networks.
So, in the real world you have to find a workaround. Using a join here is a decent workaround with decent tradeoffs. You can condense what might be hundreds of queries into one. It does not come free. There is no free lunch. But the tradeoffs are acceptable in a lot of cases.
But we're also talking about the simplest case imaginable. While it is a reasonable tradeoff for that simple example, those hacks don't scale particularly well as the complexity grows. SQL is not designed for one query to return everything and the kitchen sink.
What you are talking about is commonly known as the n+1 problem. It is a problem because the mathematically pure solution does not usually work in the real world due to real world overhead constraints. If computers operated in an idealized world the n+1 problem wouldn't exist, but we live in a harsh reality where not everything works out so perfectly. If joins were meant to be used this way, obviously it wouldn't be a problem in the first place... But it is a problem because that is not what joins are designed for, even if they can help deal with the problem in some cases.
Obviously you can use things beyond what they are designed for, but in this case it only works in the small scale. Give us a complicated example and watch the nightmare unfold. Again, SQL is designed for working with the relational model. The example GraphQL schema is not relational. It describes a graph model. There is, as they say, an impedance mismatch. The closest approximation to a graph in the relational model is multiple relations, and multiple relations in SQL requires multiple queries. In SQL, one query always returns just one relation.
- GraphQL isn't meant to bundle many operations together. People mean things and the people who created GraphQL said what they meant and this isn't it, or at least it isn't all of it.
- SQL isn't meant to be used without joins and it isn't being abused by the presence of joins.
- "users" and "posts" in the example are not unrelated. We were explicitly told by the author of the example that they ARE related.
- consequently it's not true that there's no business joining them. On the contrary there are very good reasons for doing exactly that.
- doing that--making those joins--is not a hack
- while it's true that a SQL query returns only one relation, there's nothing inherently wrong with it being a relation over a nested data type like XML or GraphQL.
In my view, you are causing real harm but spreading false information on this subject.
Agreed. There was nothing to suggest otherwise. Did you not bother to read anything before replying?
> "users" and "posts" in the example are not unrelated.
They are related as per the graph, but that does not make them related in a relation. These are not equivalent models. After all, if they were suitably related in a relation you wouldn't have two tables in which to join. They would already exist in one relation. A simple `SELECT * FROM kitchen_sink` would do.
> On the contrary there are very good reasons for doing exactly that.
Yes, we went over them in detail. How did you end up here without reading a single word?
> consequently it's not true that there's no business joining them.
That's right, there is a good reason to join them: To overcome the overhead problem, better known as the n+1 problem. That would still be a hack if using an idealized system, or even a practical system that focuses on minimizing said overhead, like SQLite.
In fact, the SQLite docs even tell you should not resort to such hacks while using SQLite as it is not necessary.
> there's nothing inherently wrong with it
That's right. Nobody said there was anything wrong with it. I even explicitly stated it was a good solution in many cases. I still don't understand how you managed to get here without reading a single thing.
> the people who created GraphQL said what they meant and this isn't it
I also said what I meant, but that didn't stop you from going off to la-la land. What makes you so sure you understood what they said when you can't even manage this simple conversation?
> In my view, you are causing real harm but spreading false information on this subject.
You haven't even read the discussion... But go on, let's assume I spread some falsehood. What harm has been caused?
This you? "If you were to use SQL as designed, with an idealized implementation, you would first query the users and then run a query for each user to retrieve the the corresponding posts"
> Yes, we went over [the very good reasons for joining "users" and "posts"] in detail
You literally wrote, "Logically, users and posts are distinct relations as it pertains to that example. They have no business being joined here."
> That's right, there is a good reason to join them: To overcome the overhead problem, better known as the n+1 problem
That's not right. That is not the reason to use joins.
> That would still be a hack if using an idealized system, or even a practical system that focuses on minimizing said overhead, like SQLite.
Joining tables in general and joining these tables in particular is not a hack.
> In fact, the SQLite docs even tell you should not resort to such hacks while using SQLite as it is not necessary.
Evidence or it didn't happen.
> there's nothing inherently wrong with [a nested data type like XML or JSON]
You also wrote, "Obviously you can use things beyond what they are designed for, but in this case it only works in the small scale. Give us a complicated example and watch the nightmare unfold. Again, SQL is designed for working with the relational model. The example GraphQL schema is not relational. It describes a graph model. There is, as they say, an impedance mismatch. The closest approximation to a graph in the relational model is multiple relations, and multiple relations in SQL requires multiple queries. In SQL, one query always returns just one relation."
Look, I read your comments many times despite your multiple incorrect assertions that I did not. If I haven't understood you perfectly, well in my defense it's because I'm dealing with gibberish like this. It sure seems like you're insinuating there's some kind of problem using SQL to generate the response for GraphQL queries and your reasoning if we can call it that seems to rest in part on the fact that a SQL query returns just one relation. Yes, that's true, but no, that is not a problem for generating the responses for GraphQL.
> I also said what I meant [about the purpose of GraphQL]
You said, "GraphQL doesn't need to be, nor is it meant to be, powerful. Its only purpose in life is to roll up multiple actions into a single response made from a single request"
No, rolling up multiple actions into a single response isn't the only reason GraphQL was created.
> You haven't even read the discussion.
Wrong. Do you always try to take the easiest way out when your reasoning is subjected to scrutiny?
> What harm has been caused [by my comments]?
Oh, I don't know. How about by promulgating the idea that joins are somehow a "hack" for starters?
> Logically, users and posts are distinct relations as it pertains to that example. They have no business being joined here.
> There is a place for joins, but that's not it.
> So already, for the simplest case imaginable, you are using SQL outside of the way it was designed to be used.
I'm sorry. What?
Note: yes, I added the word "rational" here to be explicit but which I only implied earlier. If you want to quibble about that let me save you the trouble. If you want to say that you had a reason, but it just wasn't a rational one, I'll accept that. After all, I'm not unreasonable.
The second half of the comment suffers much the same problem. If I wanted to say that the reason was rational (or not), I would need to know under what context it is being said. The reason is undoubtedly both rational and irrational, depending on context. There is not enough information in this thread to establish under what context the reader is intending to interpret the assertion should one want to assert it.
Oh well! Happy New Year. Try sometime new. Read a book about relational databases for a change. Peace!
By avoiding going down that pointless road to nowhere, you offered me some wonderful creative writing that I have been quite amused by. Had I put you in your place instead, all I would have gotten in the end is something to the effect of "Oh, yeah." How boring. That would have been the biggest waste of time ever. The outcome here has been much more beneficial.
And with that, I thank you for the amusement you provided. It has been fun.
I've been playing around with this idea of using this language as a colloquial standard for writing things like a DB poxy/cache or a "standard" interface for something like a ECS based OLAP-esque on columnar stores. Nothing to report at the time but I love to hear from people who have worked on a similar thing.
I'm expecting raw SQL to replace the API for a whole class of apps.
Throw in an event sourcing architecture for server side processing and you have a single SQL based API for all operations.
In the normal usage we have special tag prefixes with meaning, and now each query entry point has the same query language and you just need to know what the tag prefixes mean for that context.
I haven't done it yet, but next step is to add a complexity limit, that way people just can't send us queries that will DoS the database.
- it's insecure (update anything, anywhere! just need to get by check constraints)
- it's slow (table scans, when I meant to use index)
- it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns).
Solve that by introducing some sort of capability system into the query language (think foreign keys, forward or backward), and we can make major strides on all fronts.
It's entirely possible to add permissions and access control on any modern dbms.
> - it's slow (table scans, when I meant to use index)
Maybe you are using an OLTP when you meant to use an OLAP dbms?
> - it's bad for horizontal scaling (things like sharding fundamentally relies on shrinking the domain of access patterns).
See above.
SQL is more adequate as a "language" or "standard" because that's what it is. What matters is that the underlying dbms that offers this functionality is appropriate.
SQL is more than adequate as a "language" or a "standard".
My understanding is this is all mandatory access control which is cumbersome as usual.
> Maybe you are using an OLTP when you meant to use an OLAP dbms?
"It's slow" was too pithy. When it's fast it great, but it's too easy to write things that are slow my mistake. I want things to be rejected if it they don't use an index in many cases, not fall back on a slow table scan.
The most key part is that I posit that the "when are table scans allowed?" policy is very similar to the security policy.
The dream I have is that if one carefully writes a schema (in a whitelist, not blacklist manner), all the information is there to make:
1. Exposing the database to the public safe. 2. Automatically know how to scale out if more machines are added. 3. Easier to understand cost of operations from the syntax alone, without needing to consult the query planer.
This is very exciting to me!
I don't literally want just a SQL user interface to my applications, but I like to imagine if I did, think about the potential problems, then try to be resourceful in solving those problems right within SQL before giving up and resorting to other tools. I believe you can go very far with this approach, maybe not all the way, but farther than people tend to give SQL credit for. At the end of the day, I still need something else in addition to SQL, but the "something else" turns out to be smaller and less involved than it would have been had I been governed by the accumulated received wisdom.
... WHERE (either A or B or C) (either beginswith OR endswith) (either X or Y or Z)
This isn't just limited to query languages. In many programming languages, it strikes me as odd that I can't write something like `if A is (either X or Z)`, which mirrors how I would naturally express the condition in English, but need to reformulate it as `if A is X or A is Z`, or (worse IMO from the readability standpoint) something like `if [X,Z].includes(A)`.
Don't be too quick to assign rationality where its absence will do. If SQL has been despised it's quite possible the reasons have never been any good.
See https://vincent.bernat.ch/en/blog/2023-sql-like-language-fil....
We have been seeing this trend (or pendulum swing) of pushing SQL and simplicity. I am not saying this solution is simple (will leave that for discussion).
Tangential, if this kinda stuff is interesting, folks might be interested in what Omnigres (disclaimer: my employer) is doing with the python integration in Postgres
INSERT requests would create forms, SELECT would create a table.
The idea was that you could create something simple like an BBS with only a few lines of code:
CREATE TABLE posts (title TEXT, content TEXT)
INSERT INTO posts (title, content) VALUES (request.title, request.content)
SELECT title, content FROM posts
Sadly, I stopped working on it after only 1 or 2 weekends because I switched professions to cybersecurity and had too much to learn - every weekend thereafter was CTFs or bug bounties.
Oh well! Someday I'd like to write a more intuitive, "conventional" style language for Urbit, maybe called HoonScript or something. So my affection for Racket's language oriented programming will (maybe, one day) not be in vain!
So just use postgrest.
But, even if they did reinvent half of postgrest, there are plenty of reasons this might be the right choice given more contextual information, tech decisions aren't made in a vacuum, it just might not be possible to use postgrest.
It worked nice, but apart from querying logs, I don’t see a use case where one would do this rather than just creating api endpoints.
ODATA [1] is probably the main one, and it’s adopted by e.g. Microsoft’s Azure APIs. JSON:API [2] is another, although I’ve never seen it used anywhere personally.
They’re both pretty heavy specifications so I’ve never bothered implementing anything that 100% follows them. But I have picked bits and pieces from time to time—in particular ODATA’s idea for filtering is nice and comprehensive imo, and there are a number of libraries in various languages which handle parsing its filter strings into structured data.
My main issue is that joins will be processed locally, so all the foreign data will be fetched before the join happens. But otherwise basic CRUD is easy.
https://wiki.postgresql.org/wiki/Foreign_data_wrappers
Specifically, https://google.aip.dev/assets/misc/ebnf-filtering.txt which is a modification of their common expression language https://github.com/google/cel-spec
Edit: totally forgot to add the actual link. Here goes: https://security.stackexchange.com/questions/229954/why-cant...
The key advantage here being the ability to package this into a library, instead of everyone and their mother rolling their own.
"One of the most common vulnerabilities on web applications is SQL Injection, and you are giving a SQL console to your users. You are giving them a wide variety of guns, lots of bullets, and your foot, your hand, your head... And some of them don't like you."
No, you are not giving your users a wide variety guns and bullets and hands and heads. You DO have some measure of control in the amount of power available to users. I don't consider it comprehensive or balanced not to address that fact in the answer.
- Role Based Access Permissions (RBAC) for course-grained control over database objects (tables, procedures, etc.)
- Row Level Security (RLS) for fine-grained control over data items (e.g. multinenant)
For correctness:
- custom data types
- custom domains
- check constraints
- triggers
- procedures
For loose coupling:
- views
- procedures
For QoS:
- quotas (timeouts, row limits, data limits)
That gets you most of the way there. You would still want a connection pooler and a rate limiter, of course, outside the DB.
[0] Skip the API, Ship Your Database - https://fly.io/blog/skip-the-api/
psql --host=crt.sh --port=5432 --username=guest certwatchJust 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
But faster than you can imagine, the next requirement comes in: OR filters!
bicycles that are (made of steel AND weigh from 10 to 20kg) OR
(made of carbon AND weigh from 5 to 10kg)
In the rare case users do something like this, they usually use two tabs. One, where they compare steel bikes and one where they compare carbon bikes.
It makes sense, when you think about the frontend implications. Users are very used to checkboxes and range sliders. The user would check "steel" and set the range slider to "10-20". Now how do they expect to duplicate the sliders and the checkboxes to construct the alternative configuration? They open the page in a new tab and use the alternative settings in that tab.
However, it's not only about the users as in the visitors of the shop. It can also be a feature such as "show similar bicycles" that will require more complicated queries. And while it's of course possible to maybe run multiple queries and combine the results by code, it can be nice to just do it in one query that is easy to understand and debug/optimize because it's pretty much just SQL.
Additionally, tons of products do support "OR". Take the Amazon search. It has sections that are "OR" inside of them and "AND" across the sections.
I'm not sure exactly what you mean with the Amazon search, but I have the feeling that what you refer to is not the type of user customizable OR that the article used as an example.
Can you link to such an Amazon search with "OR" and "AND"?
If you mean an Amazon search that allows you to explicitly type this in, they don't and, sorry, I was probably not explicit enough in what I am saying.
Now my point is, OR does show up very often in product search, but, it's usually very limited in that it's OR within a category and AND across categories, and there is a pretty standard interface for expressing this.
products below 200 euros and brands speedo or quicksilver
search?brands=speedo,quicksilver&priceMax=200
works.
The point of the article is that this does not work for customizable AND OR constructs, and therefore one should use SQL. And then it gives the bicycles example, stating that this feature request comes up "faster than you can imagine". But no, it almost never comes up in product search. I have not seen it even once in the last 9 years among the thousands of feature requests I looked at.
We don't? Why not?
- Exposing an API that accepts SQL is crazy.
- It's a terrible idea.
- Especially if the API is exposed on the internet.
That isn't reasoning. We don't encounter actual reasoning until:
- Doing that is insecure
- itwill lead to SQL injection attacks
- it is a nightmare to maintain
- it will lock the backend implementation into a specific technology
Even these are highly debatable, so I ask again, why not just expose a SQL API, even over the Internet?
No. I don’t agree at all. Creating a custom query language based on json or lisp or whatever is easy enough, flexible enough, prone to evolution, and helps building an abstraction between the front and the back. With this in mind, the day you decide to query an ElasticSearch instead of the RDBMS, the frontal API doesn’t need to change, there’s juste to update the translation layer to generate an ES query instead of SQL.
Exposing SQL to the API is an awful idea.
1. Access control.
Table and column-level access control. Row-level restrictions by expressions.
Out-of-band table filters that the user cannot override in the query.
2. Query complexity restrictions.
Allow queries only using the index. Restrict the maximum number of records to scan. Limit the max query runtime or maximum memory consumption.
Out-of-band limiting on the result size.
3. Rate limiting.
Limit the number of rows or bytes scanned over a period of time for a user, for an IP address or IP subnet.
4. Interfaces and formats.
CORS headers. HTTP compression. TLS. Proxy protocols.
JSON with metadata, Protobuf, etc.
TLDR. It works with ClickHouse; it is doubtful with other DBMS.
For example, you might have a table "users" but while "admins" can see all users, "moderators" might only be allowed to see the users of their moderated areas.
As soon as this kind of flexibility is needed, solutions like Postgrest stop working - they are just not made for that level of customization.
It's kind of weird designers of GraphQL spent so much effort on cherry picking results but left query part static.
It should be called PickResultLang (PickRL) instead.