Thread: view
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
>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.On Tue, Sep 8, 2015 at 8:19 AM, Ramesh T <rameshparnanditech@gmail.com> wrote:
HI ,I have view .when i try to select view ,it had returnselect * from art;ERROR: permission denied for relation sub_itemSQL state: 42501
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
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