4 ms·

> In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. To be fair, SQL injection at

by Twisol 5y ago

> In a matter of hours, commenters were able to dump sensitive data (passwords and all) by constructing specially crafted queries. To be fair, SQL injection attacks have been a common problem for a long time, and most websites did not expose their database directly like this. It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. But as far as I can tell, for me, right now, it's just that: a feeling. I'd love to have a robust analysis of the access controls Postgres provides, and more importantly, how many mistakes (relative and absolute) need to be made for a breach. I'd like to know whether the security story here is fundamentally brittle, or whether it's practically viable and just needs better support for doing the "right" thing. Personally, I like the architectural philosophy a lot. We saw a project on HN yesterday [0] that takes a similar approach. I'm oversimplifying, but I like that instead of trying to figure out what series of API calls will put the system in the state you want, you can simply put it in the state you want. It's easier for me to look at a schema/type and understand what it's supposed to mean than to look at a piece of code and figure out what it's supposed to do. [0] https://news.ycombinator.com/item?id=29375911 https://news.ycombinator.com/item?id=29375911

michelpp 5y ago
> It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. There are definitely fewer buffered copies of your responses, yes. This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. This security mechanism doesn't just apply to your web frontend either, but to all SQL clients regardless of the frontend language. No other framework has such a deep integration with Postres' native security mechanism.
- Twisol 5y ago
  > As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. Maybe you're referring to the underlying concept rather than the implementation, but wasn't row-level security added to Postgres in 2016? https://www.postgresql.org/docs/9.5/release-9-5.html https://www.postgresql.org/docs/9.5/release-9-5.html > This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. This is one big reason why I like this kind of architecture, though I'd generalize it a bit. More than streaming, you don't have to reimplement access control mechanisms (as noted), subscription mechanisms (AWAIT/NOTIFY), or batching/transactions in your API layer. So much of what you do in an API server is just proxying for functionality that already exists in the DB, and that alone is a reason to seriously consider this approach.
  - michelpp 5y ago
    > Maybe you're referring to the underlying concept rather than the implementation, but wasn't row-level security added to Postgres in 2016? Yes you are correct my statement was too vague, I was referring to Postgres' role based security in general at that point. RLS specifically was added in 9.5. Before that permission checking was table/column based, so you could still protect data on a per-role basis, just not as fine a grain. Another subtle difference is that RLS hides rows you can't see, where as a REVOKE on a table or column would cause an error if you tried to access it. In either case unauthorized users cannot see protected data.
- mjay23 5y ago
  Postgrest does not stream rows, it waits for the db with the full response then forwards it to the browser/client

by Twisol 5y ago

michelpp 5y ago

> It certainly feels like PostgREST and systems like it are dancing close to the fire here, since there's a lot less in between you and an attacker. There are definitely fewer buffered copies of your responses, yes. This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. This security mechanism doesn't just apply to your web frontend either, but to all SQL clients regardless of the frontend language. No other framework has such a deep integration with Postres' native security mechanism.

Twisol 5y ago
> As for security, Postgres supports Row Level Security. You can literally protect every single row of your data with a solid security policy using a security mechanism older and tested longer than the average age of your typical HN reader. Maybe you're referring to the underlying concept rather than the implementation, but wasn't row-level security added to Postgres in 2016? https://www.postgresql.org/docs/9.5/release-9-5.html https://www.postgresql.org/docs/9.5/release-9-5.html > This is one advantage few people know about PostgREST, once the database produces even a single row of data, it starts to stream JSON to the frontend. Users don't have to wait for the response to fully buffer in memory in some Python or Go process before being relayed on. This is one big reason why I like this kind of architecture, though I'd generalize it a bit. More than streaming, you don't have to reimplement access control mechanisms (as noted), subscription mechanisms (AWAIT/NOTIFY), or batching/transactions in your API layer. So much of what you do in an API server is just proxying for functionality that already exists in the DB, and that alone is a reason to seriously consider this approach.
- michelpp 5y ago
  > Maybe you're referring to the underlying concept rather than the implementation, but wasn't row-level security added to Postgres in 2016? Yes you are correct my statement was too vague, I was referring to Postgres' role based security in general at that point. RLS specifically was added in 9.5. Before that permission checking was table/column based, so you could still protect data on a per-role basis, just not as fine a grain. Another subtle difference is that RLS hides rows you can't see, where as a REVOKE on a table or column would cause an error if you tried to access it. In either case unauthorized users cannot see protected data.
mjay23 5y ago
Postgrest does not stream rows, it waits for the db with the full response then forwards it to the browser/client