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

On Thu, Nov 13, 2025 at 9:47 AM Quan Zongliang <quanzongliang(at)yeah(dot)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-
> > 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.
>

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.

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