Re: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies

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.

> 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