public inbox for [email protected]
help / color / mirror / Atom feedFrom: Quan Zongliang <[email protected]>
To: Akshay Joshi <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
Date: Thu, 13 Nov 2025 17:36:19 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANxoLDff7Pu9-hBcxaFDWo05=Rqr40e+iMxxC3Z7dp+pBkOmLg@mail.gmail.com>
References: <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
<[email protected]>
<CANxoLDff7Pu9-hBcxaFDWo05=Rqr40e+iMxxC3Z7dp+pBkOmLg@mail.gmail.com>
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)
> >
> >
> >
>
view thread (38+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox