Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ltcce-0004sP-Pp for pgsql-admin@arkaria.postgresql.org; Wed, 16 Jun 2021 20:58:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ltccd-0003OW-PI for pgsql-admin@arkaria.postgresql.org; Wed, 16 Jun 2021 20:58:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ltccd-0003OO-GZ for pgsql-admin@lists.postgresql.org; Wed, 16 Jun 2021 20:58:51 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ltccY-0003gf-Kl for pgsql-admin@lists.postgresql.org; Wed, 16 Jun 2021 20:58:50 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 243B6380574; Wed, 16 Jun 2021 22:58:44 +0200 (CEST) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id AtLrv7TgrM3c; Wed, 16 Jun 2021 22:58:44 +0200 (CEST) Received: from Holger-Moto-G6.fritz.box (p5b248469.dip0.t-ipconnect.de [91.36.132.105]) by rs.plausibolo.de (Postfix) with ESMTPSA id DC730380534; Wed, 16 Jun 2021 22:58:43 +0200 (CEST) Date: Wed, 16 Jun 2021 22:58:41 +0200 User-Agent: K-9 Mail for Android In-Reply-To: References: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----M9M5A97H3VIQ9XJAJTFHFMZ9S9HREY" Content-Transfer-Encoding: 7bit Subject: Re: Baffled by basic permission issue. To: pgsql-admin@lists.postgresql.org From: Holger Jakobs Message-ID: <3AD88617-B7E7-4AF5-AE0B-05A79F88C2CC@jakobs.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------M9M5A97H3VIQ9XJAJTFHFMZ9S9HREY Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable If you want to use a view, you need the permissions for it=2E Not for the u= nderlying tables=20 Am 16=2E Juni 2021 22:56:17 MESZ schrieb Wells Oliver : >I have a view that does this: > >create view stats=2Ev >select * from stats=2Et >join alias=2Ei ON t=2Eid =3D i=2Eid >join alias=2Ei2 ON t=2Eid2 =3D i2=2Eid; > >And when I do: SELECT * FROM stats=2Ev I receive: *ERROR: permission >denied >for table i* > >However, if I paste the contents of the view as a query and run it >manually, it works fine=2E > >I can also query alias=2Ei as I please=2E > >The role I am using to connect has USAGE on both stats and alias, it >also >has SELECT on the table as well as the view=2E > >The only difference is that the view is owned by a different user than >the >tables, though the SELECT permissions are definitely granted=2E > >psql 13=2E2 > >--=20 >Wells Oliver >wells=2Eoliver@gmail=2Ecom --=20 Holger Jakobs, Bergisch Gladbach +49 178 9759012 - sent from mobile, therefore short - ------M9M5A97H3VIQ9XJAJTFHFMZ9S9HREY Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable If you want to use a view, you need the permission= s for it=2E Not for the underlying tables

Am 16=2E Juni 2021 22:56:17 MESZ schrieb Wells Oliver <wells=2Eoliver@= gmail=2Ecom>:
I have a view that does this:

create v= iew stats=2Ev
select * from stats=2Et
join alias=2Ei ON t=2Eid =3D i=2Eid
= join alias=2Ei2 ON t=2Eid2 =3D i2=2Eid;

And when I do: SELECT * FROM stats=2Ev I receive: E= RROR:  permission denied for table i

Howe= ver, if I paste the contents of the view as a query and run it manually, it= works fine=2E

I can also query alias=2Ei as I ple= ase=2E

The role I am using to connect has USAGE on= both stats and alias, it also has SELECT on the table as well as the = view=2E

The only difference is that the view is ow= ned by a different user than the tables, though the SELECT permissions are = definitely granted=2E

psql 13=2E2
<= div>
--

--
Holger Jakobs, Bergisch Gladbach
+49 178 = 9759012
- sent from mobile, therefore short -
------M9M5A97H3VIQ9XJAJTFHFMZ9S9HREY--