You can even expose the database server to the internet and get away with it if you configure the user access control, quotas, and limitations on query complexity.
For example, these services:
https://play.clickhouse.com/play?user=play
work this way by querying ClickHouse directly.
ClickHouse has REST API as a first-class feature.
For example, this query will not start, because the amount of rows to scan is greater than the limit: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT...
This query will fail with the error: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT...
Code: 160. DB::Exception: Estimated query execution time (580.1882010677873 seconds) is too long. Maximum: 60. Estimated rows to process: 9946243959: While executing MergeTreeThread. (TOO_SLOW) (version 22.13.1.294 (official build))Example 1:
SELECT ... FROM table WHERE key BETWEEN c1 AND c2
- here we can get the number of records and bytes to scan before even starting the query.Example 2:
SELECT ... FROM table WHERE key = (SELECT ...)
- here we cannot know how many records will be scanned until we will finish the subquery.Maybe some estimations can be applied... but we don't do that.
One of my related pocket theories for the next ten years: many people keep saying AI is going to eat jobs. Probably not in engineering, at least for a while. But, we're starting to see a new generation of software companies being built on these kinds of truly new-way-of-doing-things technologies, whether that's Postgrest/RLS, functions as a service, or no-code tools like Retool. The productivity and efficiency advantage that these companies will have over the incumbents is incomprehensible.
I think a lot of legacy programmers won't adjust. Its hard to admit to yourself that, honestly, we're spending most of our days doing really freakin similar things as other engineers as other companies; and we as an industry are starting to solve these problems in more and more off the shelf ways.
Which is all to say, the three-fold theory/prediction is: "backend engineering" will experience the least growth, or even highest decline, in job openings of any software engineering specialty over the next ten to fifteen years. The effort involved in it will move left-and-right on the spectrum: higher demand for frontend engineers, and higher demand for DevOps/Cloud/etc. We will see billion dollar software companies selling software as a service with the vast majority of their "backend stack" having no code (potentially some number of serverless functions to plug gaps in no-code capabilities).
SELECT crypt( encode(digest(gen_random_bytes(1024), 'sha512'), 'base64'), gen_salt('bf', 20) )FROM generate_series(1, 1000000)
More explanations here https://www.freecodecamp.org/news/stop-calling-postgrest-mag...
You can see an example here: https://postgrest.org/en/stable/auth.html#roles-for-each-web...
The main downside of RLS is that complex queries may require hand-optimizing.
The proper way to prevent DoS is to implement some kind of resource constraints for database queries.
AFAIK for postgres every connection launches a separate database process. So in theory you can craft some kind of ulimits or cgroup limits which would restrict a process to a limited amount of RAM, CPU or IOPS. So if a given process will eat more RAM, it'll be killed, if it wants to mine bitcoins, it'll be throttled and killed by timeout eventually.
Thinking in absolutes is not very helpful. PostgREST's API is designed to prevent DoS and it is flexible enough for most of your queries and when it isn't you can write a custom function and PostgREST will expose it for you as '/rpc/<my_function>'.
The issue with using it is more around its complexity, and difficulty verifying the constraints without building custom tooling for that. You almost need something like formal methods to model your security model.
But it's an extremely powerful security tool that if you're using postgres at all, is probably worth the effort spent learning and maintaining. And if you're going that far, you already have the skills and tools to run all your auth on it so why not.
You might be interested in what we're building: Seafowl, a database designed for running analytical SQL queries straight from the user's browser, with HTTP CDN-friendly caching [0]. It's a second iteration of the Splitgraph DDN [1] which we built on top of PostgreSQL (Seafowl is much faster for this use case, since it's based on Apache DataFusion + Parquet).
The tradeoff for allowing the client to run any SQL vs a limited API is that PostgREST-style queries have a fairly predictable and low overhead, but aren't as powerful as fully-fledged SQL with aggregations, joins, window functions and CTEs, which have their uses in interactive dashboards to reduce the amount of data that has to be processed on the client.
There's also ROAPI [2] which is a read-only SQL API that you can deploy in front of a database / other data source (though in case of using databases as a data source, it's only for tables that fit in memory).
Looking at the last project I did, the app-level db-middleware basically consists of two things: (1) plumbing boilerplate; (2) enforces app-level auth rules, either by preventing unauthorized operations or adding required filters. It looks like I should probably look at RLE and can likely move anything app-specific out of the middleware... which might let it collapse to something entirely generic, and therefore entirely reusable, and quite possibly something I don't have to write myself.
Sanitize your inputs. I'll let little bobby tables link to the XKCD
I'm just suggesting to not invent another query language, especially where a rather obvious and natural one already exists. You can and should sanitize untrusted input regardless.
If you're using the postgrest language your options to stabilise the interface are minimal anyway, so why not just go with SQL and a whitelist?
I am not sure if I would do this on something serious, but it was a lot of fun to set up and saved me a lot of code by reusing the comprehensive auth system that lives with the actual data in question.
It's been a while but does postgres let you re-auth on an existing connection? I was able to do that in Oracle and that allowed the use of connection pools rather than one connection per user, which can become a bottleneck.
I wish. Unfortunately, sometimes a stateless HTTP REST API is simply a non-negotiable business requirement.
Metabase works well is such scenarios.
Maybe pick a language that has better libraries for language parsing and analysis?
But...
I'm leery of basing the auth on postgres' auth. It just doesn't seem up to expressing auth rules based on dynamic, app-specific business rules. (Maybe I just don't know postgres' auth mechanism well enough, though.)
Also, it's perhaps overly complicated in pointless ways. A lot of this is mapping HTTP requests to SQL... why not just accept SQL and cut out all the unnecessary mapping? E.g. (real encoding not show for clarity)
GET /theapi?sql=SELECT name, age FROM Person WHERE age >= :1&p1=21
It seems crazy to me that it defines an entire HTTP-based query language when SQL is right there to be used directly. Going to SQL directly makes the API far simpler and more powerful.