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
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 |