Tom Lane escribió: > It looks to me like DropOwnedObjects doesn't actually insist on > superuserness to do DROP OWNED, only ability to become the role, > which means that DROP OWNED BY is completely broken for privileges > if executed by a non-superuser; the only privileges it would remove > would be those granted by the current user to the target user.
Well, moreover it fails completely with "permission denied" when tried, so yeah it is broken today. Now, I don't necessarily think we should remove the capability completely, as it seems useful. Consider a database where the superuser has created various department roles; admin privileges are given to certain users (bosses?) for each of such department roles. Such admins can add and remove other users from their roles at will, via grant and revoke; it seems useful to be able to remove all privileges from a certain user without going through objects one by one. Example: create user alice; create user bob; create role arts; create role arts_boss; grant arts to arts_boss with admin option; grant arts_boss to bob; create schema s_arts authorization arts_boss; revoke create on schema s_arts from public; \c - bob set role arts_boss; grant arts to alice; create table s_arts.animations (id int, description text); grant all on s_arts.animations to arts; grant update on s_arts.animations to alice; Here, Bob can GRANT and REVOKE specific things to Alice; but it doesn't work for Bob to "DROP OWNED BY alice" (fails with "permission denied to drop objects"), when in fact it does make sense that it would revoke those privileges -- but not those that alice might have gotten via a different chain of command, say on role "building" on which Charlie is admin. Alternatively we might say this is just gilding the lily and we should disallow this whole thing and restrict DROP OWNED. Two further possibilities here: a) only a superuser can run it, period. b) any user can run it on itself, and this revokes all privileges granted to this role, regardless of where they come from. (In any case I think we should disallow alice from doing "DROP OWNED by arts".) As a final comment, I am inclined to go the simplest route possible, first because I can't spend infinite time on this, and also because I think messing too much with this might lead to strange security issues. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
