Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

From: Álvaro Herrera <[email-masked]>
Date: Fri, 14 Nov 2025 12:12:21 +0100

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)

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 17 Nov 2025 20:09:54 +0530

The v3 patch adds a check for the CONNECT privilege on the target database
for pg_get_database_ddl(). This aligns its security model with functions
like pg_database_size(). Note that revoking permissions on the *pg_database*
table alone is insufficient to restrict DDL access; users must manually
revoke permission on the pg_get_database_ddl() function itself if
restriction is desired.

Attached is the v3 patch ready for review.