On 11/13/25 4:30 PM, Akshay Joshi wrote:
On Thu, Nov 13, 2025 at 9:47 AM Quan Zongliang <[email protected]
<mailto:[email protected]>> 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)
>
>
>
