Re: has_privs_of_role vs. is_member_of_role, redux

Robert Haas:
> 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.

Scratch my previous suggestion. A new, less fuzyy definition would be:
Ownership is not a privilege itself and as such not inheritable.

When role A is granted to role B, two things happen:
1. Role B now has the right to use the GRANTed privileges of role A.
2. Role B now has the right to become role A via SET ROLE.

WITH SET controls whether point 2 is the case or not.

WITH INHERIT controls whether role B actually executes their right to
use those privileges ("inheritance") **and** whether the set role is
done implicitly for anything that requires ownership, but of course only
WITH SET TRUE.

This is the same way that the role attributes INHERIT / NOINHERIT behave.

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

This would be included in the above.

> Here, though, it doesn't really seem simple enough to explain in one
> sentence, nor does it seem easy to reason about.

I think the "ownership is not inheritable" idea is easy to explain.

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

I would not make this about security-risks only. We didn't distinguish
between privileges and ownership that much before, because we didn't
have WITH INHERIT or WITH SET. Now that we have both, we could do so.

The ideas of "inherited GRANTs" and "a shortcut to avoid SET ROLE to do
owner-things" should be better to explain.

No judgement required.

All of this is to find a way to make WITH INHERIT TRUE, SET FALSE a
"real", risk-free thing - and not just some syntactic sugar. And if that
comes with the inability to COMMENT ON TABLE
owned_by_pg_read_all_settings... fine. No need for that at all.

However, it would come with the inability to do SELECT * FROM
owned_by_pg_read_all_settings, **unless** explicitly GRANTed to the
owner, too. This might feel strange at first, but should not be a
problem either. WITH INHERIT TRUE, SET FALSE is designed for built-in
roles or other container roles that group a set of privileges. Those
roles should not have objects they own anyway. And if they still do,
denying access to those objects unless explicitly granted is the safe way.

Best

Wolfgang