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

BUG #6728: revoke grant cascade behaviour

From: dmg(at)uvic(dot)ca
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6728: revoke grant cascade behaviour
Date: 2012-07-11 18:39:05
Message-ID: E1Sp1oX-0004oR-6M@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6728
Logged by:          daniel german
Email address:      dmg(at)uvic(dot)ca
PostgreSQL version: 9.1.4
Operating system:   Linux (Ubuntu)
Description:        

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 =
'sailors' and column_name = '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 =
'sailors' and column_name = '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=# select * from information_schema.column_privileges where table_name =
'sailors' and column_name = 'a';
 grantor | grantee | table_catalog | table_schema | table_name | column_name
| privilege_type | is_grantable 
---------+---------+---------------+--------------+------------+-------------+----------------+--------------
 dmg     | dmg     | temp          | public       | sailors    | a          
| UPDATE         | YES
 dmg     | dmg     | temp          | public       | sailors    | a          
| SELECT         | YES
 dmg     | dmg     | temp          | public       | sailors    | a          
| REFERENCES     | YES
 dmg     | dmg     | temp          | public       | sailors    | a          
| INSERT         | YES
 dmg     | a       | temp          | public       | sailors    | a          
| SELECT         | YES
 dmg     | b       | temp          | public       | sailors    | a          
| SELECT         | YES
 b       | m       | temp          | public       | sailors    | a          
| SELECT         | YES




Responses

pgsql-bugs by date

Next:From: ysharmaDate: 2012-07-11 23:16:14
Subject: BUG #6729: Install of postgresql-devel for x86_84 and i686 fails
Previous:From: Patrick TuckerDate: 2012-07-11 18:24:09
Subject: Re: BUG #6727: Column does not exist exception has no error code

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