In every project I've worked on, PG is only accessible via your backend and your backend is the one that's actually enforcing the security policies. When I first heard about the Superbase RLS issue the voice inside of my head was screaming: "if RLS is the only thing stopping people from reading everything in your DB then you have much much bigger problems"
What I think it happens is that non-technical people vibe-coding apps either don't take those messages seriously or they don't understand what it means but made their app work.
I used to be careful, but now I am paranoid on signing up to apps that are new. I guess it's gonna be like this for a while. Info-sec AIs sound way worse than this, tbh.