Re: view

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