The appeal for firebase is that you can get away without your own backend for quite a while. It's perfectly feasible to consume databases directly from your frontend app without having to deal with a lot of caching or networking concerns, the firebase SDK does that for you. You basically get a complete API layer without having to write a lot of code yourself. It is very similar with stuff like auth handling, which is also tightly integrated into the database offering.

This makes development incredibly easy, especially in early stages and if you're starting out as a solo/small scale project. It is even better if you don't have an in depth knowledge about backend development/architecture and just want to build a product.

You don't get any of those benefits with a regular managed Postgres offering, at least not that I'm aware of. Supabase comes a lot closer to firebase than a regular managed postgres + ORM offering would but you also get an oepn source project that you could potentially self-host.

from what I heard self-hosting Supabase is possible, but a rather complex undertaking. There is some documentation around it, but is definitely a lot more complex than using their hosted offering is.