On Fri, Mar 4, 2022 at 1:50 PM Robert Haas <robertmh...@gmail.com> wrote:
> On Mon, Feb 28, 2022 at 2:09 PM Stephen Frost <sfr...@snowman.net> wrote: > > The ability of a role to revoke itself from some other role is just > > something we need to accept as being a change that needs to be made, and > > I do believe that such a change is supported by the standard, in that a > > REVOKE will only work if you have the right to make it as the user who > > performed the GRANT in the first place. > > First, a quick overview of the > issue for those who have not followed the earlier threads in their > grueling entirety: > > rhaas=# create user boss; > CREATE ROLE > rhaas=# create user peon; > CREATE ROLE > rhaas=# grant peon to boss; > GRANT ROLE > rhaas=# \c - peon > You are now connected to database "rhaas" as user "peon". > rhaas=> revoke peon from boss; -- i don't like being bossed around! > REVOKE ROLE > > The wording for this example is hurting my brain. GRANT admin TO joe; \c admin REVOKE admin FROM joe; > I argue (and Stephen seems to agree) that the peon shouldn't be able > to undo the superuser's GRANT. I think I disagree. Or, at least, the superuser has full control of dictating how role membership is modified and that seems sufficient. The example above works because of: "A role is not considered to hold WITH ADMIN OPTION on itself, but it may grant or revoke membership in itself from a database session where the session user matches the role." If a superuser doesn't want "admin" to modify its own membership then they can prevent anyone but a superuser from being able to have a session_user of "admin". If that happens then the only way a non-superuser can modify group membership is by being added to said group WITH ADMIN OPTION. Now, if two people and a superuser are all doing membership management on the same group, and we want to add permission checks and multiple grants as tools, instead of having them just communicate with each other, then by all means let us do so. In that case, in answer to questions 2 and 3, we should indeed track which session_user made the grant and only allow the same session_user or the superuser to revoke it (we'd want to stop "ignoring" the GRANTED BY clause of REVOKE ROLE FROM so the superuser at least could remove grants made via WITH ADMIN OPTION). 4. Should we apply this rule to other types of grants, rather than > just to role membership? Why or why not? Consider this: > > The fact that the accountant may not be not in favor > of the auditor seeing what the accountant is doing with the money is > precisely the reason why we have auditors. [...] > However, when the superuser > performs the GRANT as in the above example, the grantor is recorded as > the table owner, not the superuser! So if we really want role > membersip and other kinds of grants to behave in the same way, we have > our work cut out for us here. > Yes, this particular choice seems unfortunate, but also not something that I think it is necessarily mandatory for us to improve. If the accountant is the owner then yes they get to decide permissions. In the presence of an auditor role either you trust the accountant role to keep the permissions in place or you define a superior authority to both the auditor and accountant to be the owner. Or let the superuser manage everything by witholding login and WITH ADMIN OPTION privileges from the ownership role. If we do extend role membership tracking I suppose the design question is whether the new role grantor dependency tracking will have a superuser be the recorded grantor instead of some owner. Given that roles don't presently have an owner concept, consistency with existing permissions in this manner would be trickier. Because of this, I would probably leave role grantor tracking at the session_user level while database objects continue to emanate from the object owner. The global vs database differences seem like a sufficient theoretical justification for the difference in implementation. David J.
