sql: owning a schema gives privilege to drop tables in the schema #51931
Description
After ownership is implemented, when checking for privileges on an object, we also want to check if the user has ownership of the parents of the object. Ie having ownership of a database provides the user access to tables in the database.
Edit: Seems like this first pass at understanding this was wrong, seems like inheritance only applies to drop here.
After some further investigation, it seems like owning a schema gives drop privilege on tables. Owning a database does not seem to give privileges to child objects.
As user postgres
richard=# create database test2;
CREATE DATABASE
richard=# create user test2owner;
CREATE ROLE
richard=# alter database test2 owner to test2owner;
ALTER DATABASE
richard=# create user testuser;
CREATE ROLE
richard=# grant create on database test2 to testuser;
GRANT
As testuser
test2=> create schema s;
CREATE SCHEMA
test2=> create table s.t(x int);
CREATE TABLE
As test2owner
test2=> select * from s.t;
ERROR: permission denied for schema s
LINE 1: select * from s.t;
test2=> drop table s.t;
ERROR: permission denied for schema s
test2=> create table s.t2();
ERROR: permission denied for schema s
LINE 1: create table s.t2();
Testing for schema owner
as test2owner
test2=> create schema s2;
CREATE SCHEMA
test2=> grant create on schema s2 to testuser;
GRANT
as testuser
test2=> create table s2.t(x int);
CREATE TABLE
as test2owner
test2=> select * from s2.t;
ERROR: permission denied for table t
test2=> drop table s2.t;
DROP TABLE