From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-general@postgresql.org
Subject: Re: Bug, Feature, or what else?
Date: Fri, 8 Feb 2013 18:37:45 +0100
Message-ID: <20130208173745.GA7983@tux> (raw)
In-Reply-To: <17143.1360340065@sss.pgh.pa.us>
References: <119745304.24731.1360333559327.JavaMail.open-xchange@ox.ims-firmen.de>
<51151128.8040807@gmail.com>
<1568445490.24920.1360335539088.JavaMail.open-xchange@ox.ims-firmen.de>
<51151764.4090405@gmail.com>
<609013460.25018.1360337038276.JavaMail.open-xchange@ox.ims-firmen.de>
<17143.1360340065@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: akretschmer@spamfence.net, pgsql-general@postgresql.org
Subject: Re: Bug, Feature, or what else?
In-Reply-To: <20130208173745.GA7983@tux>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox