3 ms·

> 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

> 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

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