MIME-Version: 1.0
References: <VisenaEmail.66.54f51e587b38c9cc.18fab4b4eb6@origo-test01.app.internal.visena.net>
 <671405.1716570133@sss.pgh.pa.us>
In-Reply-To: <671405.1716570133@sss.pgh.pa.us>
From: Muhammad Salahuddin Manzoor <salahuddin.m@bitnine.net>
Date: Fri, 24 May 2024 22:43:56 +0500
Message-ID: <CAKD7CDkcRu8S6i=q8S06VoGyt9jfN5iS_2WvncAALrWwP3XyNg@mail.gmail.com>
Subject: Re: prevent users from SELECT-ing from pg_roles/pg_database
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andreas Joseph Krogh <andreas@visena.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000000e29fd061936b70b"
Archived-At: <https://www.postgresql.org/message-id/CAKD7CDkcRu8S6i%3Dq8S06VoGyt9jfN5iS_2WvncAALrWwP3XyNg%40mail.gmail.com>
Precedence: bulk

--0000000000000e29fd061936b70b
Content-Type: text/plain; charset="UTF-8"

Greetings,

Yes, you are correct. And

For applications/systems/scripts relying  on this information may require
sgnificent modifications to handle the restricted access.

Alternative approches can be.

Auditing and monitoring.
You can use pgaudit extension for auditing and minitoring.

Use SE-PostgeSQL extension that give fine grained access control other than
PG standard permissions.

Try RLS row level security. May involve careful planning and may not cover
all use cases.

Regards,
Salahuddin.


On Fri, 24 May 2024, 22:02 Tom Lane, <tgl@sss.pgh.pa.us> wrote:

> Andreas Joseph Krogh <andreas@visena.com> writes:
> > Hi, is there a way to prevent a user/role from SELECT-ing from certain
> > system-tables?
> > I'd like the contents of pg_{user,roles,database} to not be visible to
> all
> > users.
>
> As noted, you can in principle revoke the public SELECT grant from
> those views/catalogs.  However, the system is not designed to hide
> such information, which means you'll have (at least) two kinds of
> issues to worry about:
>
> 1. Queries or tools breaking that you didn't expect to break.
> It's hardly uncommon for instance for queries on pg_class to
> join to pg_roles to get the owner names for tables.
>
> 2. Information leaks.  For example, mapping role OID to role name
> is trivial with either regrole or pg_get_userbyid(), so it
> wouldn't take long to scan the plausible range of role OIDs and
> get all their names, even without SQL access to the underlying
> catalog.
>
>                         regards, tom lane
>
>
>

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

<div dir=3D"auto"><div dir=3D"auto"><div>Greetings,</div><div dir=3D"auto">=
<br></div><div dir=3D"auto">Yes, you are correct. And</div><div dir=3D"auto=
"><br></div><div>For applications/systems/scripts relying=C2=A0 on this inf=
ormation may require sgnificent modifications to handle the restricted acce=
ss.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Alternative approche=
s can be.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Auditing and m=
onitoring.</div><div dir=3D"auto">You can use pgaudit extension for auditin=
g and minitoring.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Use SE=
-PostgeSQL extension that give fine grained access control other than PG st=
andard permissions.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Try =
RLS row level security. May involve careful planning and may not cover all =
use cases.</div><div dir=3D"auto"><br></div><div dir=3D"auto">Regards,</div=
><div dir=3D"auto">Salahuddin.</div><div dir=3D"auto"><br></div></div><br><=
div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, 24 =
May 2024, 22:02 Tom Lane, &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us" rel=3D"n=
oreferrer noreferrer" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br=
></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-=
left:1px #ccc solid;padding-left:1ex">Andreas Joseph Krogh &lt;<a href=3D"m=
ailto:andreas@visena.com" rel=3D"noreferrer noreferrer noreferrer" target=
=3D"_blank">andreas@visena.com</a>&gt; writes:<br>
&gt; Hi, is there a way to prevent a user/role from SELECT-ing from certain=
 <br>
&gt; system-tables?<br>
&gt; I&#39;d like the contents of pg_{user,roles,database} to not be visibl=
e to all <br>
&gt; users.<br>
<br>
As noted, you can in principle revoke the public SELECT grant from<br>
those views/catalogs.=C2=A0 However, the system is not designed to hide<br>
such information, which means you&#39;ll have (at least) two kinds of<br>
issues to worry about:<br>
<br>
1. Queries or tools breaking that you didn&#39;t expect to break.<br>
It&#39;s hardly uncommon for instance for queries on pg_class to<br>
join to pg_roles to get the owner names for tables.<br>
<br>
2. Information leaks.=C2=A0 For example, mapping role OID to role name<br>
is trivial with either regrole or pg_get_userbyid(), so it<br>
wouldn&#39;t take long to scan the plausible range of role OIDs and<br>
get all their names, even without SQL access to the underlying<br>
catalog.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
<br>
<br>
</blockquote></div></div>

--0000000000000e29fd061936b70b--