Re: pg_get__*_ddl consolidation

On 2026-03-19 Th 3:55 PM, Mahendra Singh Thalor wrote:
> On Fri, 20 Mar 2026 at 00:04, Andrew Dunstan<andrew(at)dunslane(dot)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(at)mail(dot)gmail(dot)com
>>
>> [3]
>> https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471(at)gmail(dot)com
>>
>> [4]
>> https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw(at)mail(dot)gmail(dot)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.

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com