Re: Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Hmm. I'm not sure, but that may have been a case where it was generally
> > decided that the spec was somewhat braindead in this fashion (it seems
> > so in my personal view of this, honestly...). To issue a revoke and
> > have it not work would be kind of concerning. If we do end up following
> > this path we should emit a warning (at least...) if the user still has
> > the rights which are being revoked, even if through someone else.
>
> That's not how it works for rights on ordinary objects.

Not quite sure which bit you're referring to here.. On 8.1, at least,
we ignore a grant which has a matching right and target:

sfrost=> set role u1;
sfrost=> \dp
Access privileges for database "sfrost"
Schema | Name | Type | Access privileges
--------+------+-------+-------------------------
sfrost | test | table | {u1=arwdRxt/u1,u3=r/u1}
(1 row)

sfrost=> reset role;
RESET
sfrost=> set role u2;
SET
sfrost=> grant select on test to u3;
GRANT
sfrost=> \dp
Access privileges for database "sfrost"
Schema | Name | Type | Access privileges
--------+------+-------+-------------------------
sfrost | test | table | {u1=arwdRxt/u1,u3=r/u1}
(1 row)

Additionally, any user with ownership rights on the table in question
can revoke the rights of a user. Still as u2:

sfrost=> revoke select on test from u3;
REVOKE
sfrost=> \dp
Access privileges for database "sfrost"
Schema | Name | Type | Access privileges
--------+------+-------+-------------------
sfrost | test | table | {u1=arwdRxt/u1}
(1 row)

If you're saying we don't currently warn if a revoke leaves the
priviledges in-tact for the right and target, I'm not sure you can
currently get in a state where it'd be possible to run into that.
Either you have the rights to remove the grant on the object
(you're an 'owner' of it), in which case the grant will be removed
if it exists (based on the right and target, regardless of who
granted it), or you don't, in which case you get a permission denied
ERROR outright. If regular object permissions were ever changed to
require the grantor to be the revoker, I would want a warning in the
case described for regular objects as well.

If you're saying we don't currently require that the grantor be the
revoker on regular objects, I would agree. :)

Thanks,

Stephen