REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies

From: Nazir Bilal Yavuz <byavuz81(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: adamwolk(at)microsoft(dot)com
Subject: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies
Date: 2023-02-08 10:49:37
Message-ID: cfc5cd06-d00a-0002-ce0a-7510104f3f04@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nitin Jadhav 2023-02-08 10:51:57 Re: Fix GUC_NO_SHOW_ALL test scenario in 003_check_guc.pl
Previous Message Alvaro Herrera 2023-02-08 10:49:00 Re: A bug with ExecCheckPermissions