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.