Re: Bug, Feature, or what else?

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