6 ms·

This is great in a lot of ways... A robust and far more comprehensive version of something I do in db-based apps. But... I'm leery of basing the auth on postg

by jmull 4y ago

This is great in a lot of ways... A robust and far more comprehensive version of something I do in db-based apps. 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.

deleted 4y ago
[deleted]
jeff-davis 4y ago
If someone can write their own SQL, it would be trivial to DoS the system. My guess is that the sublanguage is meant to prevent that (but it's just a guess).
- vbezhenar 4y ago
  This sublanguage will either be useless or will not prevent DoS. 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.
  - taffer 4y ago
    > This sublanguage will either be useless or will not prevent DoS. 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>'.
- jmull 4y ago
  I think that's separate from the language though. I'm not saying it would process any SQL... it can be restricted to whatever function and SQL patterns that are deemed acceptable. Realistically, though, your language is going to be seriously inflexible or allow DoS... In fact I think you're typically more likely to hit seriously inflexible before excluding the possibility of DoS. So I think you're going to need something besides the query language to prevent DoS anyway.
smallnamespace 4y ago
PostgreSQL row-level security (which PostgREST defers to) is very flexible, more or less anything expressible as a SQL query can be used to filter what the user can see. You can see an example here: https://postgrest.org/en/stable/auth.html#roles-for-each-web-user https://postgrest.org/en/stable/auth.html#roles-for-each-web... The main downside of RLS is that complex queries may require hand-optimizing.
ruslan_talpa 4y ago
Because queries like this will kill any db, and you don’t need any privileges 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-magic-8f3e1d5e5dd1/amp/ https://www.freecodecamp.org/news/stop-calling-postgrest-mag...
- RhodesianHunter 4y ago
  Wouldn't it be much easier to disable/restrict certain sql functions than do all of the http->sql mapping?
  - 411111111111111 4y ago
    No, as there are countless ways to do the same. Whitelisting is more effective if your code is running on untrusted devices (frontend)
- miga 4y ago
  Postgres can limit queries to only given number of milliseconds.
  - ruslan_talpa 4y ago
    Give me access to your PG with a user that has no privileges, can not run queries longer then X ms and see how easy it would be to DOS the db :)
giraffe_lady 4y ago
Postgres's row level security is absolutely sophisticated enough to model arbitrary business security rules, probably with more precision than almost any other auth system in widespread use. 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.
edmundsauto 4y ago
I started using RLS in the Supabase offering and am now a total fan of it. The supabase implementation, in particular, allows you to expose the API to javascript which is cool! When you use RLS that way, users get their own tokens to pass in the session which limits the records in the db they have access to. So, if you had a token stolen - the db would limit access to just that account.
- 015a 4y ago
  I'll second this; Supabase obviously popularized this whole method of doing things, but once you've worked in it it genuinely feels like the first step forward this industry has had in five years. 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).
  - edmundsauto 4y ago
    I totally agree that it feels like the right way, at least for my values. However, I struggle to apply modern software practices. Am currently struggling with writing a function that is triggered by an insert, said function needs to join the new row to another table. Without a good debugging/iteration environment, it's going to be another example of a great product that fails b/c of bad dev experience.
zX41ZdbW 4y ago
We do it exactly this way in ClickHouse, and it works perfectly. 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 https://play.clickhouse.com/play?user=play https://ghe.clickhouse.tech/ https://ghe.clickhouse.tech/ https://pastila.nl/ https://pastila.nl/ https://aretestsgreenyet.com/ https://aretestsgreenyet.com/ work this way by querying ClickHouse directly. ClickHouse has REST API as a first-class feature.
- yladiz 4y ago
  How do you determine query complexity? Do you go off of the query plan or something like looking at the columns, joins, etc.?
  - zX41ZdbW 4y ago
    There are only a few ways to determine it before running the query - by the amount of rows/bytes to scan. But most of the logic happens when query is already started - by the possibility to stop it and throw exception if some metrics became too large. 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#U0VMRUNUICogRlJPTSB3aWtpc3RhdA== https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT... This query will fail with the error: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPTSByZWRkaXQgV0hFUkUgYm9keSBJTElLRSAnJUNsaWNrSG91c2UlJw== 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))
    - yladiz 4y ago
      I see. How do you determine when to kill the query before running it then? Of course you could do “if the process is running longer than X seconds kill and returns NNN status code” but that feels somewhat wasteful if you can be smarter when calculating the possible cost.
mildbyte 4y ago
> why not just accept SQL and cut out all the unnecessary mapping? 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). [0] https://seafowl.io/ https://seafowl.io/ [1] https://www.splitgraph.com/connect https://www.splitgraph.com/connect [2] https://github.com/roapi/roapi https://github.com/roapi/roapi
jmull 4y ago
(Replying to own post)... thanks for the info about Postgres RLS/row-level security. I guess I should not be so leery... 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.
aljarry 4y ago
Why not just accept SQL? It's a matter of contract. With REST APIs you specify a contract, it gets robust with time, and other software can expect only what's in the contract. SQL allows you to run arbitrary functions over data in the database, so the whole schema and parts of the content in a way become your contract - and your options to modify the schema (or how you use the database) get very constrained.
- dragonwriter 4y ago
  You can limit the visible schema, and thus the contract, per role with SQL.
  - aljarry 4y ago
    That's a very good point!
- officialchicken 4y ago
  >Why not just accept SQL? Sanitize your inputs. I'll let little bobby tables link to the XKCD
  - jmull 4y ago
    Who says don't sanitize inputs? 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.
- lelanthran 4y ago
  Yeah, but this particular contract/API is pretty much "run arbitrary select/insert/update/delete statements" anyway! 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?
  - aljarry 4y ago
    Yes, but the REST vs SQL forces a specific API for changes. REST - update/delete a specific record. SQL - update/delete based on a filter, or a query result. I can do `set a = b` for all rows in the table, which may be needed internally, but it's a pretty bad external interface.
somat 4y ago
I did the oppisite, I needed auth in a toy application I was writing and was dreading writing and getting correct the whole auth layer, so I thought to myself "self, postgres has a very rich and through auth system, why not just use that" So I did just that, I just pass the user credentials straight through to to the database connection and let the database decide what access the user gets to the data. 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.
- SoftTalker 4y ago
  The nice thing about this approach is that if you ever develop other applications or front-ends to this database, even in some other framework, the user auth and access rules are already there. 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.
  - timando 4y ago
    You can use SET ROLE to become another user you have permission to become (I think you need to be in the same group or something) and RESET ROLE to go back to your original user. If the initial connection was made with a superuser or whatever, you could change to the user in the connection manager and reset when the connection goes back in the pool.
dventimihasura 4y ago
> why not just accept SQL I wish. Unfortunately, sometimes a stateless HTTP REST API is simply a non-negotiable business requirement.
- miga 4y ago
  SQL `SELECT`-only API is a stateless API.
machiaweliczny 4y ago
I tried to do it but couldn't find open source MIT licensed SQL parser which is needed to make it secure against SQL injection. Metabase works well is such scenarios.
- miga 4y ago
  Haskell repositories have SQL parsers aplenty, some BSD3, at least one MIT-licensed: https://hackage.haskell.org/package/queryparser https://hackage.haskell.org/package/queryparser Maybe pick a language that has better libraries for language parsing and analysis?

by jmull 4y ago

deleted 4y ago

[deleted]

jeff-davis 4y ago

If someone can write their own SQL, it would be trivial to DoS the system. My guess is that the sublanguage is meant to prevent that (but it's just a guess).

vbezhenar 4y ago
This sublanguage will either be useless or will not prevent DoS. 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.
- taffer 4y ago
  > This sublanguage will either be useless or will not prevent DoS. 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>'.
jmull 4y ago
I think that's separate from the language though. I'm not saying it would process any SQL... it can be restricted to whatever function and SQL patterns that are deemed acceptable. Realistically, though, your language is going to be seriously inflexible or allow DoS... In fact I think you're typically more likely to hit seriously inflexible before excluding the possibility of DoS. So I think you're going to need something besides the query language to prevent DoS anyway.

smallnamespace 4y ago

PostgreSQL row-level security (which PostgREST defers to) is very flexible, more or less anything expressible as a SQL query can be used to filter what the user can see. You can see an example here: https://postgrest.org/en/stable/auth.html#roles-for-each-web-user https://postgrest.org/en/stable/auth.html#roles-for-each-web... The main downside of RLS is that complex queries may require hand-optimizing.

ruslan_talpa 4y ago

Because queries like this will kill any db, and you don’t need any privileges 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-magic-8f3e1d5e5dd1/amp/ https://www.freecodecamp.org/news/stop-calling-postgrest-mag...

RhodesianHunter 4y ago
Wouldn't it be much easier to disable/restrict certain sql functions than do all of the http->sql mapping?
- 411111111111111 4y ago
  No, as there are countless ways to do the same. Whitelisting is more effective if your code is running on untrusted devices (frontend)
miga 4y ago
Postgres can limit queries to only given number of milliseconds.
- ruslan_talpa 4y ago
  Give me access to your PG with a user that has no privileges, can not run queries longer then X ms and see how easy it would be to DOS the db :)

giraffe_lady 4y ago

Postgres's row level security is absolutely sophisticated enough to model arbitrary business security rules, probably with more precision than almost any other auth system in widespread use. 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.

edmundsauto 4y ago

I started using RLS in the Supabase offering and am now a total fan of it. The supabase implementation, in particular, allows you to expose the API to javascript which is cool! When you use RLS that way, users get their own tokens to pass in the session which limits the records in the db they have access to. So, if you had a token stolen - the db would limit access to just that account.

015a 4y ago
I'll second this; Supabase obviously popularized this whole method of doing things, but once you've worked in it it genuinely feels like the first step forward this industry has had in five years. 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).
- edmundsauto 4y ago
  I totally agree that it feels like the right way, at least for my values. However, I struggle to apply modern software practices. Am currently struggling with writing a function that is triggered by an insert, said function needs to join the new row to another table. Without a good debugging/iteration environment, it's going to be another example of a great product that fails b/c of bad dev experience.

zX41ZdbW 4y ago

We do it exactly this way in ClickHouse, and it works perfectly. 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 https://play.clickhouse.com/play?user=play https://ghe.clickhouse.tech/ https://ghe.clickhouse.tech/ https://pastila.nl/ https://pastila.nl/ https://aretestsgreenyet.com/ https://aretestsgreenyet.com/ work this way by querying ClickHouse directly. ClickHouse has REST API as a first-class feature.

yladiz 4y ago
How do you determine query complexity? Do you go off of the query plan or something like looking at the columns, joins, etc.?
- zX41ZdbW 4y ago
  There are only a few ways to determine it before running the query - by the amount of rows/bytes to scan. But most of the logic happens when query is already started - by the possibility to stop it and throw exception if some metrics became too large. 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#U0VMRUNUICogRlJPTSB3aWtpc3RhdA== https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPT... This query will fail with the error: https://play.clickhouse.com/play?user=play#U0VMRUNUICogRlJPTSByZWRkaXQgV0hFUkUgYm9keSBJTElLRSAnJUNsaWNrSG91c2UlJw== 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))
  - yladiz 4y ago
    I see. How do you determine when to kill the query before running it then? Of course you could do “if the process is running longer than X seconds kill and returns NNN status code” but that feels somewhat wasteful if you can be smarter when calculating the possible cost.

mildbyte 4y ago

> why not just accept SQL and cut out all the unnecessary mapping? 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). [0] https://seafowl.io/ https://seafowl.io/ [1] https://www.splitgraph.com/connect https://www.splitgraph.com/connect [2] https://github.com/roapi/roapi https://github.com/roapi/roapi

jmull 4y ago

(Replying to own post)... thanks for the info about Postgres RLS/row-level security. I guess I should not be so leery... 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.

aljarry 4y ago

Why not just accept SQL? It's a matter of contract. With REST APIs you specify a contract, it gets robust with time, and other software can expect only what's in the contract. SQL allows you to run arbitrary functions over data in the database, so the whole schema and parts of the content in a way become your contract - and your options to modify the schema (or how you use the database) get very constrained.

dragonwriter 4y ago
You can limit the visible schema, and thus the contract, per role with SQL.
- aljarry 4y ago
  That's a very good point!
officialchicken 4y ago
>Why not just accept SQL? Sanitize your inputs. I'll let little bobby tables link to the XKCD
- jmull 4y ago
  Who says don't sanitize inputs? 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.
lelanthran 4y ago
Yeah, but this particular contract/API is pretty much "run arbitrary select/insert/update/delete statements" anyway! 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?
- aljarry 4y ago
  Yes, but the REST vs SQL forces a specific API for changes. REST - update/delete a specific record. SQL - update/delete based on a filter, or a query result. I can do `set a = b` for all rows in the table, which may be needed internally, but it's a pretty bad external interface.

somat 4y ago

I did the oppisite, I needed auth in a toy application I was writing and was dreading writing and getting correct the whole auth layer, so I thought to myself "self, postgres has a very rich and through auth system, why not just use that" So I did just that, I just pass the user credentials straight through to to the database connection and let the database decide what access the user gets to the data. 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.

SoftTalker 4y ago
The nice thing about this approach is that if you ever develop other applications or front-ends to this database, even in some other framework, the user auth and access rules are already there. 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.
- timando 4y ago
  You can use SET ROLE to become another user you have permission to become (I think you need to be in the same group or something) and RESET ROLE to go back to your original user. If the initial connection was made with a superuser or whatever, you could change to the user in the connection manager and reset when the connection goes back in the pool.

dventimihasura 4y ago

> why not just accept SQL I wish. Unfortunately, sometimes a stateless HTTP REST API is simply a non-negotiable business requirement.

miga 4y ago
SQL `SELECT`-only API is a stateless API.

machiaweliczny 4y ago

I tried to do it but couldn't find open source MIT licensed SQL parser which is needed to make it secure against SQL injection. Metabase works well is such scenarios.

miga 4y ago
Haskell repositories have SQL parsers aplenty, some BSD3, at least one MIT-licensed: https://hackage.haskell.org/package/queryparser https://hackage.haskell.org/package/queryparser Maybe pick a language that has better libraries for language parsing and analysis?