sql: owning a schema gives privilege to drop tables in the schema

[RichardJCai]

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

[knz]

cc @solongordon how does this fit in the RFC you're authoring?

[solongordon]

It doesn't completely solve any of our problems but it should mitigate the pain of an "operator" not having privileges on all database objects. If a non-admin user with CREATEDB privileges creates a database, as the owner they'll now be guaranteed full privs on all objects within that database. But they won't be guaranteed any access to databases created by other users.

I'll update the RFC to reflect this since I do think it's a significant improvement.