Re: Bug, Feature, or what else?

Hi,

i have created a normal user (no superuser) akretschmer01 and another normal
user ak02. All fine.
The first user is the owner of the db.

As user akretschmer01 i granted:

-bash-4.1$ psql -U akretschmer01 db115150
psql (9.1.8)
Type "help" for help.

db115150=> grant all on schema public to ak02;
GRANT
db115150=> commit;

There are no tables or other objects createt as user ak02.

Now i switched to user postgres (superuser):

-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# drop user ak02
db115150-# ;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

(role ak02 cannot be dropped because some objects depend on it, Detail:
privileges for schema public)

I can revoke all on database from user:

db115150=# revoke all on database db115150 from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

but still i can't drop the user.

I can do that with 9.1.8 and 9.1.1 too, same problem.

How can i drop a user as SUPERUSER (!) with all privileges?

Regards, Andreas

2013/2/8 Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>

> How can i drop a user as SUPERUSER (!) with all privileges?
>

According to the docs:
http://www.postgresql.org/docs/current/interactive/sql-droprole.html

> A role cannot be removed if it is still referenced in any database of the
cluster;
> an error will be raised if so. Before dropping the role, you must drop
all the objects
> it owns (or reassign their ownership) and revoke any privileges the role
has been
> granted. The REASSIGN OWNED<http://www.postgresql.org/docs/current/interactive/sql-reassign-owned.html>
and DROP OWNED<http://www.postgresql.org/docs/current/interactive/sql-drop-owned.html>
commands
can be useful for this purpose.

--
Victor Y. Yegorov

Andreas Kretschmer wrote:
> i have created a normal user (no superuser) akretschmer01 and another normal
> user ak02. All fine.
> The first user is the owner of the db.
>
>
> As user akretschmer01 i granted:

> db115150=> grant all on schema public to ak02;

> There are no tables or other objects createt as user ak02.
>
>
> Now i switched to user postgres (superuser):

> db115150=# drop user ak02
> db115150-# ;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>
> (role ak02 cannot be dropped because some objects depend on it, Detail:
> privileges for schema public)
>
> I can revoke all on database from user:
>
> db115150=# revoke all on database db115150 from ak02;
> REVOKE
> db115150=# drop user ak02;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>
>
> but still i can't drop the user.
>
>
> I can do that with 9.1.8 and 9.1.1 too, same problem.
>
>
> How can i drop a user as SUPERUSER (!) with all privileges?

You have to revoke the permissions that you granted.

There are two options:

db115150=# DROP OWNED BY ak02;
DROP OWNED

or

db115150=# REVOKE ALL ON SCHEMA public FROM ak02;
REVOKE

Then you can drop the user.

Yours,
Laurenz Albe

On 02/08/2013 06:25 AM, Andreas Kretschmer wrote:
> Hi,
>
> i have created a normal user (no superuser) akretschmer01 and another normal
> user ak02. All fine.
> The first user is the owner of the db.
>
>
> As user akretschmer01 i granted:
>
> -bash-4.1$ psql -U akretschmer01 db115150
> psql (9.1.8)
> Type "help" for help.
>
> db115150=> grant all on schema public to ak02;
> GRANT
> db115150=> commit;
>
> There are no tables or other objects createt as user ak02.
>
>
> Now i switched to user postgres (superuser):
>
>
> -bash-4.1$ psql -U postgres db115150
> psql (9.1.8)
> Type "help" for help.
>
> db115150=# drop user ak02
> db115150-# ;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>
> (role ak02 cannot be dropped because some objects depend on it, Detail:
> privileges for schema public)
>
> I can revoke all on database from user:
>
> db115150=# revoke all on database db115150 from ak02;
> REVOKE
> db115150=# drop user ak02;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>
>
> but still i can't drop the user.
>
>
> I can do that with 9.1.8 and 9.1.1 too, same problem.
>
>
> How can i drop a user as SUPERUSER (!) with all privileges?

I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.

REVOKE ALL ON DATABASE does not mean revoke all privileges for all
objects in the database. It only applies to DATABASE privileges-CONNECT,
CREATE, TEMP

>
>
>
> Regards, Andreas
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> > How can i drop a user as SUPERUSER (!) with all privileges?
>
> You have to revoke the permissions that you granted.
>
> There are two options:
>
> db115150=# DROP OWNED BY ak02;
> DROP OWNED
>
> or
>
> db115150=# REVOKE ALL ON SCHEMA public FROM ak02;
> REVOKE
>
> Then you can drop the user.
>
> Yours,
> Laurenz Albe

nice idea, but unfortunately no:

db115150=# drop owned by ak02;
DROP OWNED
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

Andreas

> > How can i drop a user as SUPERUSER (!) with all privileges?
>
> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.

doesn't help:

-bash-4.1$ psql -U postgres db115150
psql (9.1.8)
Type "help" for help.

db115150=# begin;
BEGIN
db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public
db115150=#

On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:
>
>>> How can i drop a user as SUPERUSER (!) with all privileges?
>>
>> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.
>
>
> doesn't help:
>
> -bash-4.1$ psql -U postgres db115150
> psql (9.1.8)
> Type "help" for help.
>
> db115150=# begin;
> BEGIN
> db115150=# revoke all on schema public from ak02;
> REVOKE
> db115150=# drop user ak02;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
> db115150=#
>
>

So what does \dn+ public show?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:19
geschrieben:
> On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:
> >
> >>> How can i drop a user as SUPERUSER (!) with all privileges?
> >>
> >> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.
> >
> >
> > doesn't help:
> >
> > -bash-4.1$ psql -U postgres db115150
> > psql (9.1.8)
> > Type "help" for help.
> >
> > db115150=# begin;
> > BEGIN
> > db115150=# revoke all on schema public from ak02;
> > REVOKE
> > db115150=# drop user ak02;
> > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> > DETAIL: Privilegien für Schema public
> > db115150=#
> >
> >
>
> So what does \dn+ public show?

db115150=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+-----------------------------+------------------------
public | postgres | postgres=UC/postgres +| standard public schema
| | akretschmer01=U*C*/postgres+|
| | ak02=UC/akretschmer01 |
(1 row)

On 02/08/2013 07:23 AM, Andreas Kretschmer wrote:
>
>
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:19
> geschrieben:
>> On 02/08/2013 06:58 AM, Andreas Kretschmer wrote:
>>>
>>>>> How can i drop a user as SUPERUSER (!) with all privileges?
>>>>
>>>> I believe you will need to REVOKE ALL ON SCHEMA public FROM ak02.
>>>
>>>
>>> doesn't help:
>>>
>>> -bash-4.1$ psql -U postgres db115150
>>> psql (9.1.8)
>>> Type "help" for help.
>>>
>>> db115150=# begin;
>>> BEGIN
>>> db115150=# revoke all on schema public from ak02;
>>> REVOKE
>>> db115150=# drop user ak02;
>>> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
>>> DETAIL: Privilegien für Schema public
>>> db115150=#
>>>
>>>
>>
>> So what does \dn+ public show?
>
> db115150=# \dn+ public
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+-----------------------------+------------------------
> public | postgres | postgres=UC/postgres +| standard public schema
> | | akretschmer01=U*C*/postgres+|
> | | ak02=UC/akretschmer01 |
> (1 row)
>
>

If I am following the Notes section correctly in:

http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html

"If a superuser chooses to issue a GRANT or REVOKE command, the command
is performed as though it were issued by the owner of the affected
object. Since all privileges ultimately come from the object owner
(possibly indirectly via chains of grant options), it is possible for a
superuser to revoke all privileges, but this might require use of
CASCADE as stated above."

Try:

revoke all on schema public from ak02 cascade;

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

>
> If I am following the Notes section correctly in:
>
> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html
>
> "If a superuser chooses to issue a GRANT or REVOKE command, the command
> is performed as though it were issued by the owner of the affected
> object. Since all privileges ultimately come from the object owner
> (possibly indirectly via chains of grant options), it is possible for a
> superuser to revoke all privileges, but this might require use of
> CASCADE as stated above."
>
>
> Try:
>
> revoke all on schema public from ak02 cascade;

Yeah, i read that before, but doesn't help:

db115150=# revoke all on schema public from ak02 cascade;
REVOKE
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

Strange, isn't it?

Andreas

On 02/08/2013 07:45 AM, Andreas Kretschmer wrote:
>
>>
>> If I am following the Notes section correctly in:
>>
>> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html
>>
>> "If a superuser chooses to issue a GRANT or REVOKE command, the command
>> is performed as though it were issued by the owner of the affected
>> object. Since all privileges ultimately come from the object owner
>> (possibly indirectly via chains of grant options), it is possible for a
>> superuser to revoke all privileges, but this might require use of
>> CASCADE as stated above."
>>
>>
>> Try:
>>
>> revoke all on schema public from ak02 cascade;
>
> Yeah, i read that before, but doesn't help:
>
>
>
> db115150=# revoke all on schema public from ak02 cascade;
> REVOKE
> db115150=# drop user ak02;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>
>
> Strange, isn't it?

Well I got left is:

REVOKE PUBLIC FROM ak02;

>
>
> Andreas
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:48
geschrieben:
> On 02/08/2013 07:45 AM, Andreas Kretschmer wrote:
> >
> >>
> >> If I am following the Notes section correctly in:
> >>
> >> http://www.postgresql.org/docs/9.1/interactive/sql-revoke.html
> >>
> >> "If a superuser chooses to issue a GRANT or REVOKE command, the command
> >> is performed as though it were issued by the owner of the affected
> >> object. Since all privileges ultimately come from the object owner
> >> (possibly indirectly via chains of grant options), it is possible for a
> >> superuser to revoke all privileges, but this might require use of
> >> CASCADE as stated above."
> >>
> >>
> >> Try:
> >>
> >> revoke all on schema public from ak02 cascade;
> >
> > Yeah, i read that before, but doesn't help:
> >
> >
> >
> > db115150=# revoke all on schema public from ak02 cascade;
> > REVOKE
> > db115150=# drop user ak02;
> > FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> > DETAIL: Privilegien für Schema public
> >
> >
> > Strange, isn't it?
>
> Well I got left is:
>
> REVOKE PUBLIC FROM ak02;
>

i think you mean:

db115150=# revoke all on schema public from ak02;
REVOKE
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

Andreas Kretschmer wrote:
>>> db115150=# revoke all on schema public from ak02;
>>> REVOKE
>>> db115150=# drop user ak02;
>>> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
>>> DETAIL: Privilegien für Schema public
>>
>> So what does \dn+ public show?
>
> db115150=# \dn+ public
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+-----------------------------+------------------------
> public | postgres | postgres=UC/postgres +| standard public schema
> | | akretschmer01=U*C*/postgres+|
> | | ak02=UC/akretschmer01 |
> (1 row)

Seems like you can't revoke privileges you didn't grant,
even as superuser.

Try:

SET SESSION AUTHORIZATION akretschmer01;
REVOKE ALL ON SCHEMA PUBLIC FROM ak02;
RESET SESSION AUTHORIZATION;
DROP USER ak02;

Yours,
Laurenz Albe

Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> writes:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:19
>> So what does \dn+ public show?

> db115150=# \dn+ public
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+-----------------------------+------------------------
> public | postgres | postgres=UC/postgres +| standard public schema
> | | akretschmer01=U*C*/postgres+|
> | | ak02=UC/akretschmer01 |
> (1 row)

Ah: this shows that you didn't tell us the whole truth to start with.
What you've actually got here is that postgres granted ALL WITH GRANT
OPTION to akretschmer01, and then akretschmer01 used the grant option
to grant rights to ak02. (I was wondering how it was that a non
superuser would be able to grant anything about schema public...)

Only akretschmer01 can directly drop the grant to ak02. What postgres
could do is revoke the grant option to akretschmer01, and the cascaded
effect of that would remove the privileges for ak02.

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.

regards, tom lane

On 02/08/2013 08:14 AM, Tom Lane wrote:
> Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> writes:
>> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:19
>>> So what does \dn+ public show?
>
>> db115150=# \dn+ public
>> List of schemas
>> Name | Owner | Access privileges | Description
>> --------+----------+-----------------------------+------------------------
>> public | postgres | postgres=UC/postgres +| standard public schema
>> | | akretschmer01=U*C*/postgres+|
>> | | ak02=UC/akretschmer01 |
>> (1 row)
>
> Ah: this shows that you didn't tell us the whole truth to start with.
> What you've actually got here is that postgres granted ALL WITH GRANT
> OPTION to akretschmer01, and then akretschmer01 used the grant option
> to grant rights to ak02. (I was wondering how it was that a non
> superuser would be able to grant anything about schema public...)
>
> Only akretschmer01 can directly drop the grant to ak02. What postgres
> could do is revoke the grant option to akretschmer01, and the cascaded
> effect of that would remove the privileges for ak02.
>
> 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:

"
nice idea, but unfortunately no:

db115150=# drop owned by ak02;
DROP OWNED
db115150=# drop user ak02;
FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
DETAIL: Privilegien für Schema public

"

>
> regards, tom lane
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On 02/08/2013 07:57 AM, Andreas Kretschmer wrote:

>> Well I got left is:
>>
>> REVOKE PUBLIC FROM ak02;
>>
>
> i think you mean:
>
> db115150=# revoke all on schema public from ak02;
> REVOKE
> db115150=# drop user ak02;
> FEHLER: kann Rolle »ak02« nicht löschen, weil andere Objekte davon abhängen
> DETAIL: Privilegien für Schema public
>

No I was trying to revoke membership in the PUBLIC role for ak02, on the
chance that ak02 was picking up privileges from it. I do not know if
that is even possible, but at this point I am grasping at straws.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> writes:
> > Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> hat am 8. Februar 2013 um 16:19
> >> So what does \dn+ public show?
>
> > db115150=# \dn+ public
> > List of schemas
> > Name | Owner | Access privileges | Description
> > --------+----------+-----------------------------+------------------------
> > public | postgres | postgres=UC/postgres +| standard public schema
> > | | akretschmer01=U*C*/postgres+|
> > | | ak02=UC/akretschmer01 |
> > (1 row)
>
> Ah: this shows that you didn't tell us the whole truth to start with.
> What you've actually got here is that postgres granted ALL WITH GRANT
> OPTION to akretschmer01, and then akretschmer01 used the grant option
> to grant rights to ak02. (I was wondering how it was that a non
> superuser would be able to grant anything about schema public...)
>
> Only akretschmer01 can directly drop the grant to ak02. What postgres
> could do is revoke the grant option to akretschmer01, and the cascaded
> effect of that would remove the privileges for ak02.
>
> 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.
>
> regards, tom lane

Thanks. A colleague of me (the author of the wiki-artikel) says:

Thanks for your reply.

Sorry to have been unclear, but yes the grants in question
were created by an intermediate admin.

(cf. http://wiki.postgresql.org/wiki/Shared_Database_Hosting, with
DBMAINUSER=akretschmer01 and DBEXTRAUSER=ak02).

Just as a side note, the user that granted the "ALL WITH
GRANT" to akretschmer01 wasn't actually postgres but
an additional supervisor role with a different name, yet
still it says postgres in the \dn+ output.

Anyway, I get that a non-super-user role may only
revoke permissions that it originally granted. But I
am a bit confused about the following paragraph from
the docs:

"
If a superuser chooses to issue a GRANT or REVOKE command,
the command is performed as though it were issued by the owner
of the affected object. Since all privileges ultimately come
from the object owner (possibly indirectly via chains of
grant options), it is possible for a superuser to revoke
all privileges, but this might require use of CASCADE as stated above.
"

http://www.postgresql.org/docs/9.2/static/sql-grant.html

And yes we really only want to get rid of the grants
of the DBEXTRAUSER, the rational in a shared hosting
scenario being that DBMAINUSER might have granted a
plethora of rights to DBEXTRAUSER via the psql,
but still should be able to remove a DBEXTRAUSER at
any time with just a click of a button (in the hosting panel)
*without* affecting the existence of any objects.

Basically we are trying to emulate a command
like

DROP ROLE <X> ALSO REMOVING ALL GRANTS TO ROLE <X>
TO ANY OBJECT IN ANY DATABASE.

(after doing a REASSIGN OWNED BY <X> TO <DBMAINUSER>)

Regards,
Thomas

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On 02/08/2013 09:09 AM, Tom Lane wrote:
> 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.

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(dot)klaver(at)gmail(dot)com

Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> wrote:

> How can i drop a user as SUPERUSER (!) with all privileges?

Okay, i think, it's time to say 'thank you' to all on this list!

It's great, only a few hours and our observation confirmed.

I (we) think, this is a bug. We will make a workaround for us in the
next days, and we will keep you informed about our solution.

Again, many thanks from /me and my colleague, not only to Tom!

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> I am not sure I am following. Are we talking two different cases here?

What I was pointing out was that the non-superuser case seems to be
broken almost completely, whereas the superuser case is only broken
if the object owner has given away some grant options and those have
been exercised.

regards, tom lane

On 02/08/2013 10:09 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
>> I am not sure I am following. Are we talking two different cases here?
>
> What I was pointing out was that the non-superuser case seems to be
> broken almost completely, whereas the superuser case is only broken
> if the object owner has given away some grant options and those have
> been exercised.

Got it, thanks.

>
> regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Tom Lane escribió:

> 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.

So it seems.

I have been mulling this over today, and it seems to me that one
way to fix it would be to have ExecGrant_Objecttype() loop over all
possible grantors when determining what to revoke when it's called by
DROP OWNED. A proof-of-concept is below (RemoveRoleFromObjectACL would
set istmt.all_grantors to true, whereas ExecuteGrantStmt leaves it as
false and behaves as today).

I am not sure about the restrict_and_check_grant() call I left out in
the middle of the operation; it seems to me that if we limit DROP OWNED
to be called only by a superuser, we can get away without that check.
Or maybe we need a new version of that routine that will only apply the
bitmask and not raise any error messages.

The patch below is just for ExecGrant_Relation(), but as far as I can
tell all the ExecGrant_Objecttype() routines do pretty much the same
here, so I think it'd be better to refactor the select_best_grantor/
restrict_and_check_grant/merge_acl_with_grant sequence into a common
function, and then have that function apply the loop for all grantors.

Thoughts?

***************
*** 1891,1932 **** ExecGrant_Relation(InternalGrant *istmt)
AclObjectKind aclkind;

/* Determine ID to do the grant as, and available grant options */
! select_best_grantor(GetUserId(), this_privileges,
! old_acl, ownerId,
! &grantorId, &avail_goptions);
!
! switch (pg_class_tuple->relkind)
{
! case RELKIND_SEQUENCE:
! aclkind = ACL_KIND_SEQUENCE;
! break;
! default:
! aclkind = ACL_KIND_CLASS;
! break;
}

! /*
! * Restrict the privileges to what we can actually grant, and emit
! * the standards-mandated warning and error messages.
! */
! this_privileges =
! restrict_and_check_grant(istmt->is_grant, avail_goptions,
! istmt->all_privs, this_privileges,
! relOid, grantorId, aclkind,
! NameStr(pg_class_tuple->relname),
! 0, NULL);

! /*
! * Generate new ACL.
! */
! new_acl = merge_acl_with_grant(old_acl,
! istmt->is_grant,
! istmt->grant_option,
! istmt->behavior,
! istmt->grantees,
! this_privileges,
! grantorId,
! ownerId);

/*
* We need the members of both old and new ACLs so we can correct
--- 1895,1962 ----
AclObjectKind aclkind;

/* Determine ID to do the grant as, and available grant options */
! if (!istmt->all_grantors)
{
! select_best_grantor(GetUserId(), this_privileges,
! old_acl, ownerId,
! &grantorId, &avail_goptions);
!
! switch (pg_class_tuple->relkind)
! {
! case RELKIND_SEQUENCE:
! aclkind = ACL_KIND_SEQUENCE;
! break;
! default:
! aclkind = ACL_KIND_CLASS;
! break;
! }
!
! /*
! * Restrict the privileges to what we can actually grant, and emit
! * the standards-mandated warning and error messages.
! */
! this_privileges =
! restrict_and_check_grant(istmt->is_grant, avail_goptions,
! istmt->all_privs, this_privileges,
! relOid, grantorId, aclkind,
! NameStr(pg_class_tuple->relname),
! 0, NULL);
!
! /*
! * Generate new ACL.
! */
! new_acl = merge_acl_with_grant(old_acl,
! istmt->is_grant,
! istmt->grant_option,
! istmt->behavior,
! istmt->grantees,
! this_privileges,
! grantorId,
! ownerId);
}
+ else
+ {
+ int ngrantors;
+ Oid *grantors;
+ int i;
+
+ ngrantors = aclgrantors(old_acl, istmt->grantees, &grantors);

! new_acl = aclcopy(old_acl);
! for (i = 0; i < ngrantors; i++)
! {
! Oid grantorId = grantors[i];

! new_acl = merge_acl_with_grant(new_acl,
! istmt->is_grant,
! istmt->grant_option,
! istmt->behavior,
! istmt->grantees,
! this_privileges,
! grantorId,
! ownerId);
! }
! }

/*
* We need the members of both old and new ACLs so we can correct

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

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