agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Tom Lane <[email protected]>
Cc: Andreas Kretschmer <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: [email protected]
Subject: Re: DROP OWNED BY fails to drop privileges granted by non-owners (was Re: [GENERAL] Bug, Feature, or what else?)
Date: Fri, 08 Feb 2013 09:52:33 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <119745304.24731.1360333559327.JavaMail.open-xchange@ox.ims-firmen.de>
<[email protected]>
<1568445490.24920.1360335539088.JavaMail.open-xchange@ox.ims-firmen.de>
<[email protected]>
<609013460.25018.1360337038276.JavaMail.open-xchange@ox.ims-firmen.de>
<[email protected]>
<[email protected]>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-hackers>
On 02/08/2013 09:09 AM, Tom Lane wrote:
> Adrian Klaver <[email protected]> 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
[email protected]
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
view thread (25+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: DROP OWNED BY fails to drop privileges granted by non-owners (was Re: [GENERAL] Bug, Feature, or what else?)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox