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