Re: CREATEROLE and role ownership hierarchies

On Sat, Jan 22, 2022 at 4:20 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Whoah, really? No, I don't agree with this, it's throwing away the
> entire concept around inheritance of role rights and how you can have
> roles which you can get the privileges of by doing a SET ROLE to them
> but you don't automatically have those rights.

I see it differently. In my opinion, what that does is make the patch
actually useful instead of largely a waste of time. If you are a
service provider, you want to give your customers a super-user-like
experience without actually making them superuser. You don't want to
actually make them superuser, because then they could do things like
change archive_command or install plperlu and shell out to the OS
account, which you don't want. But you do want them to be able to
administer objects within the database just as a superuser could. And
a superuser has privileges over objects they own and objects belonging
to other users automatically, without needing to SET ROLE.

Imagine what happens if we adopt your proposal here. Everybody now has
to understand the behavior of a regular account, the behavior of a
superuser account, and the behavior of this third type of account
which is sort of like a superuser but requires a lot more SET ROLE
commands. And also every tool. So for example pg_dump and restore
isn't going to work, not even on the set of objects this
elevated-privilege user can access. pgAdmin isn't going to understand
that it needs to insert a bunch of extra SET ROLE commands to
administer objects. Ditto literally every other tool anyone has ever
written to administer PostgreSQL. And for all of that pain, we get
exactly zero extra security.

--
Robert Haas
EDB: http://www.enterprisedb.com