permission denied (even when run as postgres) for views after making their owner nosuperuser

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: permission denied (even when run as postgres) for views after making their owner nosuperuser
Date: 2018-01-26 14:32:36
Message-ID: b09b3f84-2ffd-2861-161c-55c02158b340@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2018-01-26 14:42:14 Re: permission denied (even when run as postgres) for views after making their owner nosuperuser
Previous Message Mark Kirkwood 2018-01-26 04:15:39 Re: OOM Killing on Docker while ANALYZE running