Thread: view

On 09/08/2015 05:19 AM, Ramesh T wrote:
> HI ,
>          I have view .when i try to select view ,it had return
>   select * from art;
> ERROR:  permission denied for relation sub_item

Pretty self-explanatory, the user running the query does not have rights
on sub_item, which I presume is part of the view.

http://www.postgresql.org/docs/9.4/interactive/sql-createview.html

"Access to tables referenced in the view is determined by permissions of
the view owner. In some cases, this can be used to provide secure but
restricted access to the underlying tables.
..."

> SQL state: 42501


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/08/2015 06:44 AM, Melvin Davidson wrote:
>  >ERROR:  permission denied for relation sub_item
>
> So obviously, the user doing the select on the view (ie: YOU)  does not
> have SELECT permission on table sub_item.

It is more subtle then that:

test=# select current_user;
  current_user
--------------
  postgres


test=# create table sub_item (id int, fld_1 varchar);

test=> \c - aklaver

test=> select current_user;
  current_user
--------------
  aklaver

test=> create view sub_view as select * from sub_item;
CREATE VIEW


public | sub_item           | table | postgres
public | sub_view           | view  | aklaver


test=> select * from sub_view ;
ERROR:  permission denied for relation sub_item

test=> \c - postgres


test=# select current_user;
  current_user
--------------
  postgres


test=# select * from sub_view ;
ERROR:  permission denied for relation sub_item

test=# alter  view sub_view owner to postgres;
ALTER VIEW

test=# select * from sub_view ;
  id | fld_1
----+-------
(0 rows)


It is the view owner that determines the permissions of what can be
selected. The catch is it is possible to create a view with a query it
cannot run as the user it is created by.

>
> On Tue, Sep 8, 2015 at 8:19 AM, Ramesh T <rameshparnanditech@gmail.com
> <mailto:rameshparnanditech@gmail.com>> wrote:
>
>     HI ,
>              I have view .when i try to select view ,it had return
>       select * from art;
>     ERROR:  permission denied for relation sub_item
>     SQL state: 42501
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com