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

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> On 02/08/2013 08:14 AM, Tom Lane wrote:
>> Of course, postgres has other options besides that, of which "DROP OWNED
>> BY ak02" is probably the most appropriate here. Or if you really want
>> to get rid of just that grant, SET ROLE TO akretschmer01 and revoke.

> The DROP OWNED was tried further up the thread and did not seem to work:

Huh. You're right, here is a complete test case:

regression=# create schema s1;
cCREATE SCHEMA
regression=# create user u1;
CREATE ROLE
regression=# create user u2;
CREATE ROLE
regression=# grant all on schema s1 to u1 with grant option;
GRANT
regression=# \c - u1
You are now connected to database "regression" as user "u1".
regression=> grant all on schema s1 to u2;
GRANT
regression=> \c - postgres
You are now connected to database "regression" as user "postgres".
regression=# \dn+ s1
List of schemas
Name | Owner | Access privileges | Description
------+----------+----------------------+-------------
s1 | postgres | postgres=UC/postgres+|
| | u1=U*C*/postgres +|
| | u2=UC/u1 |
(1 row)

regression=# drop user u2; -- expect failure here
ERROR: role "u2" cannot be dropped because some objects depend on it
DETAIL: privileges for schema s1
regression=# drop owned by u2;
DROP OWNED
regression=# drop user u2; -- failure here is wrong
ERROR: role "u2" cannot be dropped because some objects depend on it
DETAIL: privileges for schema s1
regression=# \dn+ s1
List of schemas
Name | Owner | Access privileges | Description
------+----------+----------------------+-------------
s1 | postgres | postgres=UC/postgres+|
| | u1=U*C*/postgres +|
| | u2=UC/u1 |
(1 row)

I believe the problem is that DROP OWNED for privileges is implemented
by calling REVOKE. As noted upthread, when a superuser does REVOKE,
it's executed as though the object owner did the REVOKE, so only
privileges granted directly by the object owner go away. In this
particular example, "DROP OWNED BY u1" makes the grant to u1 go away,
and then the grant to u2 goes away via cascade ... but "DROP OWNED BY
u2" fails to accomplish anything at all, because postgres never granted
anything directly to u2.

We haven't seen this reported before, probably because the use of
GRANT OPTIONS isn't very common, but AFAICS it's been wrong since
the invention of DROP OWNED.

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.
I'm not really sure what the desirable behavior would be in such a
case though. Ordinary users can't revoke privileges granted *to*
them, only privileges granted *by* them. So it's not necessarily
the case that a non-superuser should be able to make all privileges
granted to a target role go away, even if he's allowed to become
the target role and thereby drop objects that it owns. I wonder
how sensible it is really to allow DROP OWNED to non-superusers.

regards, tom lane