REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies - Mailing list pgsql-hackers

From Nazir Bilal Yavuz
Subject REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies
Date
Msg-id cfc5cd06-d00a-0002-ce0a-7510104f3f04@gmail.com
Whole thread Raw
Responses Re: REASSIGN OWNED vs ALTER TABLE OWNER TO permission inconsistencies
List 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




pgsql-hackers by date:

Previous
From: Alvaro Herrera
Date:
Subject: Re: A bug with ExecCheckPermissions
Next
From: Nitin Jadhav
Date:
Subject: Re: Fix GUC_NO_SHOW_ALL test scenario in 003_check_guc.pl