Skip site navigation (1) Skip section navigation (2)

Re: Irrevocable privileges

From: "Dave Held" <dave(dot)held(at)arraysg(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Irrevocable privileges
Date: 2005-05-11 13:59:58
Message-ID: 49E94D0CFCD4DB43AFBA928DDD20C8F902618507@asg002.asg.local (view raw or flat)
Thread:
Lists: pgsql-admin
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, May 10, 2005 6:11 PM
> To: Dave Held
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] Irrevocable privileges
> 
> 
> "Dave Held" <dave(dot)held(at)arraysg(dot)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

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2005-05-11 14:59:30
Subject: Re: Irrevocable privileges
Previous:From: Chris TraversDate: 2005-05-11 01:22:23
Subject: Re: when to modify..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group