pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpermission denied (even when run as postgres) for views after making their owner nosuperuser
3+ messages / 2 participants
[nested] [flat]
* permission denied (even when run as postgres) for views after making their owner nosuperuser
@ 2018-01-26 14:32 Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-01-26 14:42 ` Re: permission denied (even when run as postgres) for views after making their owner nosuperuser David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleas Mantzios @ 2018-01-26 14:32 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: permission denied (even when run as postgres) for views after making their owner nosuperuser
2018-01-26 14:32 permission denied (even when run as postgres) for views after making their owner nosuperuser Achilleas Mantzios <achill@matrix.gatewaynet.com>
@ 2018-01-26 14:42 ` David G. Johnston <david.g.johnston@gmail.com>
2018-01-26 14:59 ` Re: permission denied (even when run as postgres) for views after making their owner nosuperuser Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2018-01-26 14:42 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-admin
On Fri, Jan 26, 2018 at 7:32 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
> 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?
When dealing with views the owner of the view is performing all of the
queries. So if you make the view owner lack permission to access the
underlying tables the view isn't going to work no matter the permissions of
the view caller.
Documented here:
https://www.postgresql.org/docs/10/static/sql-createview.html
"Access to tables referenced in the view is determined by permissions of
the view owner."
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: permission denied (even when run as postgres) for views after making their owner nosuperuser
2018-01-26 14:32 permission denied (even when run as postgres) for views after making their owner nosuperuser Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-01-26 14:42 ` Re: permission denied (even when run as postgres) for views after making their owner nosuperuser David G. Johnston <david.g.johnston@gmail.com>
@ 2018-01-26 14:59 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Achilleas Mantzios @ 2018-01-26 14:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 26/01/2018 16:42, David G. Johnston wrote:
> On Fri, Jan 26, 2018 at 7:32 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>>wrote:
>
> 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?
>
>
> When dealing with views the owner of the view is performing all of the queries. So if you make the view owner lack permission to access the underlying tables the view isn't going to work no matter
> the permissions of the view caller.
Thanks a lot!
>
> Documented here:
>
> https://www.postgresql.org/docs/10/static/sql-createview.html
>
> "Access to tables referenced in the view is determined by permissions of the view owner."
>
> David J.
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2018-01-26 14:59 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-26 14:32 permission denied (even when run as postgres) for views after making their owner nosuperuser Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-01-26 14:42 ` David G. Johnston <david.g.johnston@gmail.com>
2018-01-26 14:59 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
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