* 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