Thread: view permissions problem - featuer or bug?

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
----------------------------------------------------------------

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/

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/
>

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