Content-Type: multipart/alternative;
 boundary="------------BDL2ja4l50XR095C5xmTiwF0"
Message-ID: <c9a974a3-3f80-4911-82f0-da0476253195@dunslane.net>
Date: Thu, 19 Mar 2026 16:14:41 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_get__*_ddl consolidation
To: Mahendra Singh Thalor <mahi6run@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <e247c261-e3fb-4810-81e0-a65893170e94@dunslane.net>
 <CAKYtNArapMAz-EfLwYPhX7diSg4gXA5OgsULM_ZYGGgtrgztVw@mail.gmail.com>
From: Andrew Dunstan <andrew@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: 
 <CAKYtNArapMAz-EfLwYPhX7diSg4gXA5OgsULM_ZYGGgtrgztVw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/c9a974a3-3f80-4911-82f0-da0476253195%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------BDL2ja4l50XR095C5xmTiwF0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


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.


cheers


andrew

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

--------------BDL2ja4l50XR095C5xmTiwF0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2026-03-19 Th 3:55 PM, Mahendra
      Singh Thalor wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKYtNArapMAz-EfLwYPhX7diSg4gXA5OgsULM_ZYGGgtrgztVw@mail.gmail.com">
      <pre wrap="" class="moz-quote-pre">On Fri, 20 Mar 2026 at 00:04, Andrew Dunstan <a class="moz-txt-link-rfc2396E" href="mailto:andrew@dunslane.net">&lt;andrew@dunslane.net&gt;</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="" class="moz-quote-pre">
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]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net">https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net</a>

[2]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com">https://www.postgresql.org/message-id/flat/CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com</a>

[3]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com">https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com</a>

[4]
<a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com">https://www.postgresql.org/message-id/flat/CAKWEB6rmnmGKUA87Zmq-s=b3Scsnj02C0kObQjnbL2ajfPWGEw@mail.gmail.com</a>


--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
      </blockquote>
      <pre wrap="" class="moz-quote-pre">
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.</pre>
    </blockquote>
    <p><br>
    </p>
    <p>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.<br>
    </p>
    <p><br>
    </p>
    <p>cheers</p>
    <p><br>
    </p>
    <p>andrew<span style="white-space: pre-wrap">
</span><span style="white-space: pre-wrap">
</span></p>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------BDL2ja4l50XR095C5xmTiwF0--