Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soboF-00CUMq-3H for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 04:52:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1soboE-00FLUv-Qm for pgsql-general@arkaria.postgresql.org; Thu, 12 Sep 2024 04:51:58 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soboD-00FLUn-TT for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 04:51:58 +0000 Received: from outbound.visena.net ([46.226.12.34]) by magus.postgresql.org with utf8esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sobo9-000l3l-UO for pgsql-general@lists.postgresql.org; Thu, 12 Sep 2024 04:51:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:References:In-Reply-To: Message-ID:Cc:To:From:Date:Sender:Reply-To:Content-Transfer-Encoding: Content-ID:Content-Description; bh=U+LT61a0ulHu54rBGQcnY2me+NIJTVGgwhw5Y2ebqG4=; b=IwrBnAtDG2Oa1SxLW5L7UOSUmw 7V8cYpoeeM5nhvoJvp/KS+pL6thMn5HnVGTwCtmhnEW0N70UPeemPcHunIiGATsdkC4rSFksV8AyF gnmCFXLhgUQHe0T0F3IqIDMVLrR57Iehiwqbmthj0M04qWWKlcbjF2VpFBWTRko0Qx5Y=; Received: from batch01.services.internal.visena.net ([10.3.0.103]) by outbound.visena.net with utf8esmtp (Exim 4.93) (envelope-from ) id 1soboA-002FKc-Oe; Thu, 12 Sep 2024 06:51:54 +0200 Date: Thu, 12 Sep 2024 06:51:54 +0200 (CEST) From: Andreas Joseph Krogh To: Tom Lane Cc: pgsql-general@lists.postgresql.org Message-ID: In-Reply-To: <3952715.1726115805@sss.pgh.pa.us> References: <3952715.1726115805@sss.pgh.pa.us> Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_58056_340086286.1726116714662" X-Mailer: Visena Mail 3.2.747 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_58056_340086286.1726116714662 Content-Type: multipart/related; boundary="----=_Part_58057_363453689.1726116714662" ------=_Part_58057_363453689.1726116714662 Content-Type: multipart/alternative; boundary="----=_Part_58058_465666639.1726116714682" ------=_Part_58058_465666639.1726116714682 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks for info. I know PG is not designed for this, but I have this requirement nonetheless= =E2=80=A6 I think preventing =E2=80=9Cmost users and tools" from seeing/presenting th= is=20 information is =E2=80=9Cgood enough=E2=80=9D. Maybe not revoking access to all the tables in the schemas, but the =E2=80= =9Cmost=20 obvious ones=E2=80=9D, like pg_user, pg_shadow, pg_tables etc. will suffice= . If read-access (SELECT) on views in public-schema will still works, and=20 pg_dump/restore etc. also works, this sounds like a solution to me. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com P=C3=A5 torsdag 12. september 2024 kl. 06:36:45, skrev Tom Lane >: Andreas Joseph Krogh writes: > Motivation: I have PowerBI users, with a separate =E2=80=98reporting=E2= =80=99-role, accessing > a database and I want to prevent them from listing all tables, users,=20 databases > and view-definitions (to not see the underlying query). Postgres is not designed to support this requirement. > I'm evaluating this: > REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELE= CT=20 ON > ALL TABLES IN SCHEMA information_schema FROM PUBLIC; > Will this affect =E2=80=9Cnormal behaviour=E2=80=9D, ie. prevent the plan= ner, or other=20 > internal mechanisms, from working properly for sessions logged in with th= e=20 > =E2=80=98reporting=E2=80=99-role? Probably 95% of that stuff will still work. By the same token, there are plenty of information-leaking code pathways that will still leak. For instance, your restricted user will have no trouble discovering the OIDs and names of all extant tables, using something like do $$ begin for tid in 1..1000000 loop if tid::regclass::text !=3D tid::text then raise notice 'tid % is %', tid, tid::regclass; end if; end loop; end $$; Functions such as pg_describe_object still work fine, too. Experimenting with psql, a lot of stuff is broken as expected: busted=3D> \d mytable ERROR: permission denied for table pg_class but some things still work: busted=3D> \sf sin CREATE OR REPLACE FUNCTION pg_catalog.sin(double precision) RETURNS double precision LANGUAGE internal IMMUTABLE PARALLEL SAFE STRICT AS $function$dsin$function$ This is pretty much the other side of the same coin. The reason you can still parse and plan a query is that it does not occur to large parts of the backend that there should be any reason to refuse to read a system catalog. That carries over to these operations as well. This recent thread might be enlightening: https://www.postgresql.org/message-id/flat/18604-04d64b68e981ced6%40postgre= sql.org If you have a requirement like this, I think the only safe way to meet it is to not give those users direct SQL access. Put some kind of restrictive app in front of the database. regards, tom lane ------=_Part_58058_465666639.1726116714682 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
T= hanks for info.
=C2=A0
I know PG is not designed for th= is, but I have this requirement nonetheless=E2=80=A6
I think prev= enting =E2=80=9Cmost users and tools" from seeing/presenting this info= rmation is =E2=80=9Cgood enough=E2=80=9D.
Maybe not revoking acce= ss to all the tables in the schemas, but the =E2=80=9Cmost obvious o= nes=E2=80=9D, like pg_user, pg_shadow, pg_tables etc. will suffice.
=C2=A0
If read-access (SELECT) on views in public= -schema will still works, and pg_dump/restore etc. also works, this sounds = like a solution to me.

=C2=A0

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
= Mobile: +47 909 56 = 963
=C2=A0
P= =C3=A5 torsdag 12. september 2024 kl. 06:36:45, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas Joseph Krogh <andreas@visena.com> writes:
> = Motivation: I have PowerBI users, with a separate =E2=80=98reporting=E2=80= =99-role, accessing=C2=A0
> a database and I want to prevent them fro= m listing all tables, users, databases=C2=A0
> and view-definitions (= to not see the underlying query).

Postgres is not designed to suppor= t this requirement.

> I'm evaluating this:
> REVOKE SELECT = ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC; REVOKE SELECT ON=C2=A0
&= gt; ALL TABLES IN SCHEMA information_schema FROM PUBLIC;
> Will this = affect =E2=80=9Cnormal behaviour=E2=80=9D, ie. prevent the planner, or othe= r=C2=A0
> internal mechanisms, from working properly for sessions log= ged in with the=C2=A0
> =E2=80=98reporting=E2=80=99-role?

Prob= ably 95% of that stuff will still work.=C2=A0 By the same token, there
a= re plenty of information-leaking code pathways that will still leak.
For= instance, your restricted user will have no trouble discovering
the OID= s and names of all extant tables, using something like

do $$ beginfor tid in 1..1000000 loop
=C2=A0 if tid::regclass::text !=3D tid::tex= t then
=C2=A0 =C2=A0 raise notice 'tid % is %', tid, tid::regclass;
= =C2=A0 end if; end loop;
end $$;

Functions such as pg_describe_ob= ject still work fine, too.

Experimenting with psql, a lot of stuff i= s broken as expected:

busted=3D> \d mytable
ERROR:=C2=A0 permi= ssion denied for table pg_class

but some things still work:

b= usted=3D> \sf sin
CREATE OR REPLACE FUNCTION pg_catalog.sin(double pr= ecision)
=C2=A0RETURNS double precision
=C2=A0LANGUAGE internal
= =C2=A0IMMUTABLE PARALLEL SAFE STRICT
AS $function$dsin$function$

= This is pretty much the other side of the same coin.
The reason you can = still parse and plan a query is that
it does not occur to large parts of= the backend that there
should be any reason to refuse to read a system = catalog.
That carries over to these operations as well.

This rece= nt thread might be enlightening:

https://www.postgresql.org/message-= id/flat/18604-04d64b68e981ced6%40postgresql.org

If you have a requir= ement like this, I think the only safe
way to meet it is to not give tho= se users direct SQL access.
Put some kind of restrictive app in front of= the database.

regards, tom lane

=C2=A0
------=_Part_58058_465666639.1726116714682-- ------=_Part_58057_363453689.1726116714662 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_58057_363453689.1726116714662-- ------=_Part_58056_340086286.1726116714662--