From: Japin Li <japinli@hotmail.com>
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: jian he <jian.universality@gmail.com>,  Marcos Pegoraro
 <marcos@f10.com.br>,  Mark Wong <markwkm@gmail.com>,  =?utf-8?Q?=C3=81lva?=
 =?utf-8?Q?ro?= Herrera
 <alvherre@kurilemu.de>,  pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE
 POLICY statement
In-Reply-To: 
 <CANxoLDfdZTLLJqXnnfUYG-Uw4LHBKKnB5f1XOdaQ3ZET=K1qnw@mail.gmail.com>
	(Akshay Joshi's message of "Fri, 22 May 2026 19:02:46 +0530")
References: 
 <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
	<202510151529.s3fpwsgben57@alvherre.pgsql>
	<CANxoLDfXKWRQ0KZFtaChr2NX3UWSQq3ji7OJqBp-_-th2Zj6Fg@mail.gmail.com>
	<aP-hllbRdgqbmB8L@ltdrgnflg2>
	<CANxoLDc8UnFuKA2RX6UR_KZWRH6itmrhXK7hoFyF=5kCyfFOGA@mail.gmail.com>
	<CANxoLDfyk_+h91FNq5VNemXTBpES0aLPtLp2myTyWgquUHSQ3A@mail.gmail.com>
	<CANxoLDfw=ERY89RR08s+qXwgUQvLCFF0pVG_e0fDT14rwZsWRg@mail.gmail.com>
	<CAB-JLwYpfK3WCFJ1MT1sAB7Zj9w61wv7fMuCH2vZGW9W4JP15w@mail.gmail.com>
	<CANxoLDdV-ciaYqhWiZCRh3Nv+iW9o1LOGvS83TCZ=5X261OjNA@mail.gmail.com>
	<CAB-JLwaDPJupWecAsvmNc7y7L5cQjV5P9epSFsTed1hEDbqUSA@mail.gmail.com>
	<CANxoLDczdpL9XZhqrA0f8aOocnr_V0az1dtw8iq9YJoN+tWNbw@mail.gmail.com>
	<CANxoLDfD_MnK+2=XrWr_fZRh1qbLriO=7MbbZ9UAcwktydFnBA@mail.gmail.com>
	<CACJufxETscX67o0t9S6Y8EBBVE21R0zAEG4eXJUYHLidLNfEKQ@mail.gmail.com>
	<CANxoLDfdZTLLJqXnnfUYG-Uw4LHBKKnB5f1XOdaQ3ZET=K1qnw@mail.gmail.com>
User-Agent: mu4e 1.14.1; emacs 30.2
Date: Sat, 23 May 2026 00:24:54 +0800
Message-ID: 
 <SY7PR01MB109217288F5A2BD343E7A7727B60F2@SY7PR01MB10921.ausprd01.prod.outlook.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY7PR01MB10921:EE_|SY0PR01MB9310:EE_
X-MS-Office365-Filtering-Correlation-Id: 3fca9e18-03a6-4097-5601-08deb81eaa84
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|6090799003|55001999006|24021099003|24121999003|22091999003|23021999003|11091999009|12121999013|15080799012|19110799012|5072599009|41001999006|8060799015|52005399003|40105399003|26121999007|3412199025|440099028;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?UW5FSUVQRVBSYUJIbXF3czZhZ0krNnR6Mml1WTBDVVhIWm01NUNlVitRQ1k5?=
 =?utf-8?B?c2ZVcG5uNzFZTlNGMzNkOTFzUlZGdzR2WmIwR0NLSVU5TjlZaXpSN2g5V1VR?=
 =?utf-8?B?SlplQlJ0S2xta0w2OE5OTWIvWDN0TkYxaTFTaXNVa2ExNjk4L3JyTXRlck5q?=
 =?utf-8?B?cmV5dm54TFgzQzlxdEhYY29QWk1VUWlwckxQUHEzVjFEU0JiR1JiZG5wcEEy?=
 =?utf-8?B?YzRFNFJrcTZWZHNqRmhKdjBRYXdIL3F5d0ZURTRPbUdqamtjZi8zaHZaR0Yw?=
 =?utf-8?B?YnM1bWIxcFBFa2dtb2lFMElycTlRNGEwbWNXL0NIcitTTlh4T05GS2RaNUtw?=
 =?utf-8?B?a1J6NGZGaU11VEV3enQwRHJrMFNGMlU0MnNtN2ZIZk1Nbjg0L2MvcmRsSFJZ?=
 =?utf-8?B?WkVYTDlEY05ESndwUTBEQXh5ekZaSlVWeDArdkR4OThlSEVOakZzVnhzMHU3?=
 =?utf-8?B?OEJ2NjRHNDUyMWJGSjFJRmwwR2NVTVNUVFRwTDVGWU1UTUxyUFBmaU8vQW1T?=
 =?utf-8?B?Rm80bkR5V1FGWlczKzFjNE8rbmdkWEl1bzhpanNySmZwS0ZsbjNmWnB3bmEr?=
 =?utf-8?B?eUV2SzA4N1FObDErSUJWOTRhLzg1aU5iQzBKZ2IremdZMy9qQmhHQndzSGlt?=
 =?utf-8?B?c0wrK3R0RmJUdnV5ZGxOWEJqOU8rWHpjUXgzNlhZZjFlQnBvWU1yaFJOUDEr?=
 =?utf-8?B?a0tOODNyUTJDdFRFbjdIVHQ0b0Z3dWN5NHNRUXVzNmVYNXB6aGdvZHhuYzBp?=
 =?utf-8?B?OFo3Q2pqbC91OHpLSlorMHZkN3ZwMUNIMnFjUlZyTklwZEZCUFFtYklINFlq?=
 =?utf-8?B?NVR1S1pvcXdvcjNPRDJZNmc5UlhvSnloUzY3b0dSdWVXWm5FOEh2dVRLcUlG?=
 =?utf-8?B?WXc4bWkyaHBpMzBnWUNQMjlkdlFCcm56Z1FjLzM0TkhkL0dEMEFiYkl0T0pk?=
 =?utf-8?B?alVMUzNTNFpjOFJRUVFSclcxVWlaZGFZbjFkZE96YUV1ekg5Vk5YVjJ3VTcz?=
 =?utf-8?B?WmdtSW1PWnh2RUk0MVd2SVpzMHZLeEg3aDJISnIrRzlCeEg3NWp3U24zQ1FO?=
 =?utf-8?B?c25zZTE4SUx4SzhGY0NYK21QMnRQSU15amgyRDhwZE1oQXU2Z3NTYTlaWXpp?=
 =?utf-8?B?WkhMZWxpWmZZRHpvV2dCRk44ZjYveDd4N3dsMUJKcUVGdzZJNkdpWU03MUpR?=
 =?utf-8?B?dlk4OGNNZ29XRm1HRHF4S2lBblBZc1FpemhnVG04bGQ1cHd1RGdZS01PN213?=
 =?utf-8?B?RndIbHNTZEpqSVVHaU15NTZFVHJPMkJramRjWU1YV1d4RUo0UUcweWgzT2FJ?=
 =?utf-8?B?VU4zZXJiazRZQVBiTG5GMGR4VGRybmVDVDBiTDgxSmlCdTdINk9VMVptV3pF?=
 =?utf-8?B?emxaRkkzcWlZUk5panJ3NzhFZ2xzVjRPZVpsakZWaFF5aW9vMlh5TmpjeFhQ?=
 =?utf-8?B?TjlGb0tWamFCU3crRFpFdk9JR1B1dHVJTHYrNVc0UyswQng4NjZkRFFoYm80?=
 =?utf-8?B?YitwV0dkUzR6elZLQkg3QXhuYlB3dWdPeCtDU01xek92ejczMVFKQ3NzRTI0?=
 =?utf-8?Q?8tUwGg2Hxummhjq7nqoa3+zUBXI5cPfr4BDc+tg23XPoyo?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?KzBFZkgzcktZdVZxeWxOclVMYm42c0JmSTdhM3FBL2hrVzNVdTJRM24rNkFB?=
 =?utf-8?B?azRhMnFwbDJlRWN0NVFYSXlKSmdvdmpPYlBtbXJkQXZMbU1KNU14YnJYYjJP?=
 =?utf-8?B?MkE3SENOc01VM2twSWo4WE0wMkRzVGRYQUNGdEVOdzg1V0NVN1l2Q1BpWmg1?=
 =?utf-8?B?c0F1U1RFK05MRkU0RU9NUkVWQmV2dEdORTIvcjFtc3NTTU4vWExyT2J3UU9r?=
 =?utf-8?B?WkEvNktna1BYZTdwV3lJM0tRYklRejVsMkRRVkIrdUEyVG81bVY3S25pMXZB?=
 =?utf-8?B?YlVOTlE1bU1QRTcrYktZV2oxUXczbnErMVptTDVGeEc2ZnFMMkJyU0J4V2Rw?=
 =?utf-8?B?eWFrejZmcThYQ3g1UHFXeXhJc0xXZWxjRmhQOGpqbTlKdkRxOXRRcm42ZVR3?=
 =?utf-8?B?K2dkN2hiaHh3UWhFa2EzSklwUjBLWk5NUkhRaHhWZWRtMHlTSDNzaGdMZUND?=
 =?utf-8?B?KytSR0I2bWRHdWZNWlZXSmJMQW9lWjF3RStGa09kSVJrSXBZZGk4QUVZZWNL?=
 =?utf-8?B?dVBPTjNMUTcxTDFVUEJsaGM4NVhsaktWY1pzOVVGRDFPWStSenA0eDg3R1ZB?=
 =?utf-8?B?UkRCSVNiQlp2cTl4L3p0bjdIRHduWW5PVmpoQzlVVi84OFVLQmRaNUpLMUZB?=
 =?utf-8?B?QS9pa3NqYUpMc0l3bzBBbW5rUGY2QnlzTVpneHVFWGowdHhtTGx3MmY0UWhk?=
 =?utf-8?B?K3RnSi93ZzNGa3EyekphZ29zM05kemJmRzVWalZkQU5tQUJZeDAvNkZNc1E0?=
 =?utf-8?B?dEJZZ05TY01QY3NpWEhPNmFFbFNZczA4Y3AzQXg4RGRGSFNLYU1FaWIvNWJM?=
 =?utf-8?B?SHViZjNxSE53ZFExQmtzYk1MTVhUaVM2UWxUblR6bDk5OWlnL1pqL003M05V?=
 =?utf-8?B?aTNOeGN5cWttNVpyVlkyd1VpbDJhSlIrcWhud1F2ZFBSMjV3U2xUcVM5ejQy?=
 =?utf-8?B?MVovbWIydDl2WG9VRzRxTVZIemNDeUtHNmZSb1ZEaFJrUUJtZnNOend2dFYy?=
 =?utf-8?B?RXlKOGFNdUIwSkJkQktPSTBXYmtwTkR2WkNNZlJ4QUl1ajk5MUlha2RaaHZy?=
 =?utf-8?B?SlpBQ3BNMVBSQjJBbkxLRlA4eHlRVXF2L3NvbklGZWVKRlZLcXBmWmFRWEpZ?=
 =?utf-8?B?NGZKQTRTVkFjS05XWnV5eHI4M2ZDNzJJVlFXeWxCd1pqcE56RThCdVFTcXNZ?=
 =?utf-8?B?S05Ud0c3clVxbHBlc092T0FqSC9TMExsc2lidnRjWEdYZmJJRFpIdkpYdFNU?=
 =?utf-8?B?VzIwaWxmdVBRS1FldDFObCtWYWNtZ1YyY0lMcjE4SEs0VUd4MHBWR21IcW5k?=
 =?utf-8?B?N3Z4bGpYWlRmeTBlN2xyeWYwblA4eExXeDNaVzRDeFlSTVUrUC9JeW42SEs5?=
 =?utf-8?B?TGNBMWpxUGdYemo0dVdNaTNCVjJsNFIxSnBjclVtVUs2R2ZRSkM4TkNXMEd3?=
 =?utf-8?B?c0dKcG44d0cyRjdSZFVlVkp3MTVrT3YyVUdoRE9sU1VnUDBrTWhCRUVJZlFT?=
 =?utf-8?B?ekUxUGdtQ1FwZXpmRkVYUUttdkRkRFJaWWQ3amRSekx0ZE1NcDFiNDRwWURF?=
 =?utf-8?B?Qk9TQzJqMFNuQ0FqSFVCTE9wVkt3ZmFVWlJMcEMyc1JuaGpaNFNra3AzL3Fq?=
 =?utf-8?B?VWVYc1pDLysrMzlZNkxWZW9jejhmUGtObTRxUW9Ec2FUelM4dktpR1dYSHNG?=
 =?utf-8?B?Tlk4aithOFBvTGFzdmRrMnRQSTF0Zyt5N3dIVWZBSlNmWUdFZkRzeHo0MnNC?=
 =?utf-8?B?c0d5R24xVk5DMmx3KzM0MXdIZHBrM1ZjSlM0OFV4VmxOSU1vUWZhUlpZd3JK?=
 =?utf-8?B?dENSU1ZTRjM0cFdpbmZndkZnVko4Wk9JWG5pdFV0YzNmVXpjQlNvNFlBR2Ra?=
 =?utf-8?Q?byQxukTrL4RXn?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 3fca9e18-03a6-4097-5601-08deb81eaa84
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 22 May 2026 16:24:58.8549
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY0PR01MB9310
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/SY7PR01MB109217288F5A2BD343E7A7727B60F2%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk


Hi, Akshay

On Fri, 22 May 2026 at 19:02, Akshay Joshi <akshay.joshi@enterprisedb.com> =
wrote:
> Hi hackers,                                                              =
                                               =20
>                     =20
>
> Following the recently committed pg_get_database_ddl(), which adopted a V=
ARIADIC options text[] style for
> DDL-reconstruction functions, here is a patch in the same spirit for row-=
level security policies.
>
> The new function:
>     pg_get_policy_ddl(table regclass, policy_name name, VARIADIC options =
text[]) RETURNS setof text=20
>
> Reconstructs the CREATE POLICY statement for the named policy on the give=
n table, returning the result as a single row.
>
> The currently supported option is pretty (boolean) for formatted output. =
                                               =20
>      =20
>     SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
>     SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true'=
);
>
> NULL inputs for table or policy_name return no rows. Unknown option names=
, invalid boolean values, and duplicate options
> are reported as errors consistent with the pattern established by pg_get_=
database_ddl().=20
>
> The patch includes documentation updates in func-info.sgml and regression=
 tests in rowsecurity.sql covering
> PERMISSIVE/RESTRICTIVE, each command type (ALL/SELECT/INSERT/UPDATE/DELET=
E), TO role lists, both USING and WITH CHECK
> clauses, pretty/non-pretty output, and the error paths above.
>
> Patch is ready for review.
>
> On Mon, Jan 5, 2026 at 8:00=E2=80=AFPM jian he <jian.universality@gmail.c=
om> wrote:
>
>  On Thu, Nov 20, 2025 at 5:27=E2=80=AFPM Akshay Joshi
>  <akshay.joshi@enterprisedb.com> wrote:
>  >
>  > Attached is the v8 patch for your review, with updated variable names =
and a rebase applied.
>  >
>  hi.
>
>  +     <tbody>
>  +      <row>
>  +       <entry role=3D"func_table_entry"><para role=3D"func_signature">
>  +        <indexterm>
>  +         <primary>pg_get_policy_ddl</primary>
>  +        </indexterm>
>  +        <function>pg_get_policy_ddl</function>
>  +        ( <parameter>table</parameter> <type>regclass</type>,
>  <parameter>policy_name</parameter> <type>name</type>, <optional>
>  <parameter>pretty</parameter> <type>boolean</type> </optional> )
>  +        <returnvalue>text</returnvalue>
>  +       </para>
>  +       <para>
>  +        Reconstructs the <command>CREATE POLICY</command> statement fro=
m the
>  +        system catalogs for a specified table and policy name. The resu=
lt is a
>  +        comprehensive <command>CREATE POLICY</command> statement.
>  +       </para></entry>
>  +      </row>
>  +     </tbody>
>
>   ( <parameter>table</parameter> <type>regclass</type> ...
>  this line is way too long, we can split it into several lines, it
>  won't affect the appearance.
>
>  like:
>          <function>pg_get_policy_ddl</function>
>          ( <parameter>table</parameter> <type>regclass</type>,
>            <parameter>policy_name</parameter> <type>name</type>,
>            <optional> <parameter>pretty</parameter>
>  <type>boolean</type> </optional> )
>          <returnvalue>text</returnvalue>
>
>  Also, the explanation does not mention that the default value of
>  pretty is false.
>
>  index 2d946d6d9e9..a5e22374668 100644
>  --- a/src/backend/catalog/system_functions.sql
>  +++ b/src/backend/catalog/system_functions.sql
>  @@ -657,6 +657,12 @@ LANGUAGE INTERNAL
>   STRICT VOLATILE PARALLEL UNSAFE
>   AS 'pg_replication_origin_session_setup';
>
>  +CREATE OR REPLACE FUNCTION
>  +  pg_get_policy_ddl(tableID regclass, policyName name, pretty bool
>  DEFAULT false)
>  +RETURNS text
>  +LANGUAGE INTERNAL
>  +AS 'pg_get_policy_ddl';
>  +
>
>  The partial upper casing above has no effect; it's the same as
>  ``pg_get_policy_ddl(tableid regclass, policyname name, pretty bool
>  DEFAULT false)``
>

Thanks for updating the patch.  Just one nitpick below.

+		append_ddl_option(&buf, pretty, 4, "USING (%s)",
+						  TextDatumGetCString(expr));

The expression string already contains the parentheses, so we can omit them
here, as well as in the WITH CHECK clause.

--=20
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.