Default permissisons from schemas

Greetings,

* Stephen Frost (sfrost(at)snowman(dot)net) wrote:
> It seems unlikely that I'm going to have time at the rate things are
> going but I was hoping to take a whack at default permissions/ownership
> by schema. Kind of a umask-type thing but for schemas instead of roles
> (though I've thought about it per role and that might also solve the
> particular problem we're having atm).

Following up on my reply to Joshua, what I'd like to propose is, for
comments and suggestions:

ALTER SCHEMA name [ [ WITH ] [ DEFAULT ] option [ ... ] ]

where option can be:

{ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER | EXECUTE }
[,...] | ALL [ PRIVILEGES ] }
TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]
} [, ...]

OWNER role

pg_namespace would be modified to have two new columns,
nspdefowner oid, and nspdefacl aclitem[]. When NULL these would have
no effect. When not-null the 'nspdefowner' would be the owner of all
objects created in the schema. When not-null the 'nspdefacl' would be
the initial acl for the object (modified for what grants are valid for
the specific type of object). These can only be changed by the schema
owner and the 'OWNER role' must have create permissions in the schema.
Ideally this would be checked when the ALTER SCHEMA is issued and then
a dependency created for that. If that's not possible today then the
rights check would be done when an object creation is attempted,
possibly with a fall-back to check the current user's rights.

The defaults would be NULL for these so there would be no change in
behaviour unless specifically asked for.

I believe this would cover the following to-do item:
Allow GRANT/REVOKE permissions to be inherited by objects based on
schema permissions

Comments?

Thanks,

Stephen