Message-ID: <8b5a7c1d55c1c938cb8c39da2cf29711c6dc9247.camel@cybertec.at>
Subject: Re: role to access all information_schema.*?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Ruben Laguna <ruben.laguna@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Date: Wed, 08 Oct 2025 08:46:23 +0200
In-Reply-To: 
 <CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com>
References: 
	<CAFOAOWJNJKLr8-YS7Q5wYnUHTjjaTbU6=rmTF_jW5W8fNfFamA@mail.gmail.com>
	 <b9ae240ad537f9a2088c022bb9a34ff33a1058e1.camel@cybertec.at>
	 <CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/8b5a7c1d55c1c938cb8c39da2cf29711c6dc9247.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-10-07 at 22:59 +0200, Ruben Laguna wrote:
> Do you know what the standard says exactly, does it outright=C2=A0bans us=
ing any
> special means like having (pg_metadata_viewr or pg_read_information_schem=
a,
> 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
=3D ( SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA_CATALOG_NAME );

> Do you think it's hopeless to propose this in=C2=A0pgsql-hackers?

I agree with Tom, there is little hope.

Yours,
Laurenz Albe