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
