Message-ID: <8b5b3b7b-c2ed-4850-b65c-c504a9982135@yeah.net>
Date: Thu, 13 Nov 2025 17:36:19 +0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE statement
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
References: 
 <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
 <d6a8bb90-9b55-4124-8c80-b5a5c1ee460c@yeah.net>
 <CANxoLDff7Pu9-hBcxaFDWo05=Rqr40e+iMxxC3Z7dp+pBkOmLg@mail.gmail.com>
Content-Language: en-US
From: Quan Zongliang <quanzongliang@yeah.net>
In-Reply-To: 
 <CANxoLDff7Pu9-hBcxaFDWo05=Rqr40e+iMxxC3Z7dp+pBkOmLg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/8b5b3b7b-c2ed-4850-b65c-c504a9982135%40yeah.net>
Precedence: bulk


On 11/13/25 4:30 PM, Akshay Joshi wrote:
> 
> On Thu, Nov 13, 2025 at 9:47 AM Quan Zongliang <quanzongliang@yeah.net 
> <mailto:quanzongliang@yeah.net>> 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- <https://www.postgresql.org/message->
>      > id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
>     <http://40dunslane.net> <https://
>      > www.postgresql.org/message-id/945db7c5-be75-45bf-b55b- <http://
>     www.postgresql.org/message-id/945db7c5-be75-45bf-b55b->
>      > cb1e56f2e3e9%40dunslane.net <http://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.
> 
> 
> pg_database_size() requires CONNECT or pg_read_all_stats privileges 
> since it accesses on-disk storage details of a database, which are 
> treated as sensitive information. In contrast, other system functions 
> might not need such privileges because they operate within the connected 
> database or reveal less sensitive data.
> 
> In my view, the pg_get_database_ddl() function *should not* require 
> CONNECT or pg_read_all_stats privileges for consistency and security.
>
Agree.

But what about the following scenario? If there is no permission to 
access pg_database. Shouldn't the DDL be returned?

postgres=> SELECT * FROM pg_database;
ERROR:  permission denied for table pg_database
postgres=> SELECT pg_get_database_ddl('testdb');

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