From: "Wong, Kam Fook (TR Technology)" <kamfook.wong@thomsonreuters.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: RE: [EXT] Re: 101 Grants and Access Right Table/View
Thread-Topic: [EXT] Re: 101 Grants and Access Right Table/View
Thread-Index: Adsgw3dAKHdhqlizRD29CgJ7xQlqDQAChiGAAAK2BzA=
Date: Thu, 17 Oct 2024 21:09:01 +0000
Message-ID: 
 <CH0PR03MB610027C698325AB59F9A9C21FE472@CH0PR03MB6100.namprd03.prod.outlook.com>
References: 
 <CH0PR03MB6100645C5E5EF4CBD89AB31BFE472@CH0PR03MB6100.namprd03.prod.outlook.com>
 <704895.1729194519@sss.pgh.pa.us>
In-Reply-To: <704895.1729194519@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?us-ascii?Q?dBRd/DuaWYnBetwuc5UBUsebIVrf9u9My0m1rbPuyzH55sFqCAv1z0V8dDiH?=
 =?us-ascii?Q?G2cTIJvqA97Ybnf9tgJihvzZJKljup81j93rV3MqduBt8yjttXpqk7W49nO0?=
 =?us-ascii?Q?qc0+caFzioXK71r04KvN5Dbf03BZwydIthcWYKAoznYywVjFvpLAvLPbMwld?=
 =?us-ascii?Q?+0upgznzWrMCsE5j/SJOJ1U2iH9SXouLXhBwwcdehxQRS4xu+swQFyutsC0x?=
 =?us-ascii?Q?nDUDQkjlCAVqzCcz1t9E0A7EJWqdg6G2+BsekWw04p63Hj55Tiz5cUqFPXqh?=
 =?us-ascii?Q?trcWXUCtTZUihowRR0cdfI8bvqWK5XI9+X9dFu0VgDfo9WV5cOoDfMD9bGxR?=
 =?us-ascii?Q?a6ZMfaSe7syZRN4yI4C9jp/9X9WdiXcmNFQLSevSK5ka95QrhFcHQBedKjNL?=
 =?us-ascii?Q?Ht/TBBMpnpbDnJ5JKJ+dvZh1S4pHvYaSDrb47qDRDLQjzt7P601cORgT6yfX?=
 =?us-ascii?Q?Wk1gei47Q4KF0gncqpkMJSmCRRpEdXWEpB/w2xm8JNCSLSUjWmZjr5fwvl46?=
 =?us-ascii?Q?K7f8FOJyOjzNsIfs0AB3b31esMvswn50SAsxWQ9j93KX57qGiaUlpV5M887u?=
 =?us-ascii?Q?gKodtXtoPgWHcoQITBRjwjexDwhoIycdqYy2ehpjD9AraIVtNZN3HQnBds4o?=
 =?us-ascii?Q?QapJJwlFwzYQDZloN8gFRp59Zq7fNLh1MRrUjfFnsX4BaUKSz5faRrg4Xdhs?=
 =?us-ascii?Q?NQuGpBGKWH5/VNn/ELltvc7j2YnTB6KaDaRnxmuX3HbZao48Vd7GiWC/W+xF?=
 =?us-ascii?Q?kvuz1qSpcLk+uU1xQi2ctH4VjL4fyIKQw2I5zAe8iwXfPWbjV0iRHy9/vjlo?=
 =?us-ascii?Q?UTKxHRxvgi2jeo8xqlVK45Yhowc7egKyLwtOmHpLV5y1VwYHoykSqjx2zl0F?=
 =?us-ascii?Q?TNi0LX8EWfg3/L0uWa+0zLF9/oAErybOs4mxAnRxGt9IfbpXXt5Qk5h2PCAB?=
 =?us-ascii?Q?HayEu7ja35Fb6IpKZIBGTRYfBh6I9TlzsYECxZna2SqY8wtj2CFQQ3WXmXzS?=
 =?us-ascii?Q?XywBVCQn6iP0hRYPioJygTuSkXQFolFDmklcx/K7BhyiPOTLs9xlrhKq/Nd6?=
 =?us-ascii?Q?AAa8MuuHLtCzA2QaX/NaXcaZSNsJ/qjWNp7Aeg0NFujTHGUYxy3zUuicTFxW?=
 =?us-ascii?Q?Ai/4CIcZsQWcMzCzZ9OskT+tILJcF1LAOAMF4dUWlIbLW4Pv0ZRkp6lk/eC5?=
 =?us-ascii?Q?fgRvAw5/2SXxsWgn4c58Ru/6HnW6WtJKqnJZYchEUMBw1pHtapdVmozPXpG4?=
 =?us-ascii?Q?spAUlVQVdUZnQuMco93UaapvGZWhHKP0qkGlQqeOpBabFEbORAaQPVTiJDdD?=
 =?us-ascii?Q?aqbXW0HmH3m4kkhpWlH2IXGdT3SE18NNCrC66evuKmfSPUW74BN/7mCLkqhJ?=
 =?us-ascii?Q?jhdI5bNxHQedgZgfkafWfNmwwYOsM3p1zJNWgTIefRj/aSLLstC6/G6tSJSb?=
 =?us-ascii?Q?+nIUaKW3ChvHq6S8V+ldbH8DurKjzdxzmWNfcuOp2e1E+kYcQLCEnNhpRdeq?=
 =?us-ascii?Q?CeSiFsT1J/kOAkp6bVcfN+P9dlDIUl2Cx6dHwekNlPGvJBRBpdz5s1p5JgCi?=
 =?us-ascii?Q?rGoZ1uXFcBKckvrhCVBeOrXaW1s2lFEnhUZEXPHva8hUx84PepdNzAQs5qyV?=
 =?us-ascii?Q?ng=3D=3D?=
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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: 
 10d8c0e9-cd9e-4f64-a769-08dceeefece0
X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Oct 2024 21:09:01.8396
 (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: 
 gFJvejDtMI7sP68dYVKeCBpRIhif85+iTWWP/JzXkEeiFuOzgVTgL7bQG0uP8wk7ivECI0dv82kEC5Hi9q5Rk+YKHua/TjSe/dO4xyDEs+A=
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CH0PR03MB6178
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/CH0PR03MB610027C698325AB59F9A9C21FE472%40CH0PR03MB6100.namprd03.prod.outlook.com>
Precedence: bulk

Tom,

These are existing objects/tables.  So I assume after the grants were run, =
 the  information_schema.role_table_grants and information_schema.usage_pri=
vileges shouldn't be blank right/now rows return?

Thank you
Kam
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>=20
Sent: Thursday, October 17, 2024 2:49 PM
To: Wong, Kam Fook (TR Technology) <kamfook.wong@thomsonreuters.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXT] Re: 101 Grants and Access Right Table/View

External Email: Use caution with links and attachments.

"Wong, Kam Fook (TR Technology)" <kamfook.wong@thomsonreuters.com> writes:
> I have a simple question for "access rights" view or table within Postgre=
s.
> Here is my grant query:

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

> But I can't find a view/table that is associated with the above=20
> grants?

Those commands would simply apply the grants to any existing objects in the=
 given schema, so you'd have to look at such objects to see the effects.  I=
f there are none, nothing happens.

You might be looking for ALTER DEFAULT PRIVILEGES?

			regards, tom lane