I'm chiming in a little late here, but as someone who worked on a system to basically work around the lack of unprivileged CREATE ROLE for a cloud provider (I worked on the Heroku Data team for several years), I thought it might be useful to offer my perspective. This is, of course, not the only use case, but maybe it's useful to have something concrete. As a caveat, I don't know how current this still is (I no longer work there, though the docs [1] seem to still describe the same system), or if there are better ways to achieve the goals of a service provider.
Broadly, the general use case is something like what Robert has sketched out in his e-mails. Heroku took care of setting up the database, archiving, replication, and any other system-level config. It would then keep the superuser credentials private, create a database, and a user that owned that database and had all the permissions we could grant it without compromising the integrity of the system. (We did not want customers to break their databases, both to ensure a better user experience and to avoid getting paged.) Initially, this meant customers got just the one database user because of CREATE ROLE's limitations. To work around that, at some point, we added an API that would CREATE ROLE for you, accessible through a dashboard and the Heroku CLI. This ran CREATE ROLE (or DROP ROLE) for you, but otherwise it largely let you configure the resulting roles as you pleased (using the original role we create for you). We wanted to avoid reinventing the wheel as much as possible, and the customer database (including the role configuration) was mostly a black box for us (we did manage some predefined permissions configurations through our dashboard, but the Postgres catalogs were the source of truth for that). Thinking about how this would fit into a potential non-superuser CREATE ROLE world, the sandbox superuser model discussed above covers this pretty well, though I share some of Robert's concerns around how this fits into existing systems. Hope this is useful feedback. Thanks for working on this! [1]: https://devcenter.heroku.com/articles/heroku-postgresql-credentials