SEO

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 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFtXaurzgMPqB3kigRAtI7AJ9YUwfUM9qJVhaWrz1qKb +IOJvFYgCePWNB
p14AwPtPQY+Pb1HYda2bMnQ=
=uj60
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFtkDVrzgMPqB3kigRArojAJ4pDYKa6coTIrRVzjgOG+ XI569MLQCbBVJ5
QI4RdhzjAjBSZLuRnqmiOEE=
=xPZ6
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFtl+LrzgMPqB3kigRAoB8AJ48l/Q2k8MgVcDsp3NhP//qfzrhBACglyUD
jEYgCiSYUjT/rX/QhbIMOkI=
=ZNtU
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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)



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFt2oyrzgMPqB3kigRAuASAJ9Qi7nt7jdFGpfbKDzjcS uAXLckHACeITt7
7606RRZToKCYVFMHqqCKsNE=
=yHzI
-----END PGP SIGNATURE-----

* 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFt3BZrzgMPqB3kigRAgRtAJ95m/c9mR7i9IlJh2Mk8pnHfOYbjgCfUJZH
YV3+yynv0gTwy8MDbCum6f8=
=jGSU
-----END PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match