Re: Bug, Feature, or what else?

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°