Jeff Davis's comment in http://postgr.es/m/4f8d536a9221bccc5a33bb784dace0ef2310ec4a.ca...@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 <robertmh...@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
