Message-ID: <7daf5cec-4eae-48e0-883e-684476b57531@yeah.net>
Date: Thu, 13 Nov 2025 12:48:21 +0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statement
From: Quan Zongliang <quanzongliang@yeah.net>
To: Akshay Joshi <akshay.joshi@enterprisedb.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: 
 <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
 <d6a8bb90-9b55-4124-8c80-b5a5c1ee460c@yeah.net>
Content-Language: en-US
In-Reply-To: <d6a8bb90-9b55-4124-8c80-b5a5c1ee460c@yeah.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7daf5cec-4eae-48e0-883e-684476b57531%40yeah.net>
Precedence: bulk


On 11/13/25 12:17 PM, Quan Zongliang wrote:
> 
> 
> On 11/12/25 8:04 PM, Akshay Joshi wrote:
>> Hi Hackers,
>>
>> I’m submitting a patch as part of the broader Retail DDL Functions 
>> project described by Andrew Dunstan https://www.postgresql.org/ 
>> message- id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net 
>> <https:// www.postgresql.org/message-id/945db7c5-be75-45bf-b55b- 
>> cb1e56f2e3e9%40dunslane.net>
>>
>> This patch adds a new system function 
>> pg_get_database_ddl(database_name/ database_oid, pretty), which 
>> reconstructs the CREATE DATABASE statement for a given database name 
>> or database oid. When the pretty flag is set to true, the function 
>> returns a neatly formatted, multi-line DDL statement instead of a 
>> single-line statement.
>>
>> *Usage examples:*
>>
>> 1) SELECT pg_get_database_ddl('test_get_database_ddl_builtin');  -- 
>> *non-pretty formatted DDL*
>> pg_get_database_ddl
>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>    CREATE DATABASE test_get_database_ddl_builtin WITH OWNER = 
>> regress_ddl_database ENCODING = "UTF8" LC_COLLATE = "C" LC_CTYPE = "C" 
>> BUILTIN_LOCALE = "C.UTF-8" COLLATION_VERSION = "1" LOCALE_PROVIDER = 
>> 'builtin' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION 
>> LIMIT = -1;
>>
>>
>> 2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', true); 
>> -- *pretty formatted DDL*
>>
>> CREATE DATABASE test_get_database_ddl_builtin
>>           WITH
>>           OWNER = regress_ddl_database
>>           ENCODING = "UTF8"
>>           LC_COLLATE = "C"
>>           LC_CTYPE = "C"
>>           BUILTIN_LOCALE = "C.UTF-8"
>>           COLLATION_VERSION = "1"
>>           LOCALE_PROVIDER = 'builtin'
>>           TABLESPACE = pg_default
>>           ALLOW_CONNECTIONS = true
>>           CONNECTION LIMIT = -1;
>>
>> 3) SELECT pg_get_database_ddl(16835);      -- *non-pretty formatted 
>> DDL for OID*
>> 4) SELECT pg_get_database_ddl(16835, true);  -- *pretty formatted DDL 
>> for OID*
>>
>> The patch includes documentation, in-code comments, and regression 
>> tests, all of which pass successfully.
>> *
>> **Note:* To run the regression tests, particularly the pg_upgrade 
>> tests successfully, I had to add a helper function, ddl_filter (in 
>> database.sql), which removes locale and collation-related information 
>> from the pg_get_database_ddl output.
>>
> I think we should check the connection permissions here. Otherwise:
> 
> postgres=> SELECT pg_database_size('testdb');
> ERROR:  permission denied for database testdb
> postgres=> SELECT pg_get_database_ddl('testdb');
> 
>                          pg_get_database_ddl
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   CREATE DATABASE testdb WITH OWNER = quanzl ENCODING = "UTF8" 
> LC_COLLATE = "zh_CN.UTF-8" LC_CTYPE = "zh_CN.UTF-8" LOCALE_PROVIDER = 
> 'libc' TABLESPACE = pg_default ALLOW_CONNECTIONS = true CONNECTION LIMIT 
> = -1;
> (1 row)
> 
> Users without connection permissions should not generate DDL.
> 

The "dbOwner" is defined as a null pointer.
char	   *dbOwner = NULL;

Later, there might be a risk of it not being assigned a value.
    if (OidIsValid(dbForm->datdba))
       dbOwner = GetUserNameFromId(dbForm->datdba, false);

Although there is no problem in normal circumstances here. Many parts of 
the existing code have not been checked either. Since this possibility 
exists, it should be checked before using it. Just like the function 
roles_is_member_of (acl.c).

if (dbOwner)
   get_formatted_string(&buf, prettyFlags, 1, "OWNER = %s",
			 quote_identifier(dbOwner));

> Regards,
> Quan Zongliang
> 
>> -----
>> Regards,
>> Akshay Joshi
>> EDB (EnterpriseDB)
>>
>>
>>
> 
>