Обсуждение: BUG #6728: revoke grant cascade behaviour

The following bug has been logged on the website:

Bug reference:      6728
Logged by:          daniel german
Email address:      dmg@uvic.ca
PostgreSQL version: 9.1.4
Operating system:   Linux (Ubuntu)
Description:=20=20=20=20=20=20=20=20

Hi there,

I am trying to understand how postgresql implements REVOKE GRANT ...
CASCADE

First the scenario:

We have the default user, and five roles (a, b, m, x, y). user belongs to
all.

Consider the following code:


set session role none;
drop table sailors;
create table sailors (a int);
select * from user;
grant select on sailors to a with grant option;
grant select on sailors to b with grant option;
set session role b;
grant select on sailors to m with grant option;
set session role a;
grant select on sailors to m with grant option;
set session role none;
\dp
set session role m;
grant select on sailors to x;
grant select on sailors to y;
set session role none;
select * from information_schema.column_privileges where table_name =3D
'sailors' and column_name =3D 'a';
\dp
set session role a;
revoke select on sailors from m cascade;
set session role none;
select * from information_schema.column_privileges where table_name =3D
'sailors' and column_name =3D 'a';
\dp

What I am surprised is that m keeps the proviledge (via b) but x and y have
lost it. See below). is that the way it is supposed to be?

thank you for your time,

--daniel

temp=3D# select * from information_schema.column_privileges where table_nam=
e =3D
'sailors' and column_name =3D 'a';
 grantor | grantee | table_catalog | table_schema | table_name | column_name
| privilege_type | is_grantable=20
---------+---------+---------------+--------------+------------+-----------=
--+----------------+--------------
 dmg     | dmg     | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| UPDATE         | YES
 dmg     | dmg     | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| SELECT         | YES
 dmg     | dmg     | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| REFERENCES     | YES
 dmg     | dmg     | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| INSERT         | YES
 dmg     | a       | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| SELECT         | YES
 dmg     | b       | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| SELECT         | YES
 b       | m       | temp          | public       | sailors    | a=20=20=20=
=20=20=20=20=20=20=20
| SELECT         | YES

dmg@uvic.ca writes:
> I am trying to understand how postgresql implements REVOKE GRANT ...
> CASCADE

> [ example snipped ]

> What I am surprised is that m keeps the proviledge (via b) but x and y have
> lost it. See below). is that the way it is supposed to be?

It is not.  There is actually code in there that is supposed to deal
with this consideration, but it's got a trivial bug :-(.  Will fix,
and add a regression test example too.  Thanks for the report!

            regards, tom lane

diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 77322a115f1f7b36e08541ae6ab5e3fd873aea5b..cf04c420d9808a46828783489536932cc3530f05 100644
*** a/src/backend/utils/adt/acl.c
--- b/src/backend/utils/adt/acl.c
*************** recursive_revoke(Acl *acl,
*** 1230,1240 ****
      if (grantee == ownerId)
          return acl;

!     /* The grantee might still have the privileges via another grantor */
      still_has = aclmask(acl, grantee, ownerId,
                          ACL_GRANT_OPTION_FOR(revoke_privs),
                          ACLMASK_ALL);
!     revoke_privs &= ~still_has;
      if (revoke_privs == ACL_NO_RIGHTS)
          return acl;

--- 1230,1240 ----
      if (grantee == ownerId)
          return acl;

!     /* The grantee might still have the grant options via another grantor */
      still_has = aclmask(acl, grantee, ownerId,
                          ACL_GRANT_OPTION_FOR(revoke_privs),
                          ACLMASK_ALL);
!     revoke_privs &= ~ACL_OPTION_TO_PRIVS(still_has);
      if (revoke_privs == ACL_NO_RIGHTS)
          return acl;