> -----Original Message-----
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, May 10, 2005 6:11 PM
> To: Dave Held
> Cc: [email protected]
> Subject: Re: [ADMIN] Irrevocable privileges
>
>
> "Dave Held" <[EMAIL PROTECTED]> writes:
> > I think it's silly that any privileges that an owner grants
> to himself =
> > are essentially irrevocable.
>
> Say again? An owner can certainly revoke his own ordinary privileges.
Not completely.
> [...]
> Please define "state of limbo".
CREATE TABLE foo ();
GRANT ALL ON foo TO postgres;
REVOKE ALL ON foo FROM postgres;
\z foo
Access privileges for database "production"
Schema | Table | Access privileges
--------+-------+-----------------------------
public | foo | {postgres=*******/postgres}
(1 row)
I don't know what a permission of "*" means, so that's what I call "limbo".
While this isn't a very useful thing to be able to do, the following is:
CREATE TABLE foo ();
GRANT ALL ON foo TO postgres;
BEGIN;
REVOKE ALL ON foo FROM postgres;
ALTER TABLE foo OWNER TO joeuser;
GRANT ALL ON foo TO joeuser;
COMMIT;
Essentially, this is an attempt to change ownership from postgres to
joeuser, without leaving any residual permissions from postgres on the
table.
> Also note that if user joe is able to do "ALTER OWNER" then he must be a
> superuser, and hence not subject to access controls in the first place.
Ah, except for this blurb in the documentation:
"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. In particular, privileges
granted via such a command will appear to have been granted
by the object owner."
http://www.postgresql.org/docs/7.4/static/sql-grant.html
So whether I'm joe or whether I'm postgres (which is superuser on my db),
I can't revoke postgres' permissions. That's because if postgres is the
owner, it can't revoke its own permissions, and if joe is the owner, it
attempts to revoke *as joe*, which also fails, *even if joe is a superuser*,
because joe didn't grant the permissions to begin with.
> I do recall that we recently (probably in 8.0) fixed some issues with
> what ALTER OWNER does with existing privileges. What version are you
> testing?
7.4
__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East, Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
