Bug, Feature, or what else?

2013/2/8 Andreas Kretschmer <andreas@a-kretschmer.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&gt;

and DROP OWNED<http://www.postgresql.org/docs/current/interactive/sql-drop-owned.html&gt;
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

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

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.klaver@gmail.com

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

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

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

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=#

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

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.klaver@gmail.com

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

Adrian Klaver <adrian.klaver@gmail.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)

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

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

Adrian Klaver <adrian.klaver@gmail.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.klaver@gmail.com

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

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

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

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.klaver@gmail.com

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

Adrian Klaver <adrian.klaver@gmail.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

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

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

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

Andreas Kretschmer <andreas@a-kretschmer.de> writes:

Adrian Klaver <adrian.klaver@gmail.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

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

On 02/08/2013 08:14 AM, Tom Lane wrote:

Andreas Kretschmer <andreas@a-kretschmer.de> writes:

Adrian Klaver <adrian.klaver@gmail.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.klaver@gmail.com

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

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.klaver@gmail.com

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

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.

regards, tom lane

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

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Andreas Kretschmer <andreas@a-kretschmer.de> writes:

Adrian Klaver <adrian.klaver@gmail.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�

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

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

Andreas Kretschmer <andreas@a-kretschmer.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�

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