Re: has_privs_of_role vs. is_member_of_role, redux - Mailing list pgsql-hackers

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> Jeff Davis's comment in
> http://postgr.es/m/4f8d536a9221bccc5a33bb784dace0ef2310ec4a.camel@j-davis.com
> reminds me that I need to update this thread based on the patch posted
> over there. That patch allows you to grant membership in one role to
> another while withholding the ability to SET ROLE to the target role.
> And it's already possible to grant membership in one role to another
> while not allowing for inheritance of privileges. And I think that
> sheds new light on the two debatable points from my original email:
>
> On Thu, Aug 25, 2022 at 12:12 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > 1. robert can create new objects of various types owned by stuff:
> >
> > rhaas=> create schema stuff_by_robert authorization stuff;
> > CREATE SCHEMA
> > rhaas=> create schema unrelated_by_robert authorization unrelated;
> > ERROR:  must be member of role "unrelated"
> >
> > 2. robert can change the owner of objects he owns to instead be owned by stuff:
> >
> > rhaas=> alter table robert_table owner to unrelated;
> > ERROR:  must be member of role "unrelated"
> > rhaas=> alter table robert_table owner to stuff;
> > ALTER TABLE
>
> It now seems to me that we should disallow these, because if we adopt
> the patch from that other thread, and then you GRANT
> pg_read_all_settings TO alice WITH INHERIT false, SET false, you might
> reasonably expect that alice is not going to be able to clutter the
> system with a bunch of objects owned by pg_read_all_settings, but
> because of (1) and (2), alice can do exactly that.

Err, that shouldn't be allowed and if it is then that's my fault for not
implementing something to avoid having that happen.  imv, predefined
roles shouldn't be able to end up with objects they own except in cases
where we declare that a predefined role owns X.

I do think that the above two are correct and am fairly confident that
they were intentional as implemented as, otherwise, as noted in your
original message, you can't actually change the ownership of the
existing object/table and instead end up having to copy the whole thing,
which seems quite inefficient.  In other words, the same result could be
accomplished but in a much less efficient way and therefore it makes
sense to provide a way for it to be done that is efficient.

> To be more precise, I propose that in order for alice to create
> objects owned by bob or to change one of her objects to be owned by
> bob, she must not only be a member of role bob, but also inherit bob's
> privileges. If she has the ability to SET ROLE bob but not does not
> inherit his privileges, she can create new objects owned by bob only
> if she first does SET ROLE bob, and she cannot reassign ownership of
> her objects to bob at all.

... which means that to get a table owned by bob which is currently
owned by alice, alice has to:

set role bob;
create table;
grant insert on table to alice;
reset role;
insert into table select * from table;

That's pretty sucky and is the case that had been contemplated at the
time that was written to allow it (at least, if memory serves).  iirc,
that's also why we check the *bob* has CREATE rights in the place where
this is happening (as otherwise the above process wouldn't work either).

> Meanwhile, the patch that I posted previously to fix point (3) from
> the original email, that ALTER DEFAULT PRIVILEGES is allowed for no
> good reason, still seems like a good idea. Any reviews appreciated.

Haven't looked at the patch, +1 on the general change though, that looks
like incorrect usage of is_member_of_role to me.

Thanks,

Stephen