Обсуждение: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement

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.

Regards,
Quan Zongliang

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

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)
>>
>>
>>
> 
> 

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)
>      >
>      >
>      >
> 

On 11/13/25 8:28 PM, Álvaro Herrera wrote:

>> 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');
> 
> Hmm, what scenario is this?  Did you purposefully REVOKE the SELECT
> privileges from pg_database somehow?
> 
Yes. I revoked the access permission using the REVOKE command.

The pg_get_xxx_ddl function is actually revealing system information to 
the users. This is equivalent to accessing the corresponding system 
table. So I think we should consider this issue.

The access permission to the system tables has been revoked. This user 
is unable to directly view the contents of the system tables from the 
client side via SQL. However, it is still possible to obtain the object 
definitions (which was previously inaccessible) through pg_get_xxx_ddl. 
This is more like a security flaw.

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

And after having this function. However, users can view these in another 
format.

   postgres=> SELECT pg_get_database_ddl('testdb');
   ------------- ...
   CREATE DATABASE testdb WITH OWNER = quanzl ENCODING = "UTF8" ...

Perhaps it's just that I'm overthinking things. What do you think about it?

On Thu, Nov 13, 2025 at 02:02:30PM +0530, Akshay Joshi wrote:
> On Thu, Nov 13, 2025 at 10:18 AM Quan Zongliang <quanzongliang@yeah.net>
> wrote:
> 
> >
> >
> > 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));
> >
> 
>  Fixed the given review comment. I've attached the v2 patch ready for
> review.
> 

Thanks for updating the patch, some comments on v2.

1.
Should we merge the functions pg_get_database_ddl(oid, [boolean]) and
pg_get_database_ddl(name, [boolean]) in documentation, following the
precedent set by pg_database_size in func-admin.sgml.

2.
+ * noOfTabChars - indent with specified no of tabs.

How about using 'indent with specified number of tab characters'?
And for variable noOfTabChars, I'd like use nTabs or nTabChars.

3.
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid.
+ *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */

A copy-paste error resulted in an incorrect comments (dbName).

-- 
Best regards,
Japin Li
ChengDu WenWu Information Technology Co., LTD.

Hi Akshay,

I quick went through the patch, I do see some problems, but I need some time to wrap up, so I will do a deep review
nextweek. In the meantime, I want to first ask that why there is no privilege check? I think that’s a serious issue. 

> On Nov 13, 2025, at 16:32, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
>
>
> <v2-0001-Add-pg_get_database_ddl-function-to-reconstruct-CREATE.patch>

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

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)