how to check specific user_name have “SELECT ON ALL TABLES IN SCHEMA <tableschema>” privilege or not ?

On Sunday, September 11, 2022, yanliang lei <msdnchina@163.com> wrote:

Dear everyone，
how to check specific <user_name> have “SELECT ON ALL TABLES IN SCHEMA
<tableschema>” privilege or not ?

This is not an appropriate list to ask general usage questions. We have a
-general list for those.

But, that isn’t a privilege. Its a grant action. The privilege is just
select on a table in a schema. So you have to check every table in the
schema individually. The functions and the catalogs documented to retrieve
this information - start putting them together into a query. I’m unable to
go look for the specific details right now.

David J.

On 9/11/22 12:03, David G. Johnston wrote:

On Sunday, September 11, 2022, yanliang lei <msdnchina@163.com
<mailto:msdnchina@163.com>> wrote:

Dear everyone，
how to check specific <user_name>  have “SELECT ON ALL TABLES IN
SCHEMA <tableschema>”  privilege or not ?

This is not an appropriate list to ask general usage questions.  We have
a -general list for those.

(therefore moving thread to pgsql-general)

But, that isn’t a privilege.  Its a grant action.  The privilege is just
select on a table in a schema.  So you have to check every table in the
schema individually.  The functions and the catalogs documented to
retrieve this information - start putting them together into a query.
I’m unable to go look for the specific details right now.

For an extension that will help you with this, see
https://github.com/CrunchyData/crunchy_check_access
Either use that directly, or see how it gathers the info you are
interested in:

https://github.com/CrunchyData/crunchy_check_access/blob/master/sql/check_access.sql

HTH,

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com