view

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general