From: "Wong, Kam Fook (TR Technology)" <kamfook.wong@thomsonreuters.com>
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: 101 Grants and Access Right Table/View
Thread-Topic: 101 Grants and Access Right Table/View
Thread-Index: Adsgw3dAKHdhqlizRD29CgJ7xQlqDQ==
Date: Thu, 17 Oct 2024 18:38:07 +0000
Message-ID: 
 <CH0PR03MB6100645C5E5EF4CBD89AB31BFE472@CH0PR03MB6100.namprd03.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?TGc0N8AFzR6zMnt4Z44GzFnh9pnO7FX+gLioumX23GY3J5K97alOgaThOqn5?=
 =?us-ascii?Q?Z+cmqxeknto2gIAFg5Da+/E/8zCZhyyo/6uVSBf9wsLy4qGKyO+ZkiOKRo/D?=
 =?us-ascii?Q?zRCo6hytPAJR/Zk+pHO0gEzTl5MJEIdIfO6TyibC0dGe2zObwfc2dFfnHV2d?=
 =?us-ascii?Q?xUKZIbDzpg4GhnUmocgPcfu1ey1jfP15ULOfttNXFgFTm4XfHXFrca7AsjzI?=
 =?us-ascii?Q?T7CUjwU/M1m3aAFI89OPSQJa4Tyv/aV0I4qUgCBtRDQxzbuCovtepzC8+nkV?=
 =?us-ascii?Q?dVSX81PNzaJpkWLCzCxSUcspnItdzUc+FfebNdxJkrgFD7QeWzHR/pVZYU9c?=
 =?us-ascii?Q?mGDrSWB79n3Pf/mthVU32Pp58fRXp85UYlevz/VGzmeTXm8vOWCi1w9hEQQZ?=
 =?us-ascii?Q?6dTGslCfg4ReFsE546fAwQVLmvF9yR4t7++P7fB8sp+2QOtKBs/m7y13PKrd?=
 =?us-ascii?Q?azp5d4mpaVZssFwY1AKF6n4Z4yJ+ecqfAXYoUqEeFNuLVX+TNJYpAnboOFuR?=
 =?us-ascii?Q?vR1LhFDw6l8lhNlsPmUeawEVVHra7VXTZM4NO2Fd7DSzEIA54N1OLgjn+7hS?=
 =?us-ascii?Q?LuiedIuB+Os/E81UEn/SLOC8mdYUMMuxyNl2l65V8QuFefjWXtdEQq6Zd1ZO?=
 =?us-ascii?Q?lAkXEtApTlNBo/e6yp3XNR2ldgwkUEgfGK//MBTSxHszeyf2YoxIHaeVFPsp?=
 =?us-ascii?Q?+CIEjCzD652l7k/5UnjCdGVPiJ0SwXx3e6tfjeIt14pKuwfVsdZWiaym1o1X?=
 =?us-ascii?Q?e3dnYk+ADmEXcAJmUZGqatWCQDD1BusNkZr9+cktNfpqjh7Z5ewcLxasM7qO?=
 =?us-ascii?Q?mkGhoZlcxlQTqBDaDa9m+g+OhhQ5ivE5QuT+X22rk7RzZg8MjwN9aSXvtQWt?=
 =?us-ascii?Q?P2hgBH7HQrDvFOJobT8pKtqDtX1sB4IcaoNgqELadPjnrvThMl9lclZRi6w2?=
 =?us-ascii?Q?kyRPXp6NHbABscTVSXKn5mZjHG7BHOR6BVzSP0GvaWjYtORSE9lHkxv+k8P/?=
 =?us-ascii?Q?wBjH/pR8zCMX63Poik7mWCZAnHwlmXZTWHDKg6Zjx5gTYHm4kQ/r64mMMlek?=
 =?us-ascii?Q?w78ijMI+vlpvIIohRP72k9T39i9ggrkCN8THOlpVFTzPHuHv70Tf1DRTZp4+?=
 =?us-ascii?Q?P8J0LelGar4wAluOIKqMdB65nJyzjhpxj8LbPNbmcJtn99T/Gd5Yy88YLIGY?=
 =?us-ascii?Q?uoeNBi7UZG5ujXAGY05127gpqBAMsB6sp/AWUBsifsB0oeyGQ0tb2W1uAQeX?=
 =?us-ascii?Q?kC19HOaXsmwtZSpn+jImDknqYfo97yFgNQPs55GZkR63ZlsGsA206eSDptHI?=
 =?us-ascii?Q?iupZsDHeV2SrkMnWIv7UjlGO9Cpt8plaU5y/h0CQ4CskKN4m9uHWi0EKEz3/?=
 =?us-ascii?Q?jvtjOeTiQZEk4unJ5I7WB1a3DgemT9NszKlyi76+l7PsuAv5slRtkjsFglsK?=
 =?us-ascii?Q?ijLOE4m0NAcDDqjSi/F0hZze2hMPvxCYBzrVMDTtrPK4sghP5uf7IPAJ05xg?=
 =?us-ascii?Q?sRXkX1ihb1EJDWYRiN/dGc2dodbW0TRV4XNIBEHsYfnJz4XWzqnLJuZg6/CG?=
 =?us-ascii?Q?3Gu2y2/tGRgvA0oAKwXkFgdJgZuVkO5DP1bufh7d4t+sG/LILK+6/EsB82RE?=
 =?us-ascii?Q?uA=3D=3D?=
Content-Type: multipart/alternative;
	boundary="_000_CH0PR03MB6100645C5E5EF4CBD89AB31BFE472CH0PR03MB6100namp_"
MIME-Version: 1.0
X-OriginatorOrg: thomsonreuters.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: CH0PR03MB6100.namprd03.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 914b9ab8-3af9-440d-62ae-08dceedad819
X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Oct 2024 18:38:07.5350
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: 62ccb864-6a1a-4b5d-8e1c-397dec1a8258
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 lZ2tAvxfKE0iZwIfgjsqdVQ56DbwIJ1FaCAuS2+zvdCURZ2CPJ1Ceo6h0/7twhHdgs4bwfpQazkUk77SnOn1BPZdoyOFOk1d3+6drQ8dBSU=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CH3PR03MB7459
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/CH0PR03MB6100645C5E5EF4CBD89AB31BFE472%40CH0PR03MB6100.namprd03.prod.outlook.com>
Precedence: bulk

--_000_CH0PR03MB6100645C5E5EF4CBD89AB31BFE472CH0PR03MB6100namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I have a simple question for "access rights" view or table within Postgres.

Here is my grant query:

GRANT USAGE ON SCHEMA abc TO abc_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO abc_use=
r;
GRANT ALL ON ALL SEQUENCES IN SCHEMA abc TO abc_user;
GRANT EXECUTE ON ALL ROUTINES IN SCHEMA abc TO abc_user;

But I can't find a view/table that is associated with the above grants?  Th=
e view/table below doesn't shown the above granted rights.

select * from information_schema.role_table_grants;
select * from information_schema.usage_privileges

Thank you
Kam Fook Wong
This e-mail is for the sole use of the intended recipient and contains info=
rmation that may be privileged and/or confidential. If you are not an inten=
ded recipient, please notify the sender by return e-mail and delete this e-=
mail and any attachments. Certain required legal entity disclosures can be =
accessed on our website: https://www.thomsonreuters.com/en/resources/disclo=
sures.html

--_000_CH0PR03MB6100645C5E5EF4CBD89AB31BFE472CH0PR03MB6100namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:PMingLiU;
	panose-1:2 1 6 1 0 1 1 1 1 1;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
@font-face
	{font-family:Consolas;
	panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
	{font-family:"\@PMingLiU";
	panose-1:2 1 6 1 0 1 1 1 1 1;}
@font-face
	{font-family:"Arial Nova";}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:12.0pt;
	font-family:"Aptos",sans-serif;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">I have a simple qu=
estion for &#8220;access rights&#8221; view or table within Postgres.&nbsp;
<br>
<br>
<o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">Here is my grant q=
uery:<br>
<br>
<o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">GRANT USAGE ON SCH=
EMA abc TO abc_user;<o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">GRANT SELECT, INSE=
RT, UPDATE, DELETE ON ALL TABLES IN SCHEMA abc TO abc_user;<o:p></o:p></spa=
n></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">GRANT ALL ON ALL S=
EQUENCES IN SCHEMA abc TO abc_user;<o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">GRANT EXECUTE ON A=
LL ROUTINES IN SCHEMA abc TO abc_user;<br>
<br>
But I can&#8217;t find a view/table that is associated with the above grant=
s?&nbsp; The view/table below doesn&#8217;t shown the above granted rights.=
<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:10.0pt;font-family:&quot;Ar=
ial Nova&quot;,sans-serif"><o:p>&nbsp;</o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">select * from info=
rmation_schema.role_table_grants;<o:p></o:p></span></p>
<p class=3D"MsoNormal" style=3D"text-autospace:none"><span style=3D"font-si=
ze:10.0pt;font-family:&quot;Arial Nova&quot;,sans-serif">select * from info=
rmation_schema.usage_privileges<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><br>
Thank you<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Kam Fook Wong<o:p><=
/o:p></span></p>
</div>
This e-mail is for the sole use of the intended recipient and contains info=
rmation that may be privileged and/or confidential. If you are not an inten=
ded recipient, please notify the sender by return e-mail and delete this e-=
mail and any attachments. Certain
 required legal entity disclosures can be accessed on our website: https://=
www.thomsonreuters.com/en/resources/disclosures.html
</body>
</html>

--_000_CH0PR03MB6100645C5E5EF4CBD89AB31BFE472CH0PR03MB6100namp_--