From: Dian Fay <dian.m.fay@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_906FC796-E8F0-4B51-B021-D38C9AC0F4E4"
Mime-Version: 1.0 (Mac OS X Mail 12.2 \(3445.102.3\))
Subject: Privilege filters in information schema views
Message-Id: <83C8A77C-5BCD-41FB-8B22-35999417D32A@gmail.com>
Date: Wed, 4 Dec 2019 11:06:25 -0800
To: pgsql-docs@lists.postgresql.org
Precedence: bulk


--Apple-Mail=_906FC796-E8F0-4B51-B021-D38C9AC0F4E4
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

I recently ran afoul of the filters in the information schema views =
which restrict the returned records to those entities the querying user =
has ownership or any privilege on. The detailed documentation on the =
information schema does mention this, e.g. in =
https://www.postgresql.org/docs/current/infoschema-tables.html =
<https://www.postgresql.org/docs/current/infoschema-tables.html> :

> Only those tables and views are shown that the current user has access =
to (by way of being the owner or having some privilege).

But I think there=E2=80=99s room for a couple of improvements:

* the information schema overview page =
(https://www.postgresql.org/docs/current/information-schema.html =
<https://www.postgresql.org/docs/current/information-schema.html>) could =
mention that, as a general rule, records in the information schema are =
restricted in this manner; and,
* it=E2=80=99s not clear what recourse a user has if they do need a =
complete listing of relations, columns, foreign tables, etc. This would =
especially be the case for users more familiar with other RDBMSs, who =
may not realize the pg_catalog relations are available. Both the =
overview and the detail pages could link to the most appropriate =
pg_catalog page, such as pg_class for information_schema.tables.

I=E2=80=99m happy to submit a patch in the near future, but since this =
will touch several pages of documentation it seemed best to float the =
idea here first.

Dian Fay=

--Apple-Mail=_906FC796-E8F0-4B51-B021-D38C9AC0F4E4
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;" class=3D"">I =
recently ran afoul of the filters in the information schema views which =
restrict the returned records to those entities the querying user has =
ownership or any privilege on. The detailed documentation on the =
information schema does mention this, e.g. in <a =
href=3D"https://www.postgresql.org/docs/current/infoschema-tables.html" =
class=3D"">https://www.postgresql.org/docs/current/infoschema-tables.html<=
/a> :<div class=3D""><br class=3D""></div><blockquote type=3D"cite" =
class=3D""><span style=3D"color: rgb(13, 10, 11); font-family: =
&quot;Open Sans&quot;, sans-serif; font-variant-ligatures: normal; =
orphans: 2; widows: 2;" class=3D"">Only those tables and views are shown =
that the current user has access to (by way of being the owner or having =
some privilege).</span></blockquote><br class=3D""><div class=3D"">But I =
think there=E2=80=99s room for a couple of improvements:</div><div =
class=3D""><br class=3D""></div><div class=3D"">* the information schema =
overview page (<a =
href=3D"https://www.postgresql.org/docs/current/information-schema.html" =
class=3D"">https://www.postgresql.org/docs/current/information-schema.html=
</a>) could mention that, as a general rule, records in the information =
schema are restricted in this manner; and,</div><div class=3D"">* it=E2=80=
=99s not clear what recourse a user has if they do need a complete =
listing of relations, columns, foreign tables, etc. This would =
especially be the case for users more familiar with other RDBMSs, who =
may not realize the pg_catalog relations are available. Both the =
overview and the detail pages could link to the most appropriate =
pg_catalog page, such as pg_class for =
information_schema.tables.</div><div class=3D""><br class=3D""></div><div =
class=3D"">I=E2=80=99m happy to submit a patch in the near future, but =
since this will touch several pages of documentation it seemed best to =
float the idea here first.</div><div class=3D""><br class=3D""></div><div =
class=3D"">Dian Fay</div></body></html>=

--Apple-Mail=_906FC796-E8F0-4B51-B021-D38C9AC0F4E4--