Thread: Irrevocable privileges

I think it's silly that any privileges that an owner grants to himself are
essentially irrevocable.  It's silly because it makes changing the object
owner pointless.  Consider:

User joe creates table foo
User joe grants permission rw to himself on foo
User joe decides that user bob should really be the owner of foo
User joe revokes his permissions, alters foo to be owned by bob,
    and gives bob rw privilege
User joe is annoyed to find out that his privileges are in a state of limbo

The only way to fix this is to do a backup/restore.  It seems to me
that owner privs should be revokable in a transaction where the owner
is changed to a different user.  That way, joe doesn't leave behind a
trail of mess when he decides to change ownership of objects.

__
David B. Held
Software Engineer/Array Services Group
200 14th Ave. East,  Sartell, MN 56377
320.534.3637 320.253.7800 800.752.8129

"Dave Held" <dave.held@arraysg.com> 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.

> Consider:

> User joe creates table foo
> User joe grants permission rw to himself on foo
> User joe decides that user bob should really be the owner of foo
> User joe revokes his permissions, alters foo to be owned by bob,=20
>     and gives bob rw privilege
> User joe is annoyed to find out that his privileges are in a state of =
> limbo

Please define "state of limbo".  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.

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?

            regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, May 10, 2005 6:11 PM
> To: Dave Held
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Irrevocable privileges
>
>
> "Dave Held" <dave.held@arraysg.com> 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

"Dave Held" <dave.held@arraysg.com> writes:
> I don't know what a permission of "*" means, so that's what I call "limbo".

It means you still have grant options.  This is fixed in 8.0.

            regards, tom lane