Thread

1. view permissions problem - featuer or bug?

   Travis Bauer <trbauer@indiana.edu> — 2000-07-07T16:27:08Z

   I have a set of tables and some views which perform calculations on
   those table to which no one except the group officestaff has
   any permissions.  No problem.
   
   I tried to create views to which some particular client would have
   permissions.  These views would be filtered versions of the private views.
   Herein lies the problem.  If any of those underlying veiws call functions
   that access tables to which the user does not have permissions, or if any
   of those underlying views have sub-select statements (such as "where x in
   (select . . . )") the user gets access denied errors.
   
   Is this a feature or a bug?  On the one hand, it certainly provides tight
   security.  However, it seems like if you give someone permissions on a
   view, that view ought to be allows to perform whatever it needs to get the
   data back out regardless of other underlying permissions.
   
   ----------------------------------------------------------------
   Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
   ----------------------------------------------------------------
   
   
   

2. Re: view permissions problem - featuer or bug?

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-07-08T15:02:40Z

   Travis Bauer wrote:
   > 
   > I have a set of tables and some views which perform calculations on
   > those table to which no one except the group officestaff has
   > any permissions.  No problem.
   > 
   > I tried to create views to which some particular client would have
   > permissions.  These views would be filtered versions of the private views.
   > Herein lies the problem.  If any of those underlying veiws call functions
   > that access tables to which the user does not have permissions, or if any
   > of those underlying views have sub-select statements (such as "where x in
   > (select . . . )") the user gets access denied errors.
   > 
   > Is this a feature or a bug?  On the one hand, it certainly provides tight
   > security.  However, it seems like if you give someone permissions on a
   > view, that view ought to be allows to perform whatever it needs to get the
   > data back out regardless of other underlying permissions.
   
   At least the way it's supposed ot work is that the view is executed
   as if by the owner of the view. For example:
   
   table big_customer_db is owned by user "master"
   you create a view "active_customer_list" also owned by "master"
   now, if you grant someone access to the view, they will be
   able to get the data in it, even though they can't query
   big_customer_db directly.
   
   Is this what you want?
   
   HTH,
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/
   
   

3. Re: view permissions problem - featuer or bug?

   Travis Bauer <trbauer@indiana.edu> — 2000-07-08T20:09:36Z

   That part works okay, but here is a more complicated situation that
   doesn't:
   
   create user user1;
   create table t1 (x int, y int);
   create table t2 (a int, y int);
   create view v1 as select * from t1 where x in (select a from t2);
                                                 ------------------
   create view v2 as select * from v1 where x>3;
   
   revoke all on t1 from public;
   revoke all on t2 from public;
   revoke all on v1 from public;
   revoke all on v2 from public;
   grant select on v2 to user1;
   
   \c - user1
   
   select * from v2;
   
   ====> v1: Permission denied.
   
   The problem occurs because of the nested select underlined above.  Since
   user1 has permissions on v2, he should be able to execute v2's select
   statement.  V2 accesses another view v1.  V1 accesses t1, which causes no
   problems since t1 is a table.  But if v1 has a nested select statement (or
   calls a function which executes a select statement), you get a permission
   denied error.  
   
   The workaround I did was to create a bunch of extra subview which user1
   has access to.  This works, but seems like the above scenerio shouldn't be
   causes the permission violation. 
   
   ----------------------------------------------------------------
   Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
   ----------------------------------------------------------------
   
   > 
   > table big_customer_db is owned by user "master"
   > you create a view "active_customer_list" also owned by "master"
   > now, if you grant someone access to the view, they will be
   > able to get the data in it, even though they can't query
   > big_customer_db directly.
   > 
   > Is this what you want?
   > 
   > HTH,
   > -- 
   > Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   > http://cupid.suninternet.com/~kleptog/
   > 
   
   
   

4. Re: view permissions problem - featuer or bug?

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-09T00:34:18Z

   Travis Bauer <trbauer@indiana.edu> writes:
   > That part works okay, but here is a more complicated situation that
   > doesn't:
   
   > create user user1;
   > create table t1 (x int, y int);
   > create table t2 (a int, y int);
   > create view v1 as select * from t1 where x in (select a from t2);
   >                                               ------------------
   > create view v2 as select * from v1 where x>3;
   
   > revoke all on t1 from public;
   > revoke all on t2 from public;
   > revoke all on v1 from public;
   > revoke all on v2 from public;
   > grant select on v2 to user1;
   
   > \c - user1
   
   > select * from v2;
   
   > ====> v1: Permission denied.
   
   > The problem occurs because of the nested select underlined above.
   
   I agree, this is a bug.  The rewriter checks access permissions on the
   basis of the rule's creator, not of the rule's invoker --- but it only
   does so at the top level of the rule query.  It forgets to recurse into
   subqueries :-(.  Fairly easy fix, will work on it for 7.1.
   
   			regards, tom lane