Re: DROP OWNED BY fails to drop privileges granted by non-owners (was Re: [GENERAL] Bug, Feature, or what else?)

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