I think this has potential. In a managed services environment, you can imagine the provider as the super-duper user, having the ability to do anything - because they control the box, so there's really no stopping it - but presumably very little interest in what happens within the database. Then you have the tenant, who is a semi-super-user, authorized by the provider to do anything internal to the database that the provider doesn't think will cause them problems. With the setup you're proposing here, I suppose what the provider needs to do is have a role like 'tenant' and make all the other tenant role members of that master role. Then the tenant can log in as 'tenant' as set up event triggers that will apply to all of those users, but there's no security compromise for the provider because the role (or roles) that they use to log in are not members of 'tenant'.
Isn’t this backwards? If all those roles are members of "tenant" then they can do anything "tenant" can do. The reverse might work - make "tenant" a member of all the related roles - although I haven’t thought through in detail.
The comparison is to making all roles members of "postgres" (disaster) vs. making "postgres" a member of all roles (redundant, because of how permissions work for superuser, but harmless).
