4 ms·

I'm currently in the process of evaluating PostgREST via Supabase for replacing the CRUD aspects of our legacy PHP/Laravel code base. It's a compelling option,

I'm currently in the process of evaluating PostgREST via Supabase for replacing the CRUD aspects of our legacy PHP/Laravel code base. It's a compelling option, but there are already a lot of sharp edges. For example, PostgREST doesn't really highlight this, but for any non-trivial and sane application you have to create a separate schema ("api" or similar) to carefully pick what's exposed. PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. To help mitigate this, I'm evaluating only using PostgREST for reads in the "api" schema via access-restricted views, and having all writes go through supabase edge functions. This should simplify the RLS permissions (hopefully). RLS has some pitfalls too, and it's the only mechanism you have to secure your data. Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. In general, you want to treat your DB as a precious resource and minimize the amount of work it has to do. Nginx and similar are built and optimized for serving assets. Using your database to do this doesn't seem like a great idea if your application needs to scale.

steve-chavez 3y ago
> PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. PostgREST follows Postgres' "deny by default", you have to explicitly grant permissions for tables and views to be used. This is noted on the first tutorial[0]. Supabase overrides this default via `ALTER DEFAULT PRIVILEGES .. GRANT`[1]. This is done for easier onboarding of new users but you can turn this off with `ALTER DEFAULT PRIVILEGES .. REVOKE`. PostgREST also encourages you to create a dedicated schema for your api[2]. [0]: https://postgrest.org/en/stable/tutorials/tut0.html#step-4-create-database-for-api https://postgrest.org/en/stable/tutorials/tut0.html#step-4-c... [1]: See an example of this on https://supabase.com/docs/guides/api/using-custom-schemas https://supabase.com/docs/guides/api/using-custom-schemas. [2]: https://postgrest.org/en/stable/explanations/schema_isolation.html https://postgrest.org/en/stable/explanations/schema_isolatio...
- boomskats 3y ago
  > Supabase overrides this default Ah, thank you for this clarification. I was wondering what that comment was referring to. (and keep up the great work <3)
- claytongulick 3y ago
  Thanks for the additional info! Yep, I'm aware of the ability to revoke permissions, it's the design choice of "allow by default" that worries me - but I agree this is a Supabase thing, not PostgREST. On a new project, if you look at the dashboard (pointing at an existing db) access to all tables and data is on by default, you have to explicitly deny access to those tables. That's a concerning design choice for my industry (healthcare). > PostgREST also encourages you to create a dedicated schema for your api[2]. Yes, it does - but this isn't really called out strongly by Supabase (not PostgREST's fault). As some other commenters mentioned, it's not a big deal to create views in the api schema, and I agree. The part I'm still evaluating is whether there will be actual time savings and efficiency vs just creating CRUD endpoints in NodeJS (for example). The big "win" for PostgREST here is the ability to do deep querying and result shaping directly from the UI without needing to create a bunch of custom endpoints. If I have to create views anyway, it seems like I lose a lot of the benefits of using PostgREST in the first place, and add a layer of complication to the stack. I like PostgREST a lot, and I've been hoping that Supabase would be an effective Directus replacement (now that Directus has gone closed source), but I'm still figuring out whether the juice is worth the squeeze on my project. Would love any insight you or others have from using it in production with a medium-scale sized app.
bsdpufferfish 3y ago
> you have to create a separate schema ("api" or similar) to carefully pick what's exposed. Yep, but it's a CREATE VIEW instead of writing a route in python or ruby which also will likely hit an ORM... > Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. In general, you want to treat your DB as a precious resource and minimize the amount of work it has to do. The database should still be the source of truth (of generated assets). An easy solution is to configure NGINX to cache those asset requests, or write a script to unpack them to the file system.
boomskats 3y ago
Just to address some of your points: > PostgREST doesn't really highlight this, but for any non-trivial and sane application you have to create a separate schema ("api" or similar) to carefully pick what's exposed. That recommendation is highlighted here: https://postgrest.org/en/stable/explanations/schema_isolation.html https://postgrest.org/en/stable/explanations/schema_isolatio... > PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. I assume you're talking about access via the db-anon, without authenticating your users. I can't think of what else you could be referring to. If you rely on JWT-based authentication, as any non-trivial and sane application would do, you'd have access to transaction-scoped impersonated roles. https://postgrest.org/en/stable/references/auth.html#overview-of-role-system https://postgrest.org/en/stable/references/auth.html#overvie... > having all writes go through supabase edge functions Why is this necessary, what are you unable to achieve with JWT + RLS/RBA? Again, I have to assume you don't have auth configured correctly. > RLS has some pitfalls too, and it's the only mechanism you have to secure your data. You also have role-based access, and the JWT auth to start with. However I have found RLS to be flexible enough, especially as you can define policies using subqueries into which you can embed UDFs. What are you trying to do that you can't do with it? > Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. I instinctively agree in terms of devex, although I'm reluctant to just dismiss it. I think the performance hit with a database is the data retrieval, not the templating. I can see a possibility of this evolving into something very usable with some packaging of templates, especially once you realise the approach eliminates the need for more services, simplifies auth, and reduces rendering latency. The memory footprint of PostgREST is trivial compared to most middleware you'd have to add to the mix just to do some SSR.
- SOLAR_FIELDS 3y ago
  Supabase folks seem to agree regarding serving assets since they ship an object store for that kind of stuff in their constellation of apps rather than attempting to instead serve blobs out of the db. Hearkening back to an age long ago of having to deal with stuff like images and pdfs stored in db rows and the annoyances around that, I’m also inclined to agree, but less from the performance angle and more from the usability/compatibility angle of being forced to deal with the nuances of BLOB datatypes
cgio 3y ago
Using views is a good practice anyway as you do not tightly couple your API to your schema. It's been a few years since I used Postgrest (and to be fair I had no write scenarios to cover) but I do not remember it limiting what security capabilities you have on Postgres. Needed a bit of a setup, but nothing terrible. If row level security does not cover your use case you are down the path of custom development anyway. My advise, be careful, this is a bottomless pit, with special combinations/overlapping rules etc. Go with a rules engine, prolog/datalog style would be my path.

steve-chavez 3y ago

> PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. PostgREST follows Postgres' "deny by default", you have to explicitly grant permissions for tables and views to be used. This is noted on the first tutorial[0]. Supabase overrides this default via `ALTER DEFAULT PRIVILEGES .. GRANT`[1]. This is done for easier onboarding of new users but you can turn this off with `ALTER DEFAULT PRIVILEGES .. REVOKE`. PostgREST also encourages you to create a dedicated schema for your api[2]. [0]: https://postgrest.org/en/stable/tutorials/tut0.html#step-4-create-database-for-api https://postgrest.org/en/stable/tutorials/tut0.html#step-4-c... [1]: See an example of this on https://supabase.com/docs/guides/api/using-custom-schemas https://supabase.com/docs/guides/api/using-custom-schemas. [2]: https://postgrest.org/en/stable/explanations/schema_isolation.html https://postgrest.org/en/stable/explanations/schema_isolatio...

boomskats 3y ago
> Supabase overrides this default Ah, thank you for this clarification. I was wondering what that comment was referring to. (and keep up the great work <3)
claytongulick 3y ago
Thanks for the additional info! Yep, I'm aware of the ability to revoke permissions, it's the design choice of "allow by default" that worries me - but I agree this is a Supabase thing, not PostgREST. On a new project, if you look at the dashboard (pointing at an existing db) access to all tables and data is on by default, you have to explicitly deny access to those tables. That's a concerning design choice for my industry (healthcare). > PostgREST also encourages you to create a dedicated schema for your api[2]. Yes, it does - but this isn't really called out strongly by Supabase (not PostgREST's fault). As some other commenters mentioned, it's not a big deal to create views in the api schema, and I agree. The part I'm still evaluating is whether there will be actual time savings and efficiency vs just creating CRUD endpoints in NodeJS (for example). The big "win" for PostgREST here is the ability to do deep querying and result shaping directly from the UI without needing to create a bunch of custom endpoints. If I have to create views anyway, it seems like I lose a lot of the benefits of using PostgREST in the first place, and add a layer of complication to the stack. I like PostgREST a lot, and I've been hoping that Supabase would be an effective Directus replacement (now that Directus has gone closed source), but I'm still figuring out whether the juice is worth the squeeze on my project. Would love any insight you or others have from using it in production with a medium-scale sized app.

bsdpufferfish 3y ago

> you have to create a separate schema ("api" or similar) to carefully pick what's exposed. Yep, but it's a CREATE VIEW instead of writing a route in python or ruby which also will likely hit an ORM... > Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. In general, you want to treat your DB as a precious resource and minimize the amount of work it has to do. The database should still be the source of truth (of generated assets). An easy solution is to configure NGINX to cache those asset requests, or write a script to unpack them to the file system.

boomskats 3y ago

Just to address some of your points: > PostgREST doesn't really highlight this, but for any non-trivial and sane application you have to create a separate schema ("api" or similar) to carefully pick what's exposed. That recommendation is highlighted here: https://postgrest.org/en/stable/explanations/schema_isolation.html https://postgrest.org/en/stable/explanations/schema_isolatio... > PostgREST has a scary "allow by default" permission model which is nearly enough to turn me off of the whole project. I assume you're talking about access via the db-anon, without authenticating your users. I can't think of what else you could be referring to. If you rely on JWT-based authentication, as any non-trivial and sane application would do, you'd have access to transaction-scoped impersonated roles. https://postgrest.org/en/stable/references/auth.html#overview-of-role-system https://postgrest.org/en/stable/references/auth.html#overvie... > having all writes go through supabase edge functions Why is this necessary, what are you unable to achieve with JWT + RLS/RBA? Again, I have to assume you don't have auth configured correctly. > RLS has some pitfalls too, and it's the only mechanism you have to secure your data. You also have role-based access, and the JWT auth to start with. However I have found RLS to be flexible enough, especially as you can define policies using subqueries into which you can embed UDFs. What are you trying to do that you can't do with it? > Serving assets from Postgres seems like a bad idea aside from some simple edge use cases. I instinctively agree in terms of devex, although I'm reluctant to just dismiss it. I think the performance hit with a database is the data retrieval, not the templating. I can see a possibility of this evolving into something very usable with some packaging of templates, especially once you realise the approach eliminates the need for more services, simplifies auth, and reduces rendering latency. The memory footprint of PostgREST is trivial compared to most middleware you'd have to add to the mix just to do some SSR.

SOLAR_FIELDS 3y ago
Supabase folks seem to agree regarding serving assets since they ship an object store for that kind of stuff in their constellation of apps rather than attempting to instead serve blobs out of the db. Hearkening back to an age long ago of having to deal with stuff like images and pdfs stored in db rows and the annoyances around that, I’m also inclined to agree, but less from the performance angle and more from the usability/compatibility angle of being forced to deal with the nuances of BLOB datatypes

cgio 3y ago

Using views is a good practice anyway as you do not tightly couple your API to your schema. It's been a few years since I used Postgrest (and to be fair I had no write scenarios to cover) but I do not remember it limiting what security capabilities you have on Postgres. Needed a bit of a setup, but nothing terrible. If row level security does not cover your use case you are down the path of custom development anyway. My advise, be careful, this is a bottomless pit, with special combinations/overlapping rules etc. Go with a rules engine, prolog/datalog style would be my path.