MIME-Version: 1.0
References: <VisenaEmail.0.81936c517b2d9cfe.191e44de951@origo-test01.app.internal.visena.net>
 <3952715.1726115805@sss.pgh.pa.us> <VisenaEmail.1.4f10ceb0099d6ab1.191e48a6946@origo-test01.app.internal.visena.net>
In-Reply-To: <VisenaEmail.1.4f10ceb0099d6ab1.191e48a6946@origo-test01.app.internal.visena.net>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 12 Sep 2024 09:05:48 -0400
Message-ID: <CAKAnmm+SrS1=ggcc9qCAXd=uzJWzwH_CciM+aRr-PtDZjrEuRA@mail.gmail.com>
Subject: Re: Effects of REVOKE SELECT ON ALL TABLES IN SCHEMA pg_catalog FROM PUBLIC
To: Andreas Joseph Krogh <andreas@visena.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000016c4be0621ebc695"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BSrS1%3Dggcc9qCAXd%3DuzJWzwH_CciM%2BaRr-PtDZjrEuRA%40mail.gmail.com>
Precedence: bulk

--00000000000016c4be0621ebc695
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Sep 12, 2024 at 12:52=E2=80=AFAM Andreas Joseph Krogh <andreas@vise=
na.com>
wrote:

> 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 =
this
> information is =E2=80=9Cgood enough=E2=80=9D.
>

As pointed out, there are very many workarounds. This is security theater.

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

pg_dump will absolutely not work without access to the system catalogs.

If you want to prevent information, stop direct access and make the
application call user functions.

(Also note that determining if a database or user exists does not even
require a successful login to the cluster.)

Cheers,
Greg

--00000000000016c4be0621ebc695
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Sep 12, 2024 at 12:52=E2=80=AFAM =
Andreas Joseph Krogh &lt;<a href=3D"mailto:andreas@visena.com">andreas@vise=
na.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex"><div>I know PG is not designed for this, but I=
 have this requirement nonetheless=E2=80=A6</div><div>I think preventing =
=E2=80=9Cmost users and tools&quot; from seeing/presenting this information=
 is =E2=80=9Cgood enough=E2=80=9D.<br></div><div></div></blockquote><div><b=
r></div><div>As pointed out, there are very many workarounds. This is secur=
ity theater.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div>If read-access (SELECT) on views in <code>public</code>-schema=
 will still works, and pg_dump/restore etc. also works, this sounds like a =
solution to me.</div></blockquote><div><br></div><div>pg_dump will absolute=
ly not work without access to the system catalogs.</div><div><br></div><div=
>If you want to prevent information, stop direct access and make the applic=
ation call user functions.</div><div><br></div><div>(Also note that determi=
ning if a database or user exists does not even require a successful login =
to the cluster.)</div><div><br></div><div>Cheers,</div><div>Greg</div><div>=
<br></div></div></div>

--00000000000016c4be0621ebc695--