On Wed, Feb 8, 2023 at 5:49 AM Nazir Bilal Yavuz <byavu...@gmail.com> wrote: > My colleague Adam realized that when transferring ownership, 'REASSIGN > OWNED' command doesn't check 'CREATE privilege on the table's schema' on > new owner but 'ALTER TABLE OWNER TO' docs state that:
Well, that sucks. > As you can see, 'ALTER TABLE OWNER TO' checked 'CREATE privilege on the > table's schema' on target_role but 'REASSIGN OWNED' didn't check it and > transferred ownership of the table. Is this a potential security gap or > intentional behaviour? I was looking at this recently and I noticed that for some object types, ALTER WHATEVER ... OWNER TO requires that the user transferring ownership possess CREATE on the containing object, which might be a schema or database depending on the object type. For other object types, ALTER WHATEVER ... OWNER TO requires that the user *receiving* ownership possess CREATE on the containing object, either schema or database. That's not very consistent, and I couldn't find anything to explain why it's like that. Now you've discovered that REASSIGN OWNED ignores this issue altogether. Ugh. We probably ought to make this consistent. Either the donor needs CREATE permission on the parent object, or the recipient does, or both, or neither, and whatever the rule may be, it should be consistent across all types of objects (except for shared objects which have no parent object) and across all commands. I think that requiring the recipient to have CREATE permission on the parent object doesn't really make sense. It could make sense if we did it consistently, so that there was a hard-and-fast rule that the current owner always has CREATE on the parent object, but I think that will never happen. You can be a superuser and thus create objects with no explicit privileges on the containing object at all, and if your role is later made NOSUPERUSER, you'll still own those objects. You could have the privilege initially and then later it could be revoked, and we would not demand those objects to be dropped or given to a different owner or whatever. Changing those behaviors doesn't seem desirable. It would lead to lots of pedantic failures trying to execute REASSIGN OWNED or REVOKE or ALTER USER ... NOSUPERUSER and I can't see what we'd really be gaining. I think that requiring the donor to have CREATE permission on the parent object makes a little bit more sense. I wouldn't mind if we tried to standardize on that rule. It would be unlikely to inconvenience users trying to execute REASSIGN OWNED because most users running REASSIGNED OWNED are going to be superusers already, or at the very least highly privileged. However, I'm not sure how much logical sense it makes. Changing the owner of an object is pretty different from creating it. It makes sense to require CREATE permission on the parent object if an object is being *renamed*, because that's a lot like creating a new object: there's now something in this schema or database under a name that previously wasn't in use. But ALTER .. OWNER TO does not have that effect, so I think it's kind of unclear why we even care about CREATE on the parent object. I think the important permission checks around ALTER ... OWNER TO are on the roles involved and their relationship to the object itself. You need to own the object (or inherit those privileges) and, in master, you need to be able to SET ROLE to the new owner. If you have those permissions, is that, perhaps, good enough? Maybe checking CREATE on the parent object just isn't really needed. -- Robert Haas EDB: http://www.enterprisedb.com
