public inbox for [email protected]help / color / mirror / Atom feed
role to access all information_schema.*? 5+ messages / 3 participants [nested] [flat]
* role to access all information_schema.*? @ 2025-10-07 13:24 Ruben Laguna <[email protected]> 2025-10-07 13:33 ` Re: role to access all information_schema.*? Laurenz Albe <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Ruben Laguna @ 2025-10-07 13:24 UTC (permalink / raw) To: [email protected] --0000000000003cb90f0640917edb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: role to access all information_schema.*? 2025-10-07 13:24 role to access all information_schema.*? Ruben Laguna <[email protected]> @ 2025-10-07 13:33 ` Laurenz Albe <[email protected]> 2025-10-07 20:59 ` Re: role to access all information_schema.*? Ruben Laguna <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Laurenz Albe @ 2025-10-07 13:33 UTC (permalink / raw) To: Ruben Laguna <[email protected]>; [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: role to access all information_schema.*? 2025-10-07 13:24 role to access all information_schema.*? Ruben Laguna <[email protected]> 2025-10-07 13:33 ` Re: role to access all information_schema.*? Laurenz Albe <[email protected]> @ 2025-10-07 20:59 ` Ruben Laguna <[email protected]> 2025-10-07 21:14 ` Re: role to access all information_schema.*? Tom Lane <[email protected]> 2025-10-08 06:46 ` Re: role to access all information_schema.*? Laurenz Albe <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Ruben Laguna @ 2025-10-07 20:59 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Ruben Laguna <[email protected]> 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 <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: role to access all information_schema.*? 2025-10-07 13:24 role to access all information_schema.*? Ruben Laguna <[email protected]> 2025-10-07 13:33 ` Re: role to access all information_schema.*? Laurenz Albe <[email protected]> 2025-10-07 20:59 ` Re: role to access all information_schema.*? Ruben Laguna <[email protected]> @ 2025-10-07 21:14 ` Tom Lane <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Tom Lane @ 2025-10-07 21:14 UTC (permalink / raw) To: Ruben Laguna <[email protected]>; +Cc: Laurenz Albe <[email protected]>; [email protected] Ruben Laguna <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: role to access all information_schema.*? 2025-10-07 13:24 role to access all information_schema.*? Ruben Laguna <[email protected]> 2025-10-07 13:33 ` Re: role to access all information_schema.*? Laurenz Albe <[email protected]> 2025-10-07 20:59 ` Re: role to access all information_schema.*? Ruben Laguna <[email protected]> @ 2025-10-08 06:46 ` Laurenz Albe <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Laurenz Albe @ 2025-10-08 06:46 UTC (permalink / raw) To: Ruben Laguna <[email protected]>; +Cc: [email protected] 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-10-08 06:46 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-10-07 13:24 role to access all information_schema.*? Ruben Laguna <[email protected]> 2025-10-07 13:33 ` Laurenz Albe <[email protected]> 2025-10-07 20:59 ` Ruben Laguna <[email protected]> 2025-10-07 21:14 ` Tom Lane <[email protected]> 2025-10-08 06:46 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox