Re: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies

Greetings,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Wed, Feb 8, 2023 at 5:49 AM Nazir Bilal Yavuz <byavuz81(at)gmail(dot)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.

Yeah, that's not great.

> > 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.

When this was originally done, at least if my memory serves me
correctly, the idea was that it needed to be the receiver who needed
CREATE rights because, in that case, they could have just created it
themselves and there isn't some risk of objects being "given away" to
another user in a manner that they wouldn't have been able to create
those objects in the first place.

> 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 agree that being consistent makes sense.

> 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 don't think I really agree that "because a superuser can arrange for
it to not be valid" that it follows that requiring the recipient to have
CREATE permission on the parent object doesn't make sense. Surely for
any of these scenarios, whatever rule we come up with (assuming we have
any rule at all...) a superuser could arrange to make that rule no
longer consistent. I agree that we probably don't want to go through to
the point of what SQL requires which is actually that issuing a REVOKE
will end up DROP'ing things simply because that's just a recipe for
people ending up mistakenly having tables be DROP'd, but having a rule
that prevents users from just giving away their objects to other users,
even when the recipient couldn't have created that object, is good.

> 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.

Maybe I'm not remembering it entirely, but don't we also require that
the user performing the ownership change have the ability to SET ROLE to
the destination role? So if we're checking that the destination role
has CREATE rights on the parent object then necessarily the donor also
has that right.

> 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.

Hrm, didn't we have the requirement for SET ROLE previously? Or maybe
only in some of the code paths, but I have a pretty good recollection of
that existing before.

I'm not really a fan of just dropping the CREATE check. If we go with
"recipient needs CREATE rights" then at least without superuser
intervention and excluding cases where REVOKE's or such are happening,
we should be able to see that only objects where the owners of those
objects have CREATE rights exist in the system. If we drop the CREATE
check entirely then clearly any user who happens to have access to
multiple roles can arrange to have objects owned by any of their roles
in any schema or database they please without any consideration for what
the owner of the parent object's wishes are.

Thanks,

Stephen