To: pgsql-admin@postgresql.org
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Subject: permission denied (even when run as postgres) for views after making
 their owner nosuperuser
Message-ID: <b09b3f84-2ffd-2861-161c-55c02158b340@matrix.gatewaynet.com>
Date: Fri, 26 Jan 2018 16:32:36 +0200
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101
 Thunderbird/52.5.2
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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 works ok :

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 change its ownership (or make fuser a superuser).

is this normal? documented? Am I missing anything?

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