public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Ruben Laguna <[email protected]>
Cc: [email protected]
Subject: Re: role to access all information_schema.*?
Date: Wed, 08 Oct 2025 08:46:23 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com>
References: <CAFOAOWJNJKLr8-YS7Q5wYnUHTjjaTbU6=rmTF_jW5W8fNfFamA@mail.gmail.com>
	<[email protected]>
	<CAFOAOWKyp4zHiajAJbcdE33jN=0FhVuNhknc5AHa9PMy0ngu9A@mail.gmail.com>

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

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: <[email protected]>

* 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