PostgREST: REST API for any Postgres database

Re:PostgREST: REST API for any Postgres database by sopooneo on Saturday November 21, 2020 @10:05PM (#25174599)

I am clearly missing something, but at that point, why not just allow the client (presumably a web app running in a browser in most cases) to just make requests via SQL? You would of course want to add authentication/authorization layers. You’re want record and field level restriction. But if the entirety of you data is in an RDMS, why put this “REST” paradigm in the middle? Why not just admit you’re querying a database, and query the database? If the only reason is access control, it seems a simpler solution must be possible.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by dragonwriter on Saturday November 21, 2020 @10:34PM (#25174738)

> I am clearly missing something, but at that point, why not just allow the client (presumably a web app running in a browser in most cases) to just make requests via SQL?

Because REST-over-HTTP is low impedance with browser-based web apps, whereas SQL is...not.

Plus, with REST, you abstract whether all, some, or none of your data is an RDBMS; the fact that you've implemented something with PostgREST doesn't mean everything related to it and linked from it is implemented the same way.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by piaste on Sunday November 22, 2020 @06:15AM (#25176588)

It's difficult to grant an untrusted client a level of raw SQL access that won't let them do harm.

For example, even without access to any specific table or function, even with rate limits, I can denial-of-service the server by asking it to make a massive recursive pure computation.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by tiew9Vii on Saturday November 21, 2020 @10:29PM (#25174716)

I often thought the same with the use of GraphQL. Instead of building a highly curated API with business logic, optimisations and other general stuff you are building a direct interface around how your data looks in the db, a 1:1 mapping and crud interface. In software we are taught to prefer loose coupling vs tight coupling yet here people are preferring the tight coupling.

I’m not a fan of this as the user interface (API) has a tight coupling with how you store your data. Then like you say, why not just speak SQL as you have all the same issues, essentially multiple clients writing to/owning the same tables.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by taffer on Sunday November 22, 2020 @02:25PM (#25180227)

The PostgREST documentation makes it pretty clear that for decoupling and security reasons the data schema should be separated from the API schema. You should only provide the client with views and functions and keep all data in a separate schema. This also solves the versioning problem, because if your API schema is separate from your data, you can simply drop and re-create the entire schema for deployment without needing migrations.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by tehlike on Saturday November 21, 2020 @10:15PM (#25174646)

graphql kind of does that.

SQL is very complex, T-SQL is turing complete, meaning you can do lots of damage. you can bring servers to a halt if unchecked. It's pretty hard to restrict what can be done keeping flexibility.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by ruslan_talpa on Sunday November 22, 2020 @01:43AM (#25175496)

This is the “corect” response to the question above. :)

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by CuriouslyC on Sunday November 22, 2020 @10:56AM (#25178440)

Postgres isn't 100% set up to let fully untrusted clients do stuff. For example, right now there is no access control around listen/notify. You'd have to do some baseline query pre-processing to be completely secure, which could be a non-trivial task depending on just what you want to let clients do.

I've done it in read-only internal business apps though, it's great.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by xwdv on Saturday November 21, 2020 @10:26PM (#25174699)

Most web applications are basically just interfaces to a database. Why even have “REST” at all?

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by jaikant77 on Sunday November 22, 2020 @02:10AM (#25175610)

You got me thinking. GET/PUT/POST is not really needed for a completely server rendered app.

[ Parent | on HN ]

Re:PostgREST: REST API for any Postgres database by ntr-- on Sunday November 22, 2020 @06:59PM (#25182240)

I think we will see more of a push towards SSR technologies like LiveView for exactly this reason:

https://hexdocs.pm/phoenix_live_view/Phoenix.LiveView.html

[ Parent | on HN ]