Re: Default permissisons from schemas

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >> Before discussing "limitations" you should first justify why we need any
> >> such concept at all. It was no part of the original TODO item and I
> >> cannot see any good use for it.
>
> > There are permissions which are not grantable but exist as implicitly
> > granted to the owner of object. These include drop, truncate, alter.
> > Practically, I find myself having to change the owner of objects which I
> > create almost as often as I'm defining the ACL for those objects. In
> > many of our schemas all the objects should be owned by the same 'admin'
> > role so that those who are in that role can perform the actions which
> > are only available to object owners, much the same as those objects
> > having a certain set of minimum ACLs.
>
> I don't see any argument here for not creating the object as owned by
> the creator -- as you note, SET ROLE is the way to cause something to be
> owned by a role you have permission to become. The important difference
> is that SET ROLE actually checks that you have that permission, whereas
> a magical catalog entry saying "create objects as somebody else instead"
> wouldn't. Maybe you could make it do so, but that would just be a
> surprising behavior IMHO; and surprising security-related behaviors are
> seldom a good idea.

One of the specific suggestions I made in the previous email was to have
the permissions be checked at object creation time. There's no reason
that couldn't be done and I believe it's quite straight-forward to do.

> BTW, I believe a schema owner can DROP any contained object whether he
> owns it or not; without that the owner's ability to DROP the schema
> would of course be worthless...

Yes, the schema owner can drop contained objects but that doesn't extend
to those who have only create rights on the schema. There is no 'drop'
right which can be granted, you have to be the schema owner or the owner
of the table.

> > I had thought it was going to be possible to set up roles/permissions
> > such that a newly created object would be owned by the role through
> > which the CREATE permission is given but that doesn't seem to be the
> > case (or perhaps I'm doing something wrong with it).
>
> Hm, I have some vague recollection that we considered that and rejected
> it. Probably because it's ill-defined: what if there is more than one
> path through which you've been granted CREATE permission?

Sure, in that case it's ill-defined. This would resolve that though to
a specific role, by schema. Permissions checks could then still be done
to ensure that the user attempting the creation is a member of the
default owner role and that role has create rights on the schema. If
either of those fail, fall back to the default case. Also, only run
down this path *if asked for* by the schema owner by having set the
default owner to begin with.

Thanks,

Stephen