Re: CREATEROLE and role ownership hierarchies

Greetings,

On Mon, Jan 24, 2022 at 15:33 Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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.

The idea behind this patch is to enable creation and dropping of roles,
which isn’t possible now without being effectively a superuser.

Forcing owners to also implicitly have all rights of the roles they create
is orthogonal to that and an unnecessary change.

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.

I am not saying that we would explicitly set all cases to be noninherit or
that we would even change the default away from what it is today, only that
we should use the existing role system and it’s concept of
inherit-vs-noninherit rather than throwing all of that away.

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.

Inherit vs. noninherit roles is not a new concept, it has existed since the
role system was implemented. Further, that system does not require a lot
of SET ROLE commands unless and until an admin sets up a non-inherit role.
At that time, however, it’s expected that the rights of a role which has
inherit set to false are not automatically allowed for the role to which it
was GRANT’d. That’s how roles have always worked since they were
introduced.

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.

We have an inherit system today and pg_dump works just fine, as far as I’m
aware, and it does, indeed, issue SET ROLE at various points. Perhaps you
could explain with PG today what the issue is that is caused? Or what
issue pgAdmin has with PG’s existing role inherit system?

Further, being able to require a SET ROLE before running a given operation
is certainly a benefit in much the same way that having a user have to sudo
before running an operation is.

Thanks,

Stephen

>