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
