Postgres RLS is sophisticated enough to implement any auth model I've seen. The issue there is the same as the rest: that's rare expertise compared to a web framework and there's not much tooling to verify your implementation matches your spec. It's not how much do you trust postgres it's how much do you trust your knowledge of postgres.
That said I've worked on a profitable company's main product that was a full blown webapp running on postgrest and it was awesome. If you do have the expertise in house, and are willing to build some custom tooling around it, it's ideal for certain applications.
IMO its niche is nearly pure backend teams building out prototypes and proofs of concept, or feeling out a market or new product. If it works and you want to throw more resources at it, it's a smooth transition to just start building a real frontend around the DB you already have.
That said I've worked on a profitable company's main product that was a full blown webapp running on postgrest and it was awesome. If you do have the expertise in house, and are willing to build some custom tooling around it, it's ideal for certain applications.
IMO its niche is nearly pure backend teams building out prototypes and proofs of concept, or feeling out a market or new product. If it works and you want to throw more resources at it, it's a smooth transition to just start building a real frontend around the DB you already have.