Message-ID: <51153B61.5010207@gmail.com>
Date: Fri, 08 Feb 2013 09:52:33 -0800
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:17.0) Gecko/20130107 Thunderbird/17.0.2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Andreas Kretschmer <andreas@a-kretschmer.de>,
	Alvaro Herrera <alvherre@2ndquadrant.com>, pgsql-hackers@postgreSQL.org
Subject: Re: DROP OWNED BY fails to drop privileges granted by non-owners
	(was Re: [GENERAL] Bug, Feature, or what else?)
References: 
 <119745304.24731.1360333559327.JavaMail.open-xchange@ox.ims-firmen.de>
	<51151128.8040807@gmail.com>
	<1568445490.24920.1360335539088.JavaMail.open-xchange@ox.ims-firmen.de>
	<51151764.4090405@gmail.com>
	<609013460.25018.1360337038276.JavaMail.open-xchange@ox.ims-firmen.de>
	<17143.1360340065@sss.pgh.pa.us> <51152A28.4060303@gmail.com>
	<19080.1360343369@sss.pgh.pa.us>
In-Reply-To: <19080.1360343369@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 02/08/2013 09:09 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.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.

I am not sure I am following. Are we talking two different cases here?

1) As mentioned in the first paragraph the case where running DROP OWNED 
as a supersuser does not work.

2) A non-superuser running DROP OWNED and not having the necessary 
privileges.

>
> 			regards, tom lane
>


-- 
Adrian Klaver
adrian.klaver@gmail.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers