> -----Original Message----- > From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx] > Sent: Tuesday, May 10, 2005 6:11 PM > To: Dave Held > Cc: pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] Irrevocable privileges > > > "Dave Held" <dave.held@xxxxxxxxxxx> 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
