public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Herrera <[email protected]>
To: Quan Zongliang <[email protected]>
Cc: Akshay Joshi <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Fri, 14 Nov 2025 12:12:21 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>

On 2025-Nov-13, Quan Zongliang wrote:

> A more specific example. Originally, it was impossible to obtain the
> definition of "testdb" by accessing pg_database:
> 
>   postgres=> SELECT * FROM pg_database WHERE datname='testdb';
>   ERROR:  permission denied for table pg_database

Hmm.  So I was thinking that running things in this mode (where catalog
access is restricted) has never been supported.  But you're right that
we would be opening a hole that we don't have today, because if the
admin closes down permissions on pg_database, then this new function
would be a way to obtain information that the user can't currently
obtain.

My further point was to be that you still need to obtain a list of
database names or OIDs in order to do anything of value.  But it turns
out that this is extremely easy and quick to do, with something like

SELECT i, pg_describe_object('pg_database'::regclass, i, 0)
FROM generate_series(1, 1_000_000) i
WHERE pg_describe_object('pg_database'::regclass, i, 0) IS NOT NULL;

... and with this function, the user could again obtain everything about
the database even when they can't read the catalog directly.

Maybe checking privs for the database being dumped is enough protection
against this -- the equivalent of has_database_privilege( ..., 'CONNECT') 
I suppose.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"¿Qué importan los años?  Lo que realmente importa es comprobar que
a fin de cuentas la mejor edad de la vida es estar vivo"  (Mafalda)

view thread (38+ messages)  latest in thread

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: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
  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