Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v6En7-001RKX-Pt for pgsql-admin@arkaria.postgresql.org; Tue, 07 Oct 2025 21:00:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1v6En5-003Yjv-JO for pgsql-admin@arkaria.postgresql.org; Tue, 07 Oct 2025 21:00:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1v6En5-003YjU-3H for pgsql-admin@lists.postgresql.org; Tue, 07 Oct 2025 21:00:12 +0000 Received: from mail-yw1-x112f.google.com ([2607:f8b0:4864:20::112f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1v6En3-000YST-0Q for pgsql-admin@lists.postgresql.org; Tue, 07 Oct 2025 21:00:11 +0000 Received: by mail-yw1-x112f.google.com with SMTP id 00721157ae682-74435335177so5287177b3.0 for ; Tue, 07 Oct 2025 14:00:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1759870809; x=1760475609; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=elS4OW7tFlPDBnP78wvl+E8+EC1M/aPwoBImwy8LTaA=; b=ckzO+wO4OA3xtAbagWZ7siAQmlEhvMaolbXae/wawOtWANcDSLKa5x7l8SQZDUY5A0 2fGXxwEiWJMug2+HYNajAC/xbh0i9XKlgPr3MeY0E8BsX6gONvq8LNRjS3B9MvkHpDrD u9I7k98vFmjcKe4Hdiw+3K0Wfw7tsrFhIBZZVJNkpSmxUGmTPSlGqTBpvGCJZxFXQ+or 5yyLI7nEi0O1V+snmoDDQJX10kcWnElzaVLULHJCfEJ4asdlHvZ0zAo96nHMofBJFYVl hOGrEIxbWYFDxhoSOHW57JKIFj5KYieTG58LWmxj16RYP52vNhoRpT5iIKFlNa+gDa6a 7DuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1759870809; x=1760475609; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=elS4OW7tFlPDBnP78wvl+E8+EC1M/aPwoBImwy8LTaA=; b=bTigaTY9CRUO6XScXqVkECMgzzt1ortYSqf3/HvhbQyNPDp6aeJlR9yYs4DKDBGF6I 4knfgV8/1tpcLt/VyffyFRdNQh83TWxVN0Lz3poeY7BPx0p+xY5Em394gg4XE1yRJ+Or oGhp93BCaYfiSwRVBBsrIGnmc0fEHx8KaWjw5mB1xNWHiuwt87mpHY30ttmxln2l+2OT P4D3A/hgagHmjKwAqRijKm6ToFmjAkChjnmGV85/d7E8NwseTtjaeEk96aoOaBHfKhCE z6bl8OkhRsM3jpsEqdqrL4MTQFy1fnUiiHYwWvjGvVHJaopiDwA4ilEZ3mwsOlB6uMyd Z1zg== X-Gm-Message-State: AOJu0Yxaxp4GXkg/JFUWgzbVFeHFMwpoy2P84yZB8sP3vFipM8UvUtm6 HfeAPVYUWQ/oPxAeWaWs7RBbwemX/fSvtl6p4q0YOdS+UrkgT6b+TXYz9WhuVJmaCoKPULpt+Ay gPBnL5dgs+8dGMAuIHtfpdCf9b1sXpvA= X-Gm-Gg: ASbGncsSZ75smdE3kIcX9I35StlUYWuCLpDHRpjDImjeUB98dvoWeO++Gd3qN8x+vKs fbI6Xg4keYKXZIjbwJUKJthmEEQT4DPqmI0z2yLPD3IK5NBIb6HYMoHvcK14Cts6yU+vzHzbeWw mZ4AwoySVxjDb2U76UljKiG23TwJkW/449gizATT1vibyDthi7FCI9mWY1iitlN+BX2lGcz8WOf vRk+1H2wwds06cgoDJYQecyHQxwsQ== X-Google-Smtp-Source: AGHT+IFF5Ft3HylAjOn2aC54s+7QwijCDFQBemO8cEhiKZzwJjjtx+n7OdR6p8if1dt5kpE2DZNWT2bbeR6G+GWHTx0= X-Received: by 2002:a53:bbd2:0:b0:632:eae9:5cd7 with SMTP id 956f58d0204a3-63ccc3e12f6mr634715d50.4.1759870808994; Tue, 07 Oct 2025 14:00:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ruben Laguna Date: Tue, 7 Oct 2025 22:59:57 +0200 X-Gm-Features: AS18NWDUmOPChXYxJPCGiAyvxi0huHNehGDKmKuOIQh54Z6N8Hxf8yXOi2dPUN0 Message-ID: Subject: Re: role to access all information_schema.*? To: Laurenz Albe Cc: pgsql-admin@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000006bb8cd064097da73" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006bb8cd064097da73 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Ruben Laguna 4:05=E2=80=AFPM (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 =E2=80=94 Database languages SQL Part 1= 1: 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=C3=A9n On Tue, Oct 7, 2025 at 3:33=E2=80=AFPM Laurenz Albe 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 discus= s > > 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.* an= d > > 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 i= t > > 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 > --=20 /Rub=C3=A9n --0000000000006bb8cd064097da73 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Ruben Laguna=C2=A0<= span class=3D"gmail-cfXrwd"><ruben.laguna@gmail.com>

4:05=E2=80=AFPM (6 hours ago)
> Y= ou should use the=C2=A0PostgreSQL=C2=A0cata= log 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=C2=A0= PostgreSQL=C2=A0connector=C2=A0https://docs.open-metadata.org/latest/connectors/database/postgres=C2=A0reads from information_schema.= *, I can't change that.=C2=A0
I could write my own connector that re= ads from pg_* and writes to OM api but it seems like a lot of work.=C2=A0

> The inform= ation_schema is specified by the SQL standard, and the standard
> dec= rees 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=C2=A0 it's=C2=A0 ISO/IEC 9075-11:2023 Information tech= nology =E2=80=94 Database languages SQL Part 11: Information and definition= schemas (SQL/Schemata)=C2=A0https://www.iso.org/standard/76586.html.=C2=A0<= br>But I know that Google BigQuery has this `roles/bigquery.metadataViewer`= that when given to a user it allows to see everything in INFORMATION_SCHEM= A. I guess that they are not compliant with the standard.=C2=A0

Do y= ou know what the standard says exactly, does it outright=C2=A0bans using an= y special means like having (pg_metadata_viewr or pg_read_information_schem= a, etc). as "access privilege"? Do you think it's hopeless to= propose this in=C2=A0pgsql-hackers?
Best regards/Rub=C3=A9n

On Tue, Oct 7, 2025 at 3:33=E2=80=AFPM 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_sche= ma.tables`
> the tables that the user has been granted SELECT privilege.=C2=A0
>
> So, my question is:=C2=A0 Is=C2=A0 there is some other way to get a us= er to be a
> "metadata viewer"=C2=A0 without been a user that also has ac= cess to the data in
> those tables?=C2=A0
>
> Do you know if there is any plan to add such a role? Has it been discu= ss
> 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.* a= nd
> 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 versio= n,
but at least everybody can see all their data.

Yours,
Laurenz Albe


--
/Rub=C3=A9n
--0000000000006bb8cd064097da73--