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.
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.
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.
Thanks,
--
Robert Haas
EDB: http://www.enterprisedb.com
