Re: role to access all information_schema.*?

Ruben Laguna <ruben(dot)laguna(at)gmail(dot)com>
4:05 PM (6 hours ago)
> 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.

I agree, but this OpenMetadata PostgreSQL connector
https://docs.open-metadata.org/latest/connectors/database/postgres reads
from information_schema.*, I can't change that.
I could write my own connector that reads from pg_* and writes to OM api
but it seems like a lot of work.

> 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

I don't have access to the standard, I'm guessing it's ISO/IEC
9075-11:2023 Information technology — Database languages SQL Part 11:
Information and definition schemas (SQL/Schemata)
https://www.iso.org/standard/76586.html.
But I know that Google BigQuery has this `roles/bigquery.metadataViewer`
that when given to a user it allows to see everything in
INFORMATION_SCHEMA. I guess that they are not compliant with the standard.

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?

Best regards/Rubén

On Tue, Oct 7, 2025 at 3:33 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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
>

--
/Rubén