Re: has_privs_of_role vs. is_member_of_role, redux

On Thu, Sep 8, 2022 at 1:06 PM <walther(at)technowledgy(dot)de> wrote:
> A different line of thought (compared to the "USAGE" privilege I
> discussed earlier), would be:
> To transfer ownership of an object, you need two sets of privileges:
> - You need to have the privilege to initiate a request to transfer
> ownership.
> - You need to have the privilege to accept a request to transfer ownership.
>
> Let's imagine there'd be such a request created temporarily, then when I
> start the process of changing ownership, I would have to change to the
> other role and then accept that request.
>
> In theory, I could also inherit that privilege, but that's not how the
> system works today. By using is_member_of_role, the decision was already
> made that this should not depend on inheritance. What is left, is the
> ability to do it via SET ROLE only.

I do not accept the argument that we've already made the decision that
this should not depend on inheritance. It's pretty clear that we
haven't thought carefully enough about which checks should depend only
on membership, and which ones should depend on inheritance. The patch
I committed just now to fix ALTER DEFAULT PRIVILEGES is one clear
example of where we've gotten that wrong. We also changed the way
predefined roles worked with inheritance not too long ago, so that
they started using has_privs_of_role() rather than
is_member_of_role(). Our past thinking on this topic has been fuzzy
enough that we can't really conclude that because something uses
is_member_of_role() now that's what it should continue to do in the
future. We are working to get from a messy situation where the rules
aren't consistent or understandable to one where they are, and that
may mean changing some things.

> So it should not be has_privs_of_role() nor has_privs_of_role() ||
> member_can_set_role(), as you suggested above, but rather just
> member_can_set_role() only. Of course, only in the context of the SET
> ROLE patch.

Now, having said that, this choice of behavior might have some
advantages. It would mean that you could GRANT pg_read_all_settings TO
someone WITH INHERIT TRUE, SET FALSE and that user would be able to
read all settings but would not be able to create objects owned by
pg_read_all_settings. It would also be upward-compatible with the
existing behavior, which is nice.

Well, maybe. Suppose that role A has been granted pg_read_all_settings
WITH INHERIT TRUE, SET TRUE and role B has been granted
pg_read_all_settings WITH INHERIT TRUE, SET FALSE. A can create a
table owned by pg_read_all_settings. If A does that, then B can now
create a trigger on that table and usurp the privileges of
pg_read_all_settings, after which B can now create any number of
objects owned by pg_read_all_settings. If A does not do that, though,
I think that with the proposed rule, B would have no way to create
objects owned by A. This is a bit unsatisfying. It seems like B should
either have the right to usurp pg_read_all_settings's privileges or
not, rather than maybe having that right depending on what some other
user chooses to do.

But maybe it's OK. It's hard to come up with perfect solutions here.
One could take the view that the issue here is that
pg_read_all_settings shouldn't have the right to create objects in the
first place, and that this INHERIT vs. SET ROLE distinction is just a
distraction. However, that would require accepting the idea that it's
possible for a role to lack privileges granted to PUBLIC, which also
sounds pretty unsatisfying. On the whole, I'm inclined to think it's
reasonable to suppose that if you want to grant a role to someone
without letting them create objects owned by that role, it should be a
role that doesn't own any existing objects either. Essentially, that's
legislating that predefined roles should be minimally privileged: they
should hold the ability to do whatever it is that they are there to do
(like read all settings) but not have any other privileges (like the
ability to do stuff to objects they own).

But maybe there's a better answer. Ideas/opinions welcome.

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