Re: Default permissisons from schemas

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> >   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 | TRI=
> > GGER | EXECUTE }=20
> >       [,...] | ALL [ PRIVILEGES ] }=20
> >       TO { role | PUBLIC } [, ...] [ WITH GRANT OPTION ]=20
> >     } [, ...]
>
> >     OWNER role
>
> This seems to ignore the problem that different types of objects have
> different privileges.  E.g., if I want to grant USAGE on all sequences
> that doesn't necessarily mean I want to grant USAGE on all languages.

Hm, I agree with that.  So the construct should be more along the lines
of:
{ GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES |
TRIGGER | EXECUTE } [,...] | ALL [ PRIVILEGES ] } ON
{ TABLE | FUNCTION | LANGUAGE } TO { role | PUBLIC } [, ...] [ WITH
GRANT OPTION ] } [, ...]

That list is pulled from the GRANT syntax where we don't currently
distinguish sequences from tables.  I can understand wanting to make
that distinction here but I'm not sure what is best to use.  Perhaps,
from the 'create' syntax we could use this list instead:

AGGREGATE | CAST | CONVERSION | DOMAIN | SEQUENCE | TABLE | VIEW |
FUNCTION | LANGUAGE | OPERATOR CLASS | OPERATOR | TYPE

I've left out TRIGGER, RULE and INDEX as objects which don't have
their own ACLs (I don't think?) and DATABASE, GROUP, ROLE, TABLESPACE,
and USER as objects which don't exist inside of schemas.

> >   When not-null the 'nspdefowner' would be the owner of all
> >   objects created in the schema.
>
> Whoa.  You are going to allow people to create objects owned by someone
> else?  I don't think so ... most Unix systems have forbidden object
> give-away for years, for very good reasons.

Hmm.  While I agree with the sentiment, Unix does provide for setgid
such that objects inherit a specific group on creation.  Using roles we
don't get that distinction so I don't think comparing it to Unix is a
slam-dunk.  There do need to be limitations here though, certainly.  A
couple options, in order of my preference:

User issueing the ALTER SCHEMA command must be a member of the role
being set as the nspdefowner.  Other users who can create tables in that
schema need not be a member of the role the object ends up being owned
by.  The idea here being that theoretically the schema owner could
change the ownership to what they want it to be afterwards anyway.

User creating table must have all rights normally required to create the
object in the schema with the owner/acl asked for.  This would probably
also work for most people.  If those rights are not available then the
appropriate action, imv, would be to fall back to the process for
determining the owner currently used today.  As for insufficient rights
for the ACL, the ACL for the object would go back to NULL.  I'm a little
concerned this would end up being confusing for users though I suppose
we could issue a notice if this happens.  An alternative would be to
deny the creation, but that doesn't seem quite right if the user has
create rights on the schema.

Comments?
Thanks!
    Stephen