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?
> 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.
Best Regards,
Michael Paesold