Обсуждение: permission denied (even when run as postgres) for views after makingtheir owner nosuperuser

Hello,
I have a strange issue here, demonstrated by the below script, run as postgres (superuser), problem is in both 9.3 and
10.0:
 

dynacom=# create table testforfu (id serial, descr name);
CREATE TABLE
dynacom=# insert into testforfu (descr) values('bar');
INSERT 0 1
dynacom=# CREATE ROLE fuser;
CREATE ROLE
dynacom=# ALTER ROLE fuser WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
ALTER ROLE
dynacom=# create schema fuser;
CREATE SCHEMA
dynacom=# alter schema fuser owner to fuser ;
ALTER SCHEMA
dynacom=# SET search_path = fuser,pg_catalog;
SET
dynacom=# create view fuser.testforfu as select * from public.testforfu;
CREATE VIEW
dynacom=# alter view fuser.testforfu owner to fuser ;
ALTER VIEW
dynacom=# create table fuser.testforfutbl (descr TEXT);
CREATE TABLE
dynacom=# alter table fuser.testforfutbl owner to fuser ;
ALTER TABLE
dynacom=# select * from fuser.testforfu ;
  id | descr
----+-------
   1 | bar
(1 row)

dynacom=# alter user fuser nosuperuser ;
ALTER ROLE
dynacom=# select * from fuser.testforfutbl ;
  descr
-------
(0 rows)

dynacom=# select * from fuser.testforfu ;
ERROR:  permission denied for relation testforfu

So the select on the table works, but not on the select on the view. If I remake fuser as superuser then the select
worksok :
 

alter user fuser superuser ;
ALTER ROLE
dynacom=# select * from fuser.testforfu ;
  id | descr
----+-------
   1 | bar
(1 row)

In fact, there is no way on earth I can regain access to view fuser.testforfu for any user (postgres included) unless I
changeits ownership (or make fuser a superuser).
 

is this normal? documented? Am I missing anything?

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

On Fri, Jan 26, 2018 at 7:32 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

In fact, there is no way on earth I can regain access to view fuser.testforfu for any user (postgres included) unless I change its ownership (or make fuser a superuser).

is this normal? documented? Am I missing anything?

​When dealing with views the owner of the view is performing all of the queries.  So if you make the view owner lack permission to access the underlying tables the view isn't going to work no matter the permissions of the view caller.

Documented here:

https://www.postgresql.org/docs/10/static/sql-createview.html

"Access to tables referenced in the view is determined by permissions of the view owner."

David J.

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt