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