Hacker News Reader

> 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-c...

[1]: See an example of this on https://supabase.com/docs/guides/api/using-custom-schemas.

[2]: https://postgrest.org/en/stable/explanations/schema_isolatio...

> 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)

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.

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_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#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.

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

> 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.

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.