4 ms·

PostgREST Tutorial: APIs made easy

27 points by radimm 2y ago

wg0 2y ago
If a web socket based posters driver is available, why not use that directly?
- afavour 2y ago
  It’s very rare that you want a web client to maintain an open connection for a long time. It would be a scaling nightmare. I’m also trying to imagine validation. Parsing SQL error strings? Kill me now.
- slashdev 2y ago
  You don't want to give the internet at large unfettered access to your database. Even if you have row-based security.
  - zX41ZdbW 2y ago
    I often do it with ClickHouse, which has HTTP API included in the database server. For example, https://play.clickhouse.com/ https://play.clickhouse.com/ is an open public playground, and https://adsb.exposed/ https://adsb.exposed/ is a full-featured application with HTML+ClickHouse and no dedicated backend.
- bzzzt 2y ago
  PostgreSQL connections don't scale up as well as some other databases. Every connection gets a huge chunk of server memory so you'll run out of memory very fast.
xrd 2y ago
I'm interested in this because auth(z/n) is front and center. But I'm in love with Pocketbase because it's amazing and though it doesn't have row level security it definitely has an amazing integrated rules system that is almost as good and arguably more flexible. Anyone used both and can offer a comparison?
deleted 2y ago
[deleted]
ufmace 2y ago
The idea of PostgREST is cool, but I keep coming back to it not being very practical for most purposes. It's slick when the default query API does everything you need. But it seems inevitable that you'll eventually need to do something it doesn't support well. Then you're building views and stored procedures. Okay, those can work, but version control, unit testing, composeability, etc just aren't at the level of any mainstream web framework. Not to mention logging, tracing, etc. The complexity is going to get unmanageable fast, and then what? And the auth. It probably works okay with a few dozen users who are all trusted. Would you trust the Postgres auth system with tens of thousands of users, many of who are internet randos who may be malicious? It feels like a recipe for disaster. But then if you're only supporting trusted users doing basic stuff, why not let them use a regular DB client rather than this API?
- billllll 2y ago
  If you need to built quick, you can get all of this configured out-of-the-box with something like Supabase. If I had to set up all the roles and security myself, I don't know if I'd use it. The auth is workable - you can authn/z individual users, which is basically what you need with a real backend. Personally though, I think doing auth declaratively is a bit harder than doing it imperatively in code. If you're just 1 or 2 devs on a fledgling product, I would dare say all your concerns about testing/version control/composability are totally worth it to be able to build fast (and some of it is mitigated with Supabase's setup anyways). Personally, I think the idea is to eventually migrate to a true backend, without requiring any changes to the schema, and hopefully avoid lock-in.
  - ufmace 2y ago
    I don't know much about Supabase honestly. On concerns about testing etc. I can see letting automated testing, keeping a clean version control history, good architecture, etc slide at an early stage in the name of building fast. To me, that looks like building stuff in a more mainstream language and framework, but letting yourself be a little bit sloppy about those things. The difference is, the tools you are using don't actively preclude those things, so you keep the option open of adding them in later as needed, as gradually as makes sense. If you're building in one of these limited but all encompassing frameworks like PostgREST, sure you can probably go fast at first, as long as you're coloring within their lines, but as soon as you need something more or need to add some of that other stuff in, you hit a wall, and your only option is to do basically a complete rewrite.
billllll 2y ago
Am I missing something or is step 3 missing some steps to validate the JWT and define the current_user_id() function? Taking a look at the docs here: https://postgrest.org/en/v12/references/auth.html https://postgrest.org/en/v12/references/auth.html https://postgrest.org/en/v12/explanations/db_authz.html https://postgrest.org/en/v12/explanations/db_authz.html It doesn't seem like current_user_id() is a provided function, and the docs claim nothing else is done with the JWT except validating it. It looks like your claim already includes user_id, so you'd have to get it from the claim using: current_setting('request.jwt.claims', true)::json->>'user_id'; Not sure if I'm missing something.
- cryptonector 2y ago
  I really didn't understand what the tutorial is doing with JWT. BTW, PostgREST supports JWT for authentication, so there's nothing to do here unless this application is a sort of JWT issuer (but I really didn't get that sense at all).
  - radimm 2y ago
    Yes, application issues own JWT tokens - specifically in `public.create_jwt`
- radimm 2y ago
  Author here - you are absolutely correct. Damn, during the last proof-reading one function got lost. Will fix it ASAP

27 points by radimm 2y ago

wg0 2y ago

If a web socket based posters driver is available, why not use that directly?

afavour 2y ago
It’s very rare that you want a web client to maintain an open connection for a long time. It would be a scaling nightmare. I’m also trying to imagine validation. Parsing SQL error strings? Kill me now.
slashdev 2y ago
You don't want to give the internet at large unfettered access to your database. Even if you have row-based security.
- zX41ZdbW 2y ago
  I often do it with ClickHouse, which has HTTP API included in the database server. For example, https://play.clickhouse.com/ https://play.clickhouse.com/ is an open public playground, and https://adsb.exposed/ https://adsb.exposed/ is a full-featured application with HTML+ClickHouse and no dedicated backend.
bzzzt 2y ago
PostgreSQL connections don't scale up as well as some other databases. Every connection gets a huge chunk of server memory so you'll run out of memory very fast.

xrd 2y ago

I'm interested in this because auth(z/n) is front and center. But I'm in love with Pocketbase because it's amazing and though it doesn't have row level security it definitely has an amazing integrated rules system that is almost as good and arguably more flexible. Anyone used both and can offer a comparison?

deleted 2y ago

[deleted]

ufmace 2y ago

The idea of PostgREST is cool, but I keep coming back to it not being very practical for most purposes. It's slick when the default query API does everything you need. But it seems inevitable that you'll eventually need to do something it doesn't support well. Then you're building views and stored procedures. Okay, those can work, but version control, unit testing, composeability, etc just aren't at the level of any mainstream web framework. Not to mention logging, tracing, etc. The complexity is going to get unmanageable fast, and then what? And the auth. It probably works okay with a few dozen users who are all trusted. Would you trust the Postgres auth system with tens of thousands of users, many of who are internet randos who may be malicious? It feels like a recipe for disaster. But then if you're only supporting trusted users doing basic stuff, why not let them use a regular DB client rather than this API?

billllll 2y ago
If you need to built quick, you can get all of this configured out-of-the-box with something like Supabase. If I had to set up all the roles and security myself, I don't know if I'd use it. The auth is workable - you can authn/z individual users, which is basically what you need with a real backend. Personally though, I think doing auth declaratively is a bit harder than doing it imperatively in code. If you're just 1 or 2 devs on a fledgling product, I would dare say all your concerns about testing/version control/composability are totally worth it to be able to build fast (and some of it is mitigated with Supabase's setup anyways). Personally, I think the idea is to eventually migrate to a true backend, without requiring any changes to the schema, and hopefully avoid lock-in.
- ufmace 2y ago
  I don't know much about Supabase honestly. On concerns about testing etc. I can see letting automated testing, keeping a clean version control history, good architecture, etc slide at an early stage in the name of building fast. To me, that looks like building stuff in a more mainstream language and framework, but letting yourself be a little bit sloppy about those things. The difference is, the tools you are using don't actively preclude those things, so you keep the option open of adding them in later as needed, as gradually as makes sense. If you're building in one of these limited but all encompassing frameworks like PostgREST, sure you can probably go fast at first, as long as you're coloring within their lines, but as soon as you need something more or need to add some of that other stuff in, you hit a wall, and your only option is to do basically a complete rewrite.

billllll 2y ago

Am I missing something or is step 3 missing some steps to validate the JWT and define the current_user_id() function? Taking a look at the docs here: https://postgrest.org/en/v12/references/auth.html https://postgrest.org/en/v12/references/auth.html https://postgrest.org/en/v12/explanations/db_authz.html https://postgrest.org/en/v12/explanations/db_authz.html It doesn't seem like current_user_id() is a provided function, and the docs claim nothing else is done with the JWT except validating it. It looks like your claim already includes user_id, so you'd have to get it from the claim using: current_setting('request.jwt.claims', true)::json->>'user_id'; Not sure if I'm missing something.

cryptonector 2y ago
I really didn't understand what the tutorial is doing with JWT. BTW, PostgREST supports JWT for authentication, so there's nothing to do here unless this application is a sort of JWT issuer (but I really didn't get that sense at all).
- radimm 2y ago
  Yes, application issues own JWT tokens - specifically in `public.create_jwt`
radimm 2y ago
Author here - you are absolutely correct. Damn, during the last proof-reading one function got lost. Will fix it ASAP