* Bruno Wolff III (bruno@wolff.to) wrote:
> Thinking about it some more, drops wouldn't be an issue since the owner
> can always drop objects.

Right.

> Creating objects in particular schemas or databases is not something that
> all roles may be able to do.

Yeah, I'm not entirely sure what I think about this issue. If you're
not allowed to change ownership of objects and SET ROLE drops your
regular ROLE's privileges then the role which owns the object originally
(and which you're required to be in) must have had create access to that
schema at some point.

I can see requiring the role that's changing the ownership to have
create access to the schema in which the object that's being changed is
in.

Thanks,

Stephen

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

iD8DBQFCwbUhrzgMPqB3kigRApv1AKCHWh6ft83Cy32UvhEz1f 8FR10PvACglvs9
axZK2Ctm92Yxh9nc66yhEeY=
=qz3k
-----END PGP SIGNATURE-----

* Michael Paesold (mpaesold@gmx.at) wrote:
> Stephen Frost wrote:
> >If you're considered the owner of an object then you have access to drop
> >it already. You have to be a member of the role to which you're
> >changing the ownership. That role not having permission to create the
> >object in place is an interesting question. That's an issue for SET
> >ROLE too, to some extent I think, do you still have your role's
> >permissions after you've SET ROLE to another role?
>
> For me this would be the "natural" way how SET ROLE would behave. This is
> unix'ism again, but using setuid to become another user, you loose the
> privileges of the old user context.
> Therefore SET ROLE should not inherit privileges from the other role. This
> seems to be the safes approach.
>
> Nevertheless, what does the standard say?

Hmm, it says there's a stack and that the thing on top is what's
currently used, so it sounds like it would drop the privs too, but imv
it's not entirely clear.

> >If not then you'd
> >have to grant CREATE on the schema to the role in order to create
> >objects owned by that role, and I don't think that's necessairly
> >something you'd want to do.
>
> Right, that's an issue. But since the new role will be the *owner* of the
> object, it *should* really have create-privileges in that schema. So the
> above way seems to be correct anyway.

I'm not entirely sure that you'd necessairly want the role to have
create privileges on the schema even when it owns things in the schema
but the more I think about it that doesn't seem all that unreasonable
either. I don't think it'd be very difficult to add such a check to the
ALTER OWNER code too though.

In general, and perhaps as a unix'ism to some extent, I don't
particularly like having to su to people. To get all the other
permissions which the role has you don't have to 'su' currently, and
personally I like that and think that's correct for a role-based
environment (unlike unix where you have users and groups).

Thanks,

Stephen

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

iD8DBQFCwbkxrzgMPqB3kigRAqJaAJ9HFUsV7LqQ5rAliWAUoZ i8kasB1QCgk0eB
mcnghpQ6iMlv5xfqfCh9jBI=
=FXuP
-----END PGP SIGNATURE-----

Stephen Frost <sfrost@snowman.net> writes:
> * Bruno Wolff III (bruno@wolff.to) wrote:
>> Creating objects in particular schemas or databases is not something that
>> all roles may be able to do.

> Yeah, I'm not entirely sure what I think about this issue.

We have a precedent, which is that RENAME checks for create rights.
If you want to lean on the argument that this is just a shortcut for
dropping the object and then recreating it somewhere else, then you
need (a) the right to drop the object --- which is inherent in being
the old owner, and (b) the right to create the new object, which means
that (b1) you can become the role you wish to have owning the object,
and (b2) *as that role* you would have the rights needed to create the
object.

Stephen's original analysis covers (a) and (b1) but not (b2). With (b2)
I'd agree that it's just a useful shortcut.

I don't see a need to treat SECURITY DEFINER functions as
superuser-only. We've had that facility since 7.3 or so and no one
has complained that it's too dangerous.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Bruno Wolff III (bruno@wolff.to) wrote:
> >> Creating objects in particular schemas or databases is not something that
> >> all roles may be able to do.
>
> > Yeah, I'm not entirely sure what I think about this issue.
>
> We have a precedent, which is that RENAME checks for create rights.

Ah, ok. Precedent is good.

> If you want to lean on the argument that this is just a shortcut for
> dropping the object and then recreating it somewhere else, then you
> need (a) the right to drop the object --- which is inherent in being
> the old owner, and (b) the right to create the new object, which means
> that (b1) you can become the role you wish to have owning the object,
> and (b2) *as that role* you would have the rights needed to create the
> object.
>
> Stephen's original analysis covers (a) and (b1) but not (b2). With (b2)
> I'd agree that it's just a useful shortcut.

Right. Ok, I'll develop a patch which covers (a), (b1) and (b2). I'll
also go through all of the superuser() calls in src/backend/commands/
and check for other places we may need *_ownercheck calls.

I expect to have the patch done either tonight or tommorow.

Thanks,

Stephen

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

iD8DBQFCwenorzgMPqB3kigRAjGqAJ4vdcNcy1gdKTd+EieKun jZOdPf2wCfWyaA
Kzu6pgUmTRFzKru/zvlKIzw=
=EJsu
-----END PGP SIGNATURE-----

I notice that AddRoleMems/DelRoleMems assume that ADMIN OPTION is not
inherited indirectly; that is it must be granted directly to you.
This seems wrong; SQL99 has under <privileges>

19) B has the WITH ADMIN OPTION on a role if a role authorization
descriptor identifies the role as granted to B WITH ADMIN OPTION
or a role authorization descriptor identifies it as granted WITH
ADMIN OPTION to another applicable role for B.

and in the Access Rules for <grant role statement>

1) Every role identified by <role granted> shall be contained
in the applicable roles for A and the corresponding role
authorization descriptors shall specify WITH ADMIN OPTION.

I can't see any support in the spec for the idea that WITH ADMIN OPTION
doesn't flow through role memberships in the same way as ordinary
membership; can you quote someplace that implies this?

regards, tom lane

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

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> I notice that AddRoleMems/DelRoleMems assume that ADMIN OPTION is not
> inherited indirectly; that is it must be granted directly to you.
> This seems wrong; SQL99 has under <privileges>
>
> 19) B has the WITH ADMIN OPTION on a role if a role authorization
> descriptor identifies the role as granted to B WITH ADMIN OPTION
> or a role authorization descriptor identifies it as granted WITH
> ADMIN OPTION to another applicable role for B.
>
> and in the Access Rules for <grant role statement>
>
> 1) Every role identified by <role granted> shall be contained
> in the applicable roles for A and the corresponding role
> authorization descriptors shall specify WITH ADMIN OPTION.
>
> I can't see any support in the spec for the idea that WITH ADMIN OPTION
> doesn't flow through role memberships in the same way as ordinary
> membership; can you quote someplace that implies this?

Hrm, no, sorry, I just interpreted the 'Access Rules' line for <grant
role statement> differently. That is to say:


1) Every role identified by <role granted> shall be contained
(Alright, all the roles which you're granting, right)

in the applicable roles for A and the corresponding role
(A must be in all the roles which are being granted)

authorization descriptors shall specify WITH ADMIN OPTION.
(the grants to A for those rules specify ADMIN OPTION)

This came across to me as meaning "there must exist an authorization
descriptor such that the granted-role equals <role granted>, the grantee
is A and WITH ADMIN OPTION is set". That could only be true if the
grant was done explicitly. Reading from 19 above (which I don't recall
seeing before, or at least not reading very carefully) I think you're
right. Either way is fine with me.

Thanks,

Stephen

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

iD8DBQFCwupDrzgMPqB3kigRAribAJ9UVHLIB2hgZXbXC8Xm8U F9PFj1rwCeMOGA
VnPI5VoK7cVkmz2uVIkUqPw=
=d/sn
-----END PGP SIGNATURE-----