Re: Bug, Feature, or what else?

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