Re: Irrevocable privileges

> -----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