Thread: Default permissisons from schemas
Greetings,
* Stephen Frost (sfrost@snowman.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
wouldhave no effect. When not-null the 'nspdefowner' would be the owner of all objects created in the schema. When
not-nullthe 'nspdefacl' would be the initial acl for the object (modified for what grants are valid for the specific
typeof 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
notpossible today then the rights check would be done when an object creation is attempted, possibly with a fall-back
tocheck 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
onschema permissions
Comments?
Thanks,
Stephen
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.
> 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.
regards, tom lane
* 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
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> 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.
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.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> 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.
>
> 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.
This is, of course, only for object creation. It is possible to use
'set role' to set initial ownership on an object but for as much as it's
possible I find that it doesn't happen very often. 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).
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.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.
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...
> 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?
regards, tom lane
On Jan 23, 2007, at 12:07 PM, Stephen Frost wrote: >> 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: Is there a use-case for per-schema default ownership? I can't really think of one... -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
* Jim Nasby (decibel@decibel.org) wrote:
> On Jan 23, 2007, at 12:07 PM, Stephen Frost wrote:
> >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:
>
> Is there a use-case for per-schema default ownership? I can't really
> think of one...
Sure, all the objects in a given schema should be owned by a role which
all the admins of that schema are members of. I really see this as a
sensible step from ACLs since ownership implies additional permissions
(which can't otherwise be granted, otherwise it wouldn't matter so much).
We do this quite a bit and it's annoying when someone forgets to change
the ownership of something they created. Since we do this largely on a
per-schmea basis (and different schemas have different admin groups,
which can overlap) getting people to remember to 'set role' doesn't seem
likely to practically improve things much. I've considered writing a
cron job to periodically fix all the ownerships and permissions but then
having actual exceptions becomes a pain.
Thanks,
Stephen
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.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
On 1/24/07, Stephen Frost <sfrost@snowman.net> wrote: > Sure, all the objects in a given schema should be owned by a role which > all the admins of that schema are members of. I really see this as a > sensible step from ACLs since ownership implies additional permissions > (which can't otherwise be granted, otherwise it wouldn't matter so much). > > We do this quite a bit and it's annoying when someone forgets to change > the ownership of something they created. Since we do this largely on a > per-schmea basis (and different schemas have different admin groups, > which can overlap) getting people to remember to 'set role' doesn't seem > likely to practically improve things much. I've considered writing a > cron job to periodically fix all the ownerships and permissions but then > having actual exceptions becomes a pain. In every place I've worked, table permissions/ownership has been a problem...it's tedious and error-prone to catch permission errors...even with regression testing. My solution has always been to write pl/pgsql functions to do exactly that. It would be very nice not to have to do that however and have things auto-magically be set when you create them. Table rights almost always follow broad rules so it only natural to integrate that with schemas somehow...but admittedly it is awkward to put it into GRANT (and I've thought alot a bout. It seems like an alternate solution to this problem is to be able to hook triggers to pg_class and pg_namepace, so you can fire grant statements of your choosing when tables gets added/removed based on your own logic. Or, since triggers are broadly not allowed to system catalogs, maybe a trigger-ish sql callback could be added to the schema so that when objects inside are added/removed, you have the ability to inject your own sql. This gets you to the same place without hacking up grant or adding acl. merlin
On 1/24/07, Merlin Moncure <mmoncure@gmail.com> wrote: > when you create them. Table rights almost always follow broad rules > so it only natural to integrate that with schemas somehow...but > admittedly it is awkward to put it into GRANT (and I've thought alot a > bout. oops :( what I meant to say here is that I don't think it's possible to this in the way that Stephen wants because it would hack up GRANT to much. Tom was at least half right, this proposal was not discarded out of hand but it was on pretty shaky ground...I was one of the big supporters of extending grant this way in the original discussion but I think it might be the wrong approach. merlin
* Merlin Moncure (mmoncure@gmail.com) wrote:
> On 1/24/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> >when you create them. Table rights almost always follow broad rules
> >so it only natural to integrate that with schemas somehow...but
> >admittedly it is awkward to put it into GRANT (and I've thought alot a
> >bout.
>
> oops :( what I meant to say here is that I don't think it's possible
> to this in the way that Stephen wants because it would hack up GRANT
> to much. Tom was at least half right, this proposal was not discarded
> out of hand but it was on pretty shaky ground...I was one of the big
> supporters of extending grant this way in the original discussion but
> I think it might be the wrong approach.
err, what proposal wasn't touching the GRANT syntax at all but rather
adding some options to ALTER SCHEMA which I didn't think was all that
bad (and wasn't commented on except to point out that I needed to handle
different object types seperately). The current opposition, aiui, is
against having a 'default owner' for new objects in a schema and not the
default ACLs per schema.
I don't think it makes sense to have this syntax be part of the GRANT
syntax since it's really about a schema...
Thanks,
Stephen
On 1/24/07, Stephen Frost <sfrost@snowman.net> wrote: > err, what proposal wasn't touching the GRANT syntax at all but rather right, but the original proposal did: # %Allow GRANT/REVOKE permissions to be applied to all schema objects with one command which was more or less (with the NEW TABLES flavor of the command) duplicated by: # Allow GRANT/REVOKE permissions to be inherited by objects based on schema permissions and your proposal would make alter schema (and presumably create schema) the only command(s) that deal with privileges excluding grant/revoke. That, IMO is actually a bad thing...a surprising behavior. I think the 'new tables' form is better but has the same problems as your proposal in that it does not disambiguate sequences from tables, etc. It would however solve (I think!) your problem without resorting to ownership delegation. >I don't think it makes sense to have this syntax be part of the GRANT syntax since it's really about a schema.. So, basically I disagree with the above, and agree with the others wrt ownership change, but very much agree if it is pratical that having some mechanism of applying permissions to objects when they are created depending on which schema they are in is a good thing. merlin merlin
* Merlin Moncure (mmoncure@gmail.com) wrote:
> On 1/24/07, Stephen Frost <sfrost@snowman.net> wrote:
> >err, what proposal wasn't touching the GRANT syntax at all but rather
>
> right, but the original proposal did:
> # %Allow GRANT/REVOKE permissions to be applied to all schema objects
> with one command
>
> which was more or less (with the NEW TABLES flavor of the command)
> duplicated by:
>
> # Allow GRANT/REVOKE permissions to be inherited by objects based on
> schema permissions
These are pretty different things actually, imv.. I don't think it
makes sense to use GRANT on something which is clearly a schema
property. Would you still track the information in pg_namespace?
Nothing else makes sense to me and if it's there I think it's perfectly
reasonable to modify a schema property using ALTER SCHEMA. Hacking up
GRANT to do it sounds very, very ugly and not intuitive...
> and your proposal would make alter schema (and presumably create
> schema) the only command(s) that deal with privileges excluding
The proposal didn't involve CREATE SCHEMA. I don't really have a strong
opinion on that but I'm at least disinclined towards it as being
unnecessary.
> grant/revoke. That, IMO is actually a bad thing...a surprising
> behavior. I think the 'new tables' form is better but has the same
> problems as your proposal in that it does not disambiguate sequences
> from tables, etc. It would however solve (I think!) your problem
> without resorting to ownership delegation.
It doesn't seem unsuprising at all to me, especially with appropriate
documentation... Having the syntax in GRANT or in ALTER SCHEMA would
work for me for the ACLs. I don't see how that distincation does
anything to solve the concerns or provide a solution for ownership
delegation. Especially considering you can't change ownership with
GRANT today...
> >I don't think it makes sense to have this syntax be part of the GRANT
> syntax since it's really about a schema..
>
> So, basically I disagree with the above, and agree with the others wrt
> ownership change, but very much agree if it is pratical that having
> some mechanism of applying permissions to objects when they are
> created depending on which schema they are in is a good thing.
Ok. The issue that I have is that some permissions are exclusivly
available only to the owner of an object, and it's not possible to grant
them. I feel that it should be possible to have those permissions
applied to objects when they are created as well...
Thanks,
Stephen