public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ruben Laguna <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: [email protected]
Subject: Re: role to access all information_schema.*?
Date: Tue, 7 Oct 2025 22:59:57 +0200
Message-ID: <CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAFOAOWJNJKLr8-YS7Q5wYnUHTjjaTbU6=rmTF_jW5W8fNfFamA@mail.gmail.com>
<[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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: role to access all information_schema.*?
In-Reply-To: <CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox