From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-admin@postgresql.org
Subject: permission denied (even when run as postgres) for views after making their owner nosuperuser
Date: Fri, 26 Jan 2018 16:32:36 +0200
Message-ID: <b09b3f84-2ffd-2861-161c-55c02158b340@matrix.gatewaynet.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: permission denied (even when run as postgres) for views after making their owner nosuperuser
In-Reply-To: <b09b3f84-2ffd-2861-161c-55c02158b340@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox