I don't think you thought this through? The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need? It means the RLS is very wide open, since it needs to be able to see what User A and B can see. This forces your app to be the single boundary in pretty much all cases. Sure maybe you can give it a role where it has limited DDL rights(i.e not create table access or whatever).
> At minimum, direct db access means that every postgres CVE something I'd have to consider deeply.
I mean, not really, in practice? Most are just denial of service type bugs, not instant exploits. . Most of the DoS issues are not that big of a deal for us. They could affect us, but 99.9% of the time, they don't in reality, before we upgrade. RLS has been in PG for a good many years, it's quite stable. Sure, we upgrade PostgreSQL regularly, but you should do that anyway, regardless of RLS usage or not.
Well I'm not designing some arbitrary system. Don't expect a full spec.
> The problem with the app being constrained to RLS is you have User A and User B accessing your API, how do you get them access to the different data they need?
You can still have users provide the access to the service (ie: the password to get to the role), or otherwise map between User A and the role, etc. The service just brokers and constrains access.
> Sure maybe you can give it a role where it has limited DDL rights(i.e not create table access or whatever).
Yes, of course. Just as you would with users.
> I mean, not really, in practice?
I don't think it's contentious to say that if RLS is your only security boundary then your pressure is entirely on that one boundary. How could it be any other way? If you want to say "It's an extremely good boundary", okay. There have been relevant vulnerabilities though and I really don't know that we should say that we should expect 0 vulnerabilities in RLS in the future such that every employee having access to a db containing financial data is fine. The point of layering is to avoid having to put all pressure on this one thing.
I don't even understand how this is contentious or confusing. If you have one boundary, you have one boundary. I'm suggesting that I'm uncomfortable with systems having one boundary.