Re: role to access all information_schema.*?
| 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,
> 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
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.albe@cybertec.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
В списке pgsql-admin по дате отправления: