Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ef5Tu-00015G-8P for pgsql-admin@arkaria.postgresql.org; Fri, 26 Jan 2018 14:59:54 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ef5Tt-0003jd-DU for pgsql-admin@arkaria.postgresql.org; Fri, 26 Jan 2018 14:59:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ef5Tt-0003jU-3O for pgsql-admin@lists.postgresql.org; Fri, 26 Jan 2018 14:59:53 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ef5Tp-0001nO-TN for pgsql-admin@lists.postgresql.org; Fri, 26 Jan 2018 14:59:51 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id w0QExkBw003398 for ; Fri, 26 Jan 2018 16:59:47 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: permission denied (even when run as postgres) for views after making their owner nosuperuser To: pgsql-admin@lists.postgresql.org References: From: Achilleas Mantzios Message-ID: <3b4e1884-e2df-0152-e258-8adb984a2252@matrix.gatewaynet.com> Date: Fri, 26 Jan 2018 16:59:46 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.5.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9FBDFF7BFAFF0FFF5E75C246" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------9FBDFF7BFAFF0FFF5E75C246 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 26/01/2018 16:42, David G. Johnston wrote: > On Fri, Jan 26, 2018 at 7:32 AM, Achilleas Mantzios >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 --------------9FBDFF7BFAFF0FFF5E75C246 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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> 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
--------------9FBDFF7BFAFF0FFF5E75C246--