Re: has_privs_of_role vs. is_member_of_role, redux

On Mon, Sep 26, 2022 at 12:16 PM Wolfgang Walther
<walther(at)technowledgy(dot)de> wrote:
> I think, when WITH INHERIT TRUE, SET FALSE is set, we should:
> - Inherit all explicitly granted privileges
> - Not inherit any DDL privileges implicitly given through ownership:
> CREATE, REFERENCES, TRIGGER.
> - Inherit all other privileges implicitly given through ownership (DML +
> others)

I don't think we're going to be very happy if we redefine inheriting
the privileges of another role to mean inheriting only some of them.
That seems pretty counterintuitive to me. I also think that this
particular definition is pretty fuzzy.

Your previous proposal was to make the SET attribute of a GRANT
control not only the ability to SET ROLE to the target role but also
the ability to create objects owned by that role and/or transfer
objects to that role. I think some people might find that behavior a
little bit surprising - certainly, it goes beyond what the name SET
implies - but it is at least simple enough to explain in one sentence,
and the consequences don't seem too difficult to reason about.

Here, though, it doesn't really seem simple enough to explain in one
sentence, nor does it seem easy to reason about. I'm not sure that
there's any firm distinction between DML privileges and DDL
privileges. I'm not sure that the privileges that you mention are all
and only those that are security-relevant, nor that it would
necessarily remain true in the future even if it's true today.

There are many operations which are permitted or declined just using
an owner-check. One example is commenting on an object. That sure
sounds like it would fit within your proposed "DDL privileges
implicitly given through ownership" category, but it doesn't really
present any security hazard, so I do not think there is a good reason
to restrict that from a user who has INHERIT TRUE, SET FALSE. Another
is renaming an object, which is a little more murky. You can't
directly usurp someone's privileges by renaming an object that they
own, but you could potentially rename an object out of the way and
replace it with one that you own and thus induce a user to do
something dangerous. I don't really want to make even small exceptions
to the idea that inheriting a role's privileges means inheriting all
of them, and I especially don't want to make large exceptions, or
exceptions that involve judgement calls about the relative degree of
risk of each possible operation.

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