Обсуждение: role to access all information_schema.*?

On Tue, 2025-10-07 at 15:24 +0200, Ruben Laguna wrote:
>
> From what I see a user can only see in `select * from information_schema.tables`
> the tables that the user has been granted SELECT privilege. 
>
> So, my question is:  Is  there is some other way to get a user to be a
> "metadata viewer"  without been a user that also has access to the data in
> those tables? 
>
> Do you know if there is any plan to add such a role? Has it been discuss
> before and deemed a bad idea?

That is not for PostgreSQL to decide.

The information_schema is specified by the SQL standard, and the standard
decrees that you can only see the metadata of objects on which you have
access privileges.

This is quite different from the PostgreSQL approach, which is to make
all metadata public (with the exception of password hashes etc.).

> My use case is to have OpenMetadata to read the information_schema.* and
> publish the table name, column names, etc in the OM user interface.
> I would prefer keeping the privileges of the OM user to a minimum but it
> seems that right now the minimum would be `pg_read_all_data`

You should use the PostgreSQL catalog tables like pg_class and pg_attribute.
They are more cumbersome to use, and they may change from version to version,
but at least everybody can see all their data.

Yours,
Laurenz Albe

Ruben Laguna <ruben.laguna@gmail.com> writes:
> Do you know what the standard says exactly, does it outright bans using any
> special means like having (pg_metadata_viewr or pg_read_information_schema,
> etc). as "access privilege"? Do you think it's hopeless to propose this in
> pgsql-hackers?

You can propose all you want, but I doubt there will be a lot of
interest in it.  In the first place, the pg_read_all_data role
already exists and gets the job done without any arguable violation
of the standard.  In the second place, we really don't take that much
interest in the information_schema.  It's there for pro-forma spec
compliance, but it performs very poorly (not least because it has to
check the spec-mandated privilege restrictions).  Moreover there are
significant aspects of Postgres that simply aren't represented in
the information_schema because they're outside the standard.
So the advice you'll generally get is what Laurenz already said,
namely look directly at the PG catalogs.

That being the case, and since there's a pretty decent argument
that such a role would violate the SQL spec, I doubt it'll happen.

            regards, tom lane

On Tue, 2025-10-07 at 22:59 +0200, Ruben Laguna wrote:
> Do you know what the standard says exactly, does it outright bans using any
> special means like having (pg_metadata_viewr or pg_read_information_schema,
> etc). as "access privilege"?

Yes, that would be ISO/IEC 9075-11.  They define for example
information_schema.tables as

CREATE VIEW TABLES AS
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE,
SELF_REFERENCING_COLUMN_NAME, REFERENCE_GENERATION,
USER_DEFINED_TYPE_CATALOG, USER_DEFINED_TYPE_SCHEMA,
USER_DEFINED_TYPE_NAME, IS_INSERTABLE_INTO, IS_TYPED,
COMMIT_ACTION
FROM DEFINITION_SCHEMA.TABLES
WHERE ( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME ) IN
( SELECT TP.TABLE_CATALOG, TP.TABLE_SCHEMA, TP.TABLE_NAME
FROM DEFINITION_SCHEMA.TABLE_PRIVILEGES AS TP
WHERE ( TP.GRANTEE IN
( 'PUBLIC', CURRENT_USER )
OR
GRANTEE IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) )
UNION
SELECT CP.TABLE_CATALOG, CP.TABLE_SCHEMA, CP.TABLE_NAME
FROM DEFINITION_SCHEMA.COLUMN_PRIVILEGES AS CP
WHERE ( CP.GRANTEE IN
( 'PUBLIC', CURRENT_USER )
OR
CP.GRANTEE IN
( SELECT ROLE_NAME
FROM ENABLED_ROLES ) ) )
AND
TABLE_CATALOG
= ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );

> Do you think it's hopeless to propose this in pgsql-hackers?

I agree with Tom, there is little hope.

Yours,
Laurenz Albe