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

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jeff Davis <pgsql(at)j-davis(dot)com>, pgsql-bugs(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Date: 2007-05-17 22:16:50
Message-ID: 200705172216.l4HMGoK26494@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Is there a TODO here?

---------------------------------------------------------------------------

Russell Smith wrote:
> Alvaro Herrera wrote:
> > Russell Smith wrote:
> >   
> >> Alvaro Herrera wrote:
> >>     
> >>> Alvaro Herrera wrote:
> >>>
> >>>  
> >>>       
> >>>> 2. decide that the standard is braindead and just omit dumping the
> >>>>   grantor when it's no longer available, but don't remove
> >>>>   pg_auth_members.grantor
> >>>>
> >>>> Which do people feel should be implemented?  I can do whatever we
> >>>> decide; if no one has a strong opinion on the matter, my opinion is we
> >>>> do (2) which is the easiest.
> >>>>         
> >>> Here is a patch implementing this idea, vaguely based on Russell's.
> >>>       
> >> I haven't had time to finalize my research about this, but the admin 
> >> option with revoke doesn't appear to work as expected.
> >>
> >> Here is my sample SQL for 8.2.4
> >>
> >> create table test (x integer);
> >> \z
> >> create role test1 noinherit;
> >> create role test2 noinherit;
> >> grant select on test to test1 with grant option;
> >> grant select on test to test2;
> >> \z test
> >> set role test1;
> >> revoke select on test from test2;
> >> \z test
> >> set role test2;
> >> select * from test;
> >> reset role;
> >> revoke all on test from test2;
> >> revoke all on test from test1;
> >> drop role test2;
> >> drop role test1;
> >> drop table test;
> >> \q
> >>
> >>
> >> The privilege doesn't appear to be revoked by test1 from test2.  I'm not 
> >> sure if this is related, but I wanted to bring it up in light of the 
> >> options we have for grantor.
> >>     
> >
> > Humm, but the privilege was not granted by test1, but by the user you
> > were using initially.  The docs state in a note that
> >
> > 	A user can only revoke privileges that were granted directly by
> > 	that user.
> >
> > I understand that this would apply to the grantor stuff being discussed
> > in this thread as well, but I haven't seen anyone arguing that we should
> > implement that for GRANT ROLE (and I asked three times if people felt it
> > was important and nobody answered).
> >
> >   
> Well, I would vote for implementing this in GRANT ROLE.  I wish to use 
> it in my security model.  I don't think the spec is brain dead when you 
> understand what it's trying to achieve.
> 
> Example:
> 
> 2 Groups of administrators who are allowed to grant a role to users of 
> the system
> 
> App_Admin_G1
> App_Admin_G2
> App_User
> 
> SET ROLE App_Admin_G1
> GRANT App_User TO "Fred";
> SET ROLE App_Admin_G2
> GRANT App_User TO "John";
> SET ROLE App_Admin_G1
> REVOKE App_User FROM "John";
> 
> As App_Admin_G1 did not grant App_User rights to John, he should not be 
> able to take them away.
> 
> I currently have a situation where I would like to be able to do the 
> above.  I have two separate departments who might grant privileges for 
> the same application to the same user.  One department administrator 
> should not be able to revoke the privileges set by the other one.
> 
> I would expect superusers to be able to revoke from anybody, or the 
> "owner".  I'm not sure what the owner is when we talk about granting roles.
> 
> Regards
> 
> Russell Smith
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2007-05-17 22:20:27
Subject: Re: Updated bitmap index patch
Previous:From: Tom LaneDate: 2007-05-17 22:14:29
Subject: Re: [COMMITTERS] pgsql: Fix parameter recalculation for Limit nodes: during a ReScan call

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2007-05-17 22:22:02
Subject: Re: [HACKERS] Removing pg_auth_members.grantor (was Grantor name gets lost when grantor role dropped)
Previous:From: Tom LaneDate: 2007-05-17 21:28:50
Subject: Re: strange problem with ip6

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