Re: pg_get__*_ddl consolidation

On Fri, 20 Mar 2026 at 01:44, Andrew Dunstan <andrew@dunslane.net> wrote:
>
>
> On 2026-03-19 Th 3:55 PM, Mahendra Singh Thalor wrote:
>
> On Fri, 20 Mar 2026 at 00:04, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Greetings
>
> Euler Taveira and I have been working on consolidating these patches.
>
> These patches came out of a suggestion from me some time back [1], and I
> used it as the base for some work at an EDB internal program. Perhaps I
> was motivated a bit by Mao's dictum "Let a hundred flowers bloom; let a
> hundred schools of thought contend." I wanted to see what people would
> come up with. Therefore, if this has seemed a bit chaotic, I apologize,
> both to the authors and to the list. I won't do things quite this way in
> future.
>
> Rather than adding to the already huge ruleutils.c, we decided to create
> a new ddlutils.c file to contain these functions and their associated
> infrastructure. There is in fact a fairly clean separation between these
> functions and ruleutils. We just need to expose one function in ruleutils.
>
> We (Euler and I) decided to concentrate on setting up common
> infrastucture and ensuring a common argument and result structure. In
> this first round, we are proposing to add functions for getting the DDL
> for databases, tablespaces, and roles. We decided to stop there for now.
> This sets up a good basis for dealing with more object types in future.
> To the authors of the remaining patches - rest assured you have not been
> forgotten.
>
> Patch 1 sets up the functions used by the rest for option parsing. see [2]
> Patch 2 implements pg_get_role_dll see[3]
> Patch 3 implements pg_get_tabespace_ddl see [4]
> Patch 4 implements pg_get_database_ddl see [2]
>
>
> cheers
>
>
> andrew
>
>
> [1]
> https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
>
> [2]
> https://www.postgresql.org/message-id/flat/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com
>
> [3]
> https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com
>
> [4]
> https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
> Hi all,
> I was reading these patches and found that any user can get the
> definition of database/roles by pg_get__*_ddl. I think these functions
> should be restricted only to super users as these are cluster level
> objects.
>
>
> You could construct these functions using plpgsql. The information isn't hidden from non-superusers. So what exactly would making these functions superuser-only achieve? Now it's true that the user might not be able to execute the DDL. But that's not the point.

Thanks Andrew for the clarification.

>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com

Sorry, my question might be stupid. Can we use these functions
directly into our dump utilities so that common code can be removed
and if there is any syntax change for a particular object, then no
need to change into different-2 places, rather we just need to change
it into ddlutils.c file only.

-- 
Thanks and Regards
Mahendra Singh Thalor
EnterpriseDB: http://www.enterprisedb.com