On Thu, Sep 8, 2022 at 1:06 PM <walt...@technowledgy.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
