Обсуждение: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies

Hi,

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:

To alter the owner, you must also be a direct or indirect member of the 
new owning role, and that role must have CREATE privilege on the table's 
schema. (These restrictions enforce that altering the owner doesn't do 
anything you couldn't do by dropping and recreating the table. However, 
a superuser can alter ownership of any table anyway.)

I tested that with:

# Connect as a superuser
$ psql test
test=# CREATE ROLE source_role WITH LOGIN;
CREATE ROLE
test=# CREATE ROLE target_role WITH LOGIN;
CREATE ROLE
test=# GRANT target_role to source_role;
GRANT ROLE
test=# GRANT CREATE on schema public to source_role;
GRANT

# Connect as a source_role
$ psql test -U source_role
test=> CREATE TABLE test_table();
CREATE TABLE

test=> \dt
              List of relations
  Schema |    Name    | Type  |    Owner
--------+------------+-------+-------------
  public | test_table | table | source_role
(1 row)

# Alter owner with 'ALTER TABLE OWNER TO'
test=> ALTER TABLE test_table owner to target_role;
ERROR:  permission denied for schema public

# Alter owner with 'REASSIGN OWNED'
test=> REASSIGN OWNED BY source_role to target_role;
REASSIGN OWNED

test=> \dt
              List of relations
  Schema |    Name    | Type  |    Owner
--------+------------+-------+-------------
  public | test_table | table | target_role
(1 row)

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?

Regards,
Nazir Bilal Yavuz
Microsoft

On Wed, Feb 8, 2023 at 5:49 AM Nazir Bilal Yavuz <byavuz81@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

Greetings,

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

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

On Wed, Feb 15, 2023 at 9:01 AM Stephen Frost <sfrost@snowman.net> wrote:
> 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.

Well .... yes and no.

The superuser can always hack things by modifying the system catalogs,
but we have plenty of integrity constraints that a superuser can't
just casually violate because they feel like it. For example, a
superuser is no more able to revoke privileges without revoking the
privileges that depend upon them than anyone else.

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

That's true, and it is a downside of dropping to CREATE check, but
it's also a bit hard to believe that anyone's really getting a lot of
value out of the current inconsistent checks.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Feb 15, 2023 at 9:01 AM Stephen Frost <sfrost@snowman.net> wrote:
> > 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.
>
> That's true, and it is a downside of dropping to CREATE check, but
> it's also a bit hard to believe that anyone's really getting a lot of
> value out of the current inconsistent checks.

I agree that we should be consistent about these checks.  I'm just more
inclined to have that consistent result include the CREATE check than
have it be dropped.  Not sure that it's a huge thing but being able to
control what set of owner roles are allowed to have objects in a given
schema seems useful and was certainly the intent, as I recall anyhow.

Thanks,

Stephen