Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Avoid leaking system path from pg_available_extensions

  2. postgres_fdw, dblink: Validate use_scram_passthrough values

  3. Make pg_get_expr() more bulletproof.

  1. [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-15T13:37:12Z

    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-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net

This patch adds a new system function pg_get_policy_ddl(table, policy_name,
pretty), which reconstructs the CREATE POLICY statement for a given table
and policy. 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_policy_ddl('rls_tbl_1', 'rls_p8', false);  -- *non-pretty
formatted DDL*
                                               pg_get_policy_ddl

---------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE POLICY rls_p8 ON rls_tbl_1 AS PERMISSIVE FOR ALL TO
regress_rls_alice, regress_rls_dave USING (true);

2) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);   -- *pretty
formatted DDL*
               pg_get_policy_ddl
------------------------------------------------
 CREATE POLICY rls_p8 ON rls_tbl_1
         AS PERMISSIVE
         FOR ALL
         TO regress_rls_alice, regress_rls_dave
         USING (true)
 ;

The patch includes documentation, in-code comments, and regression tests,
all of which pass successfully.

-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)

  2. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Álvaro Herrera <alvherre@kurilemu.de> — 2025-10-15T17:25:37Z

    Hello,

I have reviewed this patch before and provided a number of comments that
have been addressed by Akshay (so I encourage you to list my name and
this address in a Reviewed-by trailer line in the commit message).  One
thing I had not noticed is that while this function has a "pretty" flag,
it doesn't use it to pass anything to pg_get_expr_worker()'s prettyFlags
argument, and I think it should -- probably just 

  prettyFlags = GET_PRETTY_FLAGS(pretty);

same as pg_get_querydef() does.

Thanks

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

  3. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Philip Alger <paalger0@gmail.com> — 2025-10-15T17:30:35Z

    Hi Akshay,

When applying the patch, I got a number of errors and the tests failed. I
think it stems from:

+ targetTable = relation_open(tableID, NoLock);
+ relation_close(targetTable, NoLock);


I changed them to use "AccessShareLock" and it worked, and it's probably
relevant to change table_close to "AccessShareLock" as well. You're just
reading from the table.

+ table_close(pgPolicyRel, RowExclusiveLock);


You might move "Datum valueDatum" to the top of the function. The
compiler screamed at me for that, so I went ahead and made that change and
the screaming stopped.

+ /* Check if the policy has a TO list */
+ Datum valueDatum = heap_getattr(tuplePolicy,


I also don't think you need the extra parenthesis around "USING (%s)" and
""WITH CHECK (%s)" in the code; it seems to print it just fine without
them. Other people might have different opinions.

2) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);   -- *pretty
> formatted DDL*
>                pg_get_policy_ddl
> ------------------------------------------------
>  CREATE POLICY rls_p8 ON rls_tbl_1
>          AS PERMISSIVE
>          FOR ALL
>          TO regress_rls_alice, regress_rls_dave
>          USING (true)
>  ;
>
>
As for the "pretty" part. In my opinion, I don't think it's necessary, and
putting the statement terminator (;) seems strange.
However, I think you're going to get a lot of opinions on what
well-formatted SQL looks like.

-- 
Best,
Phil Alger

  4. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-16T08:17:53Z

    On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de>
wrote:

> Hello,
>
> I have reviewed this patch before and provided a number of comments that
> have been addressed by Akshay (so I encourage you to list my name and
> this address in a Reviewed-by trailer line in the commit message).  One
> thing I had not noticed is that while this function has a "pretty" flag,
> it doesn't use it to pass anything to pg_get_expr_worker()'s prettyFlags
> argument, and I think it should -- probably just
>
>   prettyFlags = GET_PRETTY_FLAGS(pretty);
>
> same as pg_get_querydef() does.
>

   Fixed and added 'Reviewed-by:'

>
> Thanks
>
> --
> Álvaro Herrera         PostgreSQL Developer  —
> https://www.EnterpriseDB.com/
>

  5. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-16T08:34:21Z

    On Wed, Oct 15, 2025 at 11:00 PM Philip Alger <paalger0@gmail.com> wrote:

> Hi Akshay,
>
> When applying the patch, I got a number of errors and the tests failed. I
> think it stems from:
>
> + targetTable = relation_open(tableID, NoLock);
> + relation_close(targetTable, NoLock);
>
>
> I changed them to use "AccessShareLock" and it worked, and it's probably
> relevant to change table_close to "AccessShareLock" as well. You're just
> reading from the table.
>
> + table_close(pgPolicyRel, RowExclusiveLock);
>
>
> You might move "Datum valueDatum" to the top of the function. The
> compiler screamed at me for that, so I went ahead and made that change and
> the screaming stopped.
>
> + /* Check if the policy has a TO list */
> + Datum valueDatum = heap_getattr(tuplePolicy,
>
>
Fixed all the above review comments in the v2 patch.

>
> I also don't think you need the extra parenthesis around "USING (%s)" and
> ""WITH CHECK (%s)" in the code; it seems to print it just fine without
> them. Other people might have different opinions.
>

We need to add extra parentheses for the USING and CHECK clauses. Without
them, expressions like USING true or CHECK true will throw a syntax error
at or near "true".

>
> 2) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);   -- *pretty
>> formatted DDL*
>>                pg_get_policy_ddl
>> ------------------------------------------------
>>  CREATE POLICY rls_p8 ON rls_tbl_1
>>          AS PERMISSIVE
>>          FOR ALL
>>          TO regress_rls_alice, regress_rls_dave
>>          USING (true)
>>  ;
>>
>>
> As for the "pretty" part. In my opinion, I don't think it's necessary, and
> putting the statement terminator (;) seems strange.
>

I think the pretty format option is a nice-to-have parameter. Users can
simply set it to false if they don’t want the DDL to be formatted.
As for the statement terminator, it’s useful to include it, while running
multiple queries together could result in a syntax error. In my opinion,
there’s no harm in providing the statement terminator.
However, I’ve modified the logic to add the statement terminator at the end
instead of appending to a new line.

>
> However, I think you're going to get a lot of opinions on what
> well-formatted SQL looks like.
>
> --
> Best,
> Phil Alger
>

  6. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    jian he <jian.universality@gmail.com> — 2025-10-16T09:14:30Z

    hi. I still can not compile your v2.

../../Desktop/pg_src/src1/postgres/src/backend/utils/adt/ruleutils.c:
In function ‘get_formatted_string’:
../../Desktop/pg_src/src1/postgres/src/backend/utils/adt/ruleutils.c:13770:9:
error: function ‘get_formatted_string’ might be a candidate for
‘gnu_printf’ format attribute [-Werror=suggest-attribute=format]
13770 |         appendStringInfoVA(buf, fmt, args);
      |         ^~~~~~~~~~~~~~~~~~
cc1: all warnings being treated as errors

Maybe you can register your patch on https://commitfest.postgresql.org/
then it will run all CI tests on all kinds of OS.

row security policy qual and with_check_qual can contain sublink/subquery.
but pg_get_expr can not cope with sublink/subquery.

see pg_get_expr comments below:
 * Currently, the expression can only refer to a single relation, namely
 * the one specified by the second parameter.  This is sufficient for
 * partial indexes, column default expressions, etc.  We also support
 * Var-free expressions, for which the OID can be InvalidOid.

see commit 6867f96 and
https://www.postgresql.org/message-id/flat/20211219205422.GT17618%40telsasoft.com

I guess (because I can not compile, mentioned above):
"ERROR:  expression contains variables"
can be triggered by the following setup:

create table t(a int);
CREATE POLICY p1 ON t AS RESTRICTIVE FOR ALL
USING (a IS NOT NULL AND (SELECT 1 = 1 FROM pg_rewrite WHERE
pg_get_function_arg_default(ev_class, 1) !~~ pg_get_expr(ev_qual, 0,
false)));
SELECT pg_get_policy_ddl('t', 'p1', true);

You can also check my patch at https://commitfest.postgresql.org/patch/6054/
which similarly needs to build the POLICY definition for reconstruction.

see RelationBuildRowSecurity, checkExprHasSubLink also.

  7. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-16T11:47:22Z

    On Thu, Oct 16, 2025 at 2:45 PM jian he <jian.universality@gmail.com> wrote:

> hi. I still can not compile your v2.
>
> ../../Desktop/pg_src/src1/postgres/src/backend/utils/adt/ruleutils.c:
> In function ‘get_formatted_string’:
>
> ../../Desktop/pg_src/src1/postgres/src/backend/utils/adt/ruleutils.c:13770:9:
> error: function ‘get_formatted_string’ might be a candidate for
> ‘gnu_printf’ format attribute [-Werror=suggest-attribute=format]
> 13770 |         appendStringInfoVA(buf, fmt, args);
>       |         ^~~~~~~~~~~~~~~~~~
> cc1: all warnings being treated as errors
>

I’m relatively new to PostgreSQL development. I’m working on setting up the
CI pipeline and will try to fix all warnings.

>
> Maybe you can register your patch on https://commitfest.postgresql.org/
> then it will run all CI tests on all kinds of OS.
>
> row security policy qual and with_check_qual can contain sublink/subquery.
> but pg_get_expr can not cope with sublink/subquery.
>
> see pg_get_expr comments below:
>  * Currently, the expression can only refer to a single relation, namely
>  * the one specified by the second parameter.  This is sufficient for
>  * partial indexes, column default expressions, etc.  We also support
>  * Var-free expressions, for which the OID can be InvalidOid.
>
> see commit 6867f96 and
>
> https://www.postgresql.org/message-id/flat/20211219205422.GT17618%40telsasoft.com
>
> I guess (because I can not compile, mentioned above):
> "ERROR:  expression contains variables"
> can be triggered by the following setup:
>
> create table t(a int);
> CREATE POLICY p1 ON t AS RESTRICTIVE FOR ALL
> USING (a IS NOT NULL AND (SELECT 1 = 1 FROM pg_rewrite WHERE
> pg_get_function_arg_default(ev_class, 1) !~~ pg_get_expr(ev_qual, 0,
> false)));
> SELECT pg_get_policy_ddl('t', 'p1', true);
>

The above example works fine with my patch
[image: Screenshot 2025-10-16 at 5.08.10 PM.png]


>
> You can also check my patch at
> https://commitfest.postgresql.org/patch/6054/
> which similarly needs to build the POLICY definition for reconstruction.
>
> see RelationBuildRowSecurity, checkExprHasSubLink also.
>

  8. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Philip Alger <paalger0@gmail.com> — 2025-10-16T12:36:05Z

    Hi Akshay,


>>> As for the statement terminator, it’s useful to include it, while
>> running multiple queries together could result in a syntax error. In my
>> opinion, there’s no harm in providing the statement terminator.
>>
> However, I’ve modified the logic to add the statement terminator at the
> end instead of appending to a new line.
>
>>
>>
Regarding putting the terminator at the end, I think my original comment
got cut off by my poor editing. Yes, that's what I was referring to; no
need to append it to a new line.

-- 
Best, Phil Alger

  9. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-16T12:50:50Z

    Please find attached the v3 patch, which resolves all compilation errors
and warnings.

On Thu, Oct 16, 2025 at 6:06 PM Philip Alger <paalger0@gmail.com> wrote:

> Hi Akshay,
>
>
>>>> As for the statement terminator, it’s useful to include it, while
>>> running multiple queries together could result in a syntax error. In my
>>> opinion, there’s no harm in providing the statement terminator.
>>>
>> However, I’ve modified the logic to add the statement terminator at the
>> end instead of appending to a new line.
>>
>>>
>>>
> Regarding putting the terminator at the end, I think my original comment
> got cut off by my poor editing. Yes, that's what I was referring to; no
> need to append it to a new line.
>
> --
> Best, Phil Alger
>

  10. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Chao Li <li.evan.chao@gmail.com> — 2025-10-21T09:08:32Z

    
> On Oct 16, 2025, at 20:50, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
> 
> Please find attached the v3 patch, which resolves all compilation errors and warnings.
> 
> On Thu, Oct 16, 2025 at 6:06 PM Philip Alger <paalger0@gmail.com> wrote:
> Hi Akshay,
> 
> 
> As for the statement terminator, it’s useful to include it, while running multiple queries together could result in a syntax error. In my opinion, there’s no harm in providing the statement terminator.
> However, I’ve modified the logic to add the statement terminator at the end instead of appending to a new line. 
> 
> 
> Regarding putting the terminator at the end, I think my original comment got cut off by my poor editing. Yes, that's what I was referring to; no need to append it to a new line. 
> 
> -- 
> Best, Phil Alger
> <v3-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch>

1 - ruleutils.c
```
+	if (pretty)
+	{
+		/* Indent with tabs */
+		for (int i = 0; i < noOfTabChars; i++)
+		{
+			appendStringInfoString(buf, "\t");
+		}
```

As you are adding a single char of ‘\t’, better to use appendStringInfoChar() that is cheaper.

2 - ruleutils.c
```
+	initStringInfo(&buf);
+
+	targetTable = relation_open(tableID, AccessShareLock);
```

Looks like only usage of opening the table is to get the table name. In that case, why don’t just call get_rel_name(tableID) and store the table name in a local variable?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

  11. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    jian he <jian.universality@gmail.com> — 2025-10-22T07:20:21Z

    On Thu, Oct 16, 2025 at 8:51 PM Akshay Joshi
<akshay.joshi@enterprisedb.com> wrote:
>
> Please find attached the v3 patch, which resolves all compilation errors and warnings.
>

drop table if exists t, ts, ts1;
create table t(a int);
CREATE POLICY p0 ON t FOR ALL TO PUBLIC USING (a % 2 = 1);
SELECT pg_get_policy_ddl('t', 'p0', false);

                          pg_get_policy_ddl
---------------------------------------------------------------------
  CREATE POLICY p0 ON t AS PERMISSIVE FOR ALL USING (((a % 2) = 1));
(1 row)

"TO PUBLIC" part is missing, maybe it's ok.


SELECT pg_get_policy_ddl(-1, 'p0', false);
ERROR:  could not open relation with OID 4294967295
as I mentioned in a nearby thread [1], this should be NULL instead of ERROR.
[1] https://postgr.es/m/CACJufxGbE4uJWu1YuqdmOx+7PMBpHvX_fbRMmHu=r4SrsuW9tg@mail.gmail.com


IMHO, get_formatted_string is not needed, most of the time, if pretty is true,
we append "\t" and "\n", for that we can simply do
```
  appendStringInfo(&buf, "CREATE POLICY %s ON %s ",
       quote_identifier(NameStr(*policyName)),
       generate_qualified_relation_name(policy_form->polrelid));
if (pretty)
    appendStringInfoString(buf, "\t\n");
```


in pg_get_triggerdef_worker, I found the below code pattern:
    /*
     * In non-pretty mode, always schema-qualify the target table name for
     * safety.  In pretty mode, schema-qualify only if not visible.
     */
    appendStringInfo(&buf, " ON %s ",
                     pretty ?
                     generate_relation_name(trigrec->tgrelid, NIL) :
                     generate_qualified_relation_name(trigrec->tgrelid));

maybe we can apply it too while construct query string:
"CREATE POLICY %s ON %s",

  12. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-22T13:19:10Z

    On Tue, Oct 21, 2025 at 2:39 PM Chao Li <li.evan.chao@gmail.com> wrote:

>
>
> > On Oct 16, 2025, at 20:50, Akshay Joshi <akshay.joshi@enterprisedb.com>
> wrote:
> >
> > Please find attached the v3 patch, which resolves all compilation errors
> and warnings.
> >
> > On Thu, Oct 16, 2025 at 6:06 PM Philip Alger <paalger0@gmail.com> wrote:
> > Hi Akshay,
> >
> >
> > As for the statement terminator, it’s useful to include it, while
> running multiple queries together could result in a syntax error. In my
> opinion, there’s no harm in providing the statement terminator.
> > However, I’ve modified the logic to add the statement terminator at the
> end instead of appending to a new line.
> >
> >
> > Regarding putting the terminator at the end, I think my original comment
> got cut off by my poor editing. Yes, that's what I was referring to; no
> need to append it to a new line.
> >
> > --
> > Best, Phil Alger
> > <v3-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch>
>
> 1 - ruleutils.c
> ```
> +       if (pretty)
> +       {
> +               /* Indent with tabs */
> +               for (int i = 0; i < noOfTabChars; i++)
> +               {
> +                       appendStringInfoString(buf, "\t");
> +               }
> ```
>
> As you are adding a single char of ‘\t’, better to use
> appendStringInfoChar() that is cheaper.
>
> 2 - ruleutils.c
> ```
> +       initStringInfo(&buf);
> +
> +       targetTable = relation_open(tableID, AccessShareLock);
> ```
>
> Looks like only usage of opening the table is to get the table name. In
> that case, why don’t just call get_rel_name(tableID) and store the table
> name in a local variable?
>

    Fixed the above review comments in the v4 patch. I have used
generate_qualified_relation_name(tableID) instead of get_rel_name(tableID).

>
> Best regards,
> --
> Chao Li (Evan)
> HighGo Software Co., Ltd.
> https://www.highgo.com/
>
>
>
>
>

  13. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-22T13:21:50Z

    On Wed, Oct 22, 2025 at 12:51 PM jian he <jian.universality@gmail.com>
wrote:

> On Thu, Oct 16, 2025 at 8:51 PM Akshay Joshi
> <akshay.joshi@enterprisedb.com> wrote:
> >
> > Please find attached the v3 patch, which resolves all compilation errors
> and warnings.
> >
>
> drop table if exists t, ts, ts1;
> create table t(a int);
> CREATE POLICY p0 ON t FOR ALL TO PUBLIC USING (a % 2 = 1);
> SELECT pg_get_policy_ddl('t', 'p0', false);
>
>                           pg_get_policy_ddl
> ---------------------------------------------------------------------
>   CREATE POLICY p0 ON t AS PERMISSIVE FOR ALL USING (((a % 2) = 1));
> (1 row)
>
> "TO PUBLIC" part is missing, maybe it's ok.
>

I used the logic below, which did not return PUBLIC as a role. I have added
logic to default the TO clause to PUBLIC when no specific role name is
provided
valueDatum = heap_getattr(tuplePolicy,
Anum_pg_policy_polroles,
RelationGetDescr(pgPolicyRel),
&attrIsNull);
if (!attrIsNull)
{
ArrayType *policy_roles = DatumGetArrayTypePCopy(valueDatum);
int nitems = ARR_DIMS(policy_roles)[0];
Oid *roles = (Oid *) ARR_DATA_PTR(policy_roles);


>
>
> SELECT pg_get_policy_ddl(-1, 'p0', false);
> ERROR:  could not open relation with OID 4294967295
> as I mentioned in a nearby thread [1], this should be NULL instead of
> ERROR.
> [1]
> https://postgr.es/m/CACJufxGbE4uJWu1YuqdmOx+7PMBpHvX_fbRMmHu=r4SrsuW9tg@mail.gmail.com
>
> Fixed in v4 patch.

>
> IMHO, get_formatted_string is not needed, most of the time, if pretty is
> true,
> we append "\t" and "\n", for that we can simply do
> ```
>   appendStringInfo(&buf, "CREATE POLICY %s ON %s ",
>        quote_identifier(NameStr(*policyName)),
>        generate_qualified_relation_name(policy_form->polrelid));
> if (pretty)
>     appendStringInfoString(buf, "\t\n");
> ```
>
>
The get_formatted_string function is needed. Instead of using multiple if
statements for the pretty flag, it’s better to have a generic function.
This will be useful if the pretty-format DDL implementation is accepted by
the community, as it can be reused by other pg_get_<object>_ddl() DDL
functions added in the future.

>
> in pg_get_triggerdef_worker, I found the below code pattern:
>     /*
>      * In non-pretty mode, always schema-qualify the target table name for
>      * safety.  In pretty mode, schema-qualify only if not visible.
>      */
>     appendStringInfo(&buf, " ON %s ",
>                      pretty ?
>                      generate_relation_name(trigrec->tgrelid, NIL) :
>                      generate_qualified_relation_name(trigrec->tgrelid));
>
> maybe we can apply it too while construct query string:
> "CREATE POLICY %s ON %s",
>

In my opinion, the table name should always be schema-qualified, which I
have addressed in the v4 patch. The implementation of the pretty flag here
differs from pg_get_triggerdef_worker, which is used only for the target
table name. In my patch, the pretty flag adds \t and \n to each different
clause (example AS, FOR, USING ...)

  14. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Philip Alger <paalger0@gmail.com> — 2025-10-22T18:49:17Z

    >> The get_formatted_string function is needed. Instead of using multiple if
> statements for the pretty flag, it’s better to have a generic function.
> This will be useful if the pretty-format DDL implementation is accepted by
> the community, as it can be reused by other pg_get_<object>_ddl() DDL
> functions added in the future.
>
>>
>> in pg_get_triggerdef_worker, I found the below code pattern:
>>     /*
>>      * In non-pretty mode, always schema-qualify the target table name for
>>      * safety.  In pretty mode, schema-qualify only if not visible.
>>      */
>>     appendStringInfo(&buf, " ON %s ",
>>                      pretty ?
>>                      generate_relation_name(trigrec->tgrelid, NIL) :
>>                      generate_qualified_relation_name(trigrec->tgrelid));
>>
>> maybe we can apply it too while construct query string:
>> "CREATE POLICY %s ON %s",
>>
>
> In my opinion, the table name should always be schema-qualified, which I
> have addressed in the v4 patch. The implementation of the pretty flag
> here differs from pg_get_triggerdef_worker, which is used only for the
> target table name. In my patch, the pretty flag adds \t and \n to each
> different clause (example AS, FOR, USING ...)
>
>

I think that's where the confusion lies with adding `pretty` to the DDL
functions. The `pretty` flag set to `true` on other functions is used to
"not" show the schema name. But in the case of this function, it is used to
format the statement. I wonder if the word `format` or `pretty_format` is a
better name? `pretty` itself in the codebase isn't a very clear name
regardless.

-- 
Best,
Phil Alger

  15. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-25T05:57:54Z

    On Thu, Oct 23, 2025 at 12:19 AM Philip Alger <paalger0@gmail.com> wrote:

>
>
>
>>> The get_formatted_string function is needed. Instead of using multiple
>> if statements for the pretty flag, it’s better to have a generic
>> function. This will be useful if the pretty-format DDL implementation is
>> accepted by the community, as it can be reused by other
>> pg_get_<object>_ddl() DDL functions added in the future.
>>
>>>
>>> in pg_get_triggerdef_worker, I found the below code pattern:
>>>     /*
>>>      * In non-pretty mode, always schema-qualify the target table name
>>> for
>>>      * safety.  In pretty mode, schema-qualify only if not visible.
>>>      */
>>>     appendStringInfo(&buf, " ON %s ",
>>>                      pretty ?
>>>                      generate_relation_name(trigrec->tgrelid, NIL) :
>>>                      generate_qualified_relation_name(trigrec->tgrelid));
>>>
>>> maybe we can apply it too while construct query string:
>>> "CREATE POLICY %s ON %s",
>>>
>>
>> In my opinion, the table name should always be schema-qualified, which I
>> have addressed in the v4 patch. The implementation of the pretty flag
>> here differs from pg_get_triggerdef_worker, which is used only for the
>> target table name. In my patch, the pretty flag adds \t and \n to each
>> different clause (example AS, FOR, USING ...)
>>
>>
>
> I think that's where the confusion lies with adding `pretty` to the DDL
> functions. The `pretty` flag set to `true` on other functions is used to
> "not" show the schema name. But in the case of this function, it is used to
> format the statement. I wonder if the word `format` or `pretty_format` is a
> better name? `pretty` itself in the codebase isn't a very clear name
> regardless.
>
In my opinion, we should first decide whether we want the DDL statement to
be in a 'pretty' format or not. Personally, I believe it should be, since
parsing a one-line DDL statement can be quite complex and difficult for
users of this function. From a user’s perspective, having the entire DDL in
a single line makes it harder to read and understand. The flag allows users
to disable the pretty format by passing false.
If the community agrees on this approach, we can then think about choosing
a more appropriate word for it.

>
> --
> Best,
> Phil Alger
>

  16. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Mark Wong <markwkm@gmail.com> — 2025-10-27T16:45:10Z

    Hi everyone,

On Thu, Oct 16, 2025 at 01:47:53PM +0530, Akshay Joshi wrote:
> 
> 
> On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
> 
>     Hello,
> 
>     I have reviewed this patch before and provided a number of comments that
>     have been addressed by Akshay (so I encourage you to list my name and
>     this address in a Reviewed-by trailer line in the commit message).  One
>     thing I had not noticed is that while this function has a "pretty" flag,
>     it doesn't use it to pass anything to pg_get_expr_worker()'s prettyFlags
>     argument, and I think it should -- probably just
> 
>       prettyFlags = GET_PRETTY_FLAGS(pretty);
> 
>     same as pg_get_querydef() does.

Kinda sorta similar thought, I've noticed some existing functions like
pg_get_constraintdef make the "pretty" flag optional, so I'm wondering
if that scheme is also preferred here.

I've attached a small diff to the original
0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch to
illustrate the additional work to follow suit, if so desired.

Regards,
Mark
--
Mark Wong <markwkm@gmail.com>
EDB https://enterprisedb.com

  17. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-10-28T09:38:03Z

    Thanks, Mark, for your review comments and the updated patch.

I’ve incorporated your changes and prepared a combined v5 patch. The v5
patch is attached for further review.

On Mon, Oct 27, 2025 at 10:15 PM Mark Wong <markwkm@gmail.com> wrote:

> Hi everyone,
>
> On Thu, Oct 16, 2025 at 01:47:53PM +0530, Akshay Joshi wrote:
> >
> >
> > On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de>
> wrote:
> >
> >     Hello,
> >
> >     I have reviewed this patch before and provided a number of comments
> that
> >     have been addressed by Akshay (so I encourage you to list my name and
> >     this address in a Reviewed-by trailer line in the commit message).
> One
> >     thing I had not noticed is that while this function has a "pretty"
> flag,
> >     it doesn't use it to pass anything to pg_get_expr_worker()'s
> prettyFlags
> >     argument, and I think it should -- probably just
> >
> >       prettyFlags = GET_PRETTY_FLAGS(pretty);
> >
> >     same as pg_get_querydef() does.
>
> Kinda sorta similar thought, I've noticed some existing functions like
> pg_get_constraintdef make the "pretty" flag optional, so I'm wondering
> if that scheme is also preferred here.
>
> I've attached a small diff to the original
> 0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch to
> illustrate the additional work to follow suit, if so desired.
>
> Regards,
> Mark
> --
> Mark Wong <markwkm@gmail.com>
> EDB https://enterprisedb.com
>

  18. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-03T11:47:40Z

    Hi Hackers,

Added a new #define GET_DDL_PRETTY_FLAGS because the existing #define
GET_PRETTY_FLAGS is not suitable for formatting reconstructed DDLs. The
existing #define GET_PRETTY_FLAGS always indents the code, regardless of
whether the flag is set to true or false, which is not the desired behavior
for pg_get_<object>_ddl functions.

Updated the logic of the get_formatted_string function based on Tim
Waizenegger’s suggestion.

I am attaching the new v6 patch, which is ready for review.

On Tue, Oct 28, 2025 at 3:08 PM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:

> Thanks, Mark, for your review comments and the updated patch.
>
> I’ve incorporated your changes and prepared a combined v5 patch. The v5
> patch is attached for further review.
>
> On Mon, Oct 27, 2025 at 10:15 PM Mark Wong <markwkm@gmail.com> wrote:
>
>> Hi everyone,
>>
>> On Thu, Oct 16, 2025 at 01:47:53PM +0530, Akshay Joshi wrote:
>> >
>> >
>> > On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de>
>> wrote:
>> >
>> >     Hello,
>> >
>> >     I have reviewed this patch before and provided a number of comments
>> that
>> >     have been addressed by Akshay (so I encourage you to list my name
>> and
>> >     this address in a Reviewed-by trailer line in the commit message).
>> One
>> >     thing I had not noticed is that while this function has a "pretty"
>> flag,
>> >     it doesn't use it to pass anything to pg_get_expr_worker()'s
>> prettyFlags
>> >     argument, and I think it should -- probably just
>> >
>> >       prettyFlags = GET_PRETTY_FLAGS(pretty);
>> >
>> >     same as pg_get_querydef() does.
>>
>> Kinda sorta similar thought, I've noticed some existing functions like
>> pg_get_constraintdef make the "pretty" flag optional, so I'm wondering
>> if that scheme is also preferred here.
>>
>> I've attached a small diff to the original
>> 0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch to
>> illustrate the additional work to follow suit, if so desired.
>>
>> Regards,
>> Mark
>> --
>> Mark Wong <markwkm@gmail.com>
>> EDB https://enterprisedb.com
>>
>

  19. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-07T12:20:52Z

    Hi Hackers,

Make the pretty flag a default parameter by adding CREATE OR REPLACE
FUNCTION in system_functions.sql.
Attached is the v7 patch, which is ready for review.

On Mon, Nov 3, 2025 at 5:17 PM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:

> Hi Hackers,
>
> Added a new #define GET_DDL_PRETTY_FLAGS because the existing #define
> GET_PRETTY_FLAGS is not suitable for formatting reconstructed DDLs. The
> existing #define GET_PRETTY_FLAGS always indents the code, regardless of
> whether the flag is set to true or false, which is not the desired
> behavior for pg_get_<object>_ddl functions.
>
> Updated the logic of the get_formatted_string function based on Tim
> Waizenegger’s suggestion.
>
> I am attaching the new v6 patch, which is ready for review.
>
> On Tue, Oct 28, 2025 at 3:08 PM Akshay Joshi <
> akshay.joshi@enterprisedb.com> wrote:
>
>> Thanks, Mark, for your review comments and the updated patch.
>>
>> I’ve incorporated your changes and prepared a combined v5 patch. The v5
>> patch is attached for further review.
>>
>> On Mon, Oct 27, 2025 at 10:15 PM Mark Wong <markwkm@gmail.com> wrote:
>>
>>> Hi everyone,
>>>
>>> On Thu, Oct 16, 2025 at 01:47:53PM +0530, Akshay Joshi wrote:
>>> >
>>> >
>>> > On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de>
>>> wrote:
>>> >
>>> >     Hello,
>>> >
>>> >     I have reviewed this patch before and provided a number of
>>> comments that
>>> >     have been addressed by Akshay (so I encourage you to list my name
>>> and
>>> >     this address in a Reviewed-by trailer line in the commit
>>> message).  One
>>> >     thing I had not noticed is that while this function has a "pretty"
>>> flag,
>>> >     it doesn't use it to pass anything to pg_get_expr_worker()'s
>>> prettyFlags
>>> >     argument, and I think it should -- probably just
>>> >
>>> >       prettyFlags = GET_PRETTY_FLAGS(pretty);
>>> >
>>> >     same as pg_get_querydef() does.
>>>
>>> Kinda sorta similar thought, I've noticed some existing functions like
>>> pg_get_constraintdef make the "pretty" flag optional, so I'm wondering
>>> if that scheme is also preferred here.
>>>
>>> I've attached a small diff to the original
>>> 0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch to
>>> illustrate the additional work to follow suit, if so desired.
>>>
>>> Regards,
>>> Mark
>>> --
>>> Mark Wong <markwkm@gmail.com>
>>> EDB https://enterprisedb.com
>>>
>>

  20. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Marcos Pegoraro <marcos@f10.com.br> — 2025-11-07T13:14:20Z

    Em sex., 7 de nov. de 2025 às 09:21, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

> Attached is the v7 patch, which is ready for review.
>
>> <https://enterprisedb.com>
>>>
>>> For this functionality to be complete, the
ALTER TABLE ... ENABLE ROW LEVEL SECURITY
statement needs to be added to the command.

Did you think about that ?
Maybe one more parameter ?

regards
Marcos

  21. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-07T14:27:06Z

    On Fri, Nov 7, 2025 at 6:45 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

> Em sex., 7 de nov. de 2025 às 09:21, Akshay Joshi <
> akshay.joshi@enterprisedb.com> escreveu:
>
>> Attached is the v7 patch, which is ready for review.
>>
>>> <https://enterprisedb.com>
>>>>
>>>> For this functionality to be complete, the
> ALTER TABLE ... ENABLE ROW LEVEL SECURITY
> statement needs to be added to the command.
>
> Did you think about that ?
> Maybe one more parameter ?
>

I don’t think we need that statement. Could you please elaborate on where
exactly it needs to be added?
The purpose of this reconstruction DDL is to generate the DDL for an
already created policy. The command you mentioned is used to enable
row-level security on the table, which is a separate step.

>
> regards
> Marcos
>
>
>

  22. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Marcos Pegoraro <marcos@f10.com.br> — 2025-11-07T14:47:36Z

    Em sex., 7 de nov. de 2025 às 11:27, Akshay Joshi <
akshay.joshi@enterprisedb.com> escreveu:

> I don’t think we need that statement. Could you please elaborate on where
> exactly it needs to be added?
>

well, these pg_get_..._ddl() functions will be cool for compare/clone
schemas in a multi tenant world.
Instead of dump/sed/restore a schema to create a new one, I could use
something like
select pg_get_table_ddl(oid) from pg_class where nspname = 'customer_050'
and relkind = 'r' union all
select pg_get_constraint_ddl(oid) from pg_constraint inner join pg_class on
... where ... union all
select pg_get_trigger_ddl(oid) from pg_trigger inner join pg_class on
... where ... union all
...

And pg_get_policy_ddl() will be part of these union all selects

Because that would be good to worry about create that only if it does not
exists or drop first too.

regards
Marcos

  23. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-10T05:16:40Z

    Thanks for the clarification. However, I still believe this is out of scope
for the CREATE POLICY DDL. The command ALTER TABLE ... ENABLE ROW LEVEL
SECURITY seems more appropriate as part of the CREATE TABLE reconstruction
rather than CREATE POLICY.

That said, I’m open to adding it if the majority feels it should be
included in this feature.

On Fri, Nov 7, 2025 at 8:18 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

> Em sex., 7 de nov. de 2025 às 11:27, Akshay Joshi <
> akshay.joshi@enterprisedb.com> escreveu:
>
>> I don’t think we need that statement. Could you please elaborate on where
>> exactly it needs to be added?
>>
>
> well, these pg_get_..._ddl() functions will be cool for compare/clone
> schemas in a multi tenant world.
> Instead of dump/sed/restore a schema to create a new one, I could use
> something like
> select pg_get_table_ddl(oid) from pg_class where nspname = 'customer_050'
> and relkind = 'r' union all
> select pg_get_constraint_ddl(oid) from pg_constraint inner join pg_class
> on ... where ... union all
> select pg_get_trigger_ddl(oid) from pg_trigger inner join pg_class on
> ... where ... union all
> ...
>
> And pg_get_policy_ddl() will be part of these union all selects
>
> Because that would be good to worry about create that only if it does not
> exists or drop first too.
>
> regards
> Marcos
>

  24. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2025-11-20T09:27:05Z

    Attached is the v8 patch for your review, with updated variable names and a
rebase applied.

On Mon, Nov 10, 2025 at 10:46 AM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:

> Thanks for the clarification. However, I still believe this is out of
> scope for the CREATE POLICY DDL. The command ALTER TABLE ... ENABLE ROW
> LEVEL SECURITY seems more appropriate as part of the CREATE TABLE
> reconstruction rather than CREATE POLICY.
>
> That said, I’m open to adding it if the majority feels it should be
> included in this feature.
>
> On Fri, Nov 7, 2025 at 8:18 PM Marcos Pegoraro <marcos@f10.com.br> wrote:
>
>> Em sex., 7 de nov. de 2025 às 11:27, Akshay Joshi <
>> akshay.joshi@enterprisedb.com> escreveu:
>>
>>> I don’t think we need that statement. Could you please elaborate on
>>> where exactly it needs to be added?
>>>
>>
>> well, these pg_get_..._ddl() functions will be cool for compare/clone
>> schemas in a multi tenant world.
>> Instead of dump/sed/restore a schema to create a new one, I could use
>> something like
>> select pg_get_table_ddl(oid) from pg_class where nspname = 'customer_050'
>> and relkind = 'r' union all
>> select pg_get_constraint_ddl(oid) from pg_constraint inner join pg_class
>> on ... where ... union all
>> select pg_get_trigger_ddl(oid) from pg_trigger inner join pg_class on
>> ... where ... union all
>> ...
>>
>> And pg_get_policy_ddl() will be part of these union all selects
>>
>> Because that would be good to worry about create that only if it does not
>> exists or drop first too.
>>
>> regards
>> Marcos
>>
>

  25. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2026-05-22T13:32:46Z

    Hi hackers,


Following the recently committed *pg_get_database_ddl()*, which adopted a
VARIADIC 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

Reconstructs the CREATE POLICY statement for the named policy on the given
table, returning the result as a single row.

The currently supported option is pretty (boolean) for formatted output.

    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().

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/DELETE), 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 PM jian he <jian.universality@gmail.com> wrote:

> On Thu, Nov 20, 2025 at 5:27 PM 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="func_table_entry"><para role="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 from
> the
> +        system catalogs for a specified table and policy name. The result
> 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)``
>
> --
> jian
> https://www.enterprisedb.com/
>

  26. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Japin Li <japinli@hotmail.com> — 2026-05-22T16:24:54Z

    Hi, Akshay

On Fri, 22 May 2026 at 19:02, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
> Hi hackers,                                                                                                              
>                      
>
> Following the recently committed pg_get_database_ddl(), which adopted a VARIADIC 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 
>
> Reconstructs the CREATE POLICY statement for the named policy on the given table, returning the result as a single row.
>
> The currently supported option is pretty (boolean) for formatted output.                                                 
>       
>     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(). 
>
> 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/DELETE), 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 PM jian he <jian.universality@gmail.com> wrote:
>
>  On Thu, Nov 20, 2025 at 5:27 PM 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="func_table_entry"><para role="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 from the
>  +        system catalogs for a specified table and policy name. The result 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.

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

  27. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2026-05-25T07:17:41Z

    Thanks Japin,

Attached is the updated patch.

On Fri, May 22, 2026 at 9:55 PM Japin Li <japinli@hotmail.com> wrote:

>
> Hi, Akshay
>
> On Fri, 22 May 2026 at 19:02, Akshay Joshi <akshay.joshi@enterprisedb.com>
> wrote:
> > Hi hackers,
>
> >
> >
> > Following the recently committed pg_get_database_ddl(), which adopted a
> VARIADIC 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
> >
> > Reconstructs the CREATE POLICY statement for the named policy on the
> given table, returning the result as a single row.
> >
> > The currently supported option is pretty (boolean) for formatted
> output.
> >
> >     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().
> >
> > 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/DELETE), 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 PM jian he <jian.universality@gmail.com>
> wrote:
> >
> >  On Thu, Nov 20, 2025 at 5:27 PM 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="func_table_entry"><para role="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
> from the
> >  +        system catalogs for a specified table and policy name. The
> result 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.
>
> --
> Regards,
> Japin Li
> ChengDu WenWu Information Technology Co., Ltd.
>

  28. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Ilmar Y <tanswis42@gmail.com> — 2026-05-28T13:41:58Z

    The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       tested, failed
Spec compliant:           not tested
Documentation:            not tested

Hi,

I looked at v10, focused on whether the generated CREATE POLICY statement
can be executed again.

The patch applies cleanly on current master at
8a86aa313a714adc56c74e4b08793e4e6102b5ca.

git diff --check reports no issues.

I built with:

./configure --prefix="$PWD/pg-install" --without-readline --without-zlib --without-icu
make -s -j8
make -s install

make -C src/test/regress check TESTS=rowsecurity

ended up running the full parallel_schedule in this makefile; all 245 tests
passed, including rowsecurity.

I found one correctness issue in the generated non-pretty DDL.  The code
assumes that pg_get_expr_ext(..., false) already returns the parentheses
required by CREATE POLICY syntax, but that is not true for simple boolean
constants.

For example:

CREATE TABLE t(a int);
CREATE POLICY p_true ON t USING (true);
SELECT ddl FROM pg_get_policy_ddl('t', 'p_true', 'pretty', 'false') AS ddl;

returns:

CREATE POLICY p_true ON public.t USING true;

If I drop the policy and execute that generated statement, it fails:

ERROR:  syntax error at or near "true"
LINE 1: CREATE POLICY p_true ON public.t USING true;
                                               ^

The same issue reproduces for WITH CHECK:

CREATE POLICY p_check ON t FOR INSERT WITH CHECK (false);

is reconstructed as:

CREATE POLICY p_check ON public.t FOR INSERT WITH CHECK false;

and executing it fails at "false".

So I think USING and WITH CHECK need to be parenthesized in non-pretty mode
too, or the tests should include a round-trip execution check for generated
DDL with simple boolean expressions.

I used two small SQL reproducers for the manual checks; the complete repro is
included above.

I have not reviewed the broader pg_get_*_ddl API design or every possible
policy expression form.

Regards,
Ilmar Yunusov

The new status of this patch is: Waiting on Author

  29. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Akshay Joshi <akshay.joshi@enterprisedb.com> — 2026-05-29T06:50:54Z

    Thanks for the reviews.

My original patch (v9) was actually correct. After considering Japin's
review comment, I initially thought the extra parentheses weren't
necessary, but they are indeed required for handling boolean values
properly in non-pretty mode too, so I kept them in USING (%s) / WITH CHECK
(%s) for both modes.

`pg_get_expr()` only adds outer parentheses for composite expressions (via
the deparsers for `OpExpr`, `BoolExpr`, etc.). For atomic top-level nodes
like `Const`, `Var`, `current_user`, `NULL`, etc.
For example:

    CREATE POLICY p ON t USING (true);
    SELECT pg_get_policy_ddl('t', 'p');  -- previously: ... USING true;
 (syntax error)

This is exactly why `pg_dump` always wraps the expression unconditionally;
see `src/bin/pg_dump/pg_dump.c`:4473-4477:

    if (polinfo->polqual != NULL)
        appendPQExpBuffer(query, " USING (%s)", polinfo->polqual);
    if (polinfo->polwithcheck != NULL)
        appendPQExpBuffer(query, " WITH CHECK (%s)", polinfo->polwithcheck);

I've also added a round-trip regression test with `USING (true)` / `WITH
CHECK (false)` that captures the generated DDL, drops the policies,
re-executes the DDL, and verifies the policies are recreated.

*v11 Patch attached for review.*

On Thu, May 28, 2026 at 7:12 PM Ilmar Y <tanswis42@gmail.com> wrote:

> The following review has been posted through the commitfest application:
> make installcheck-world:  not tested
> Implements feature:       tested, failed
> Spec compliant:           not tested
> Documentation:            not tested
>
> Hi,
>
> I looked at v10, focused on whether the generated CREATE POLICY statement
> can be executed again.
>
> The patch applies cleanly on current master at
> 8a86aa313a714adc56c74e4b08793e4e6102b5ca.
>
> git diff --check reports no issues.
>
> I built with:
>
> ./configure --prefix="$PWD/pg-install" --without-readline --without-zlib
> --without-icu
> make -s -j8
> make -s install
>
> make -C src/test/regress check TESTS=rowsecurity
>
> ended up running the full parallel_schedule in this makefile; all 245 tests
> passed, including rowsecurity.
>
> I found one correctness issue in the generated non-pretty DDL.  The code
> assumes that pg_get_expr_ext(..., false) already returns the parentheses
> required by CREATE POLICY syntax, but that is not true for simple boolean
> constants.
>
> For example:
>
> CREATE TABLE t(a int);
> CREATE POLICY p_true ON t USING (true);
> SELECT ddl FROM pg_get_policy_ddl('t', 'p_true', 'pretty', 'false') AS ddl;
>
> returns:
>
> CREATE POLICY p_true ON public.t USING true;
>
> If I drop the policy and execute that generated statement, it fails:
>
> ERROR:  syntax error at or near "true"
> LINE 1: CREATE POLICY p_true ON public.t USING true;
>                                                ^
>
> The same issue reproduces for WITH CHECK:
>
> CREATE POLICY p_check ON t FOR INSERT WITH CHECK (false);
>
> is reconstructed as:
>
> CREATE POLICY p_check ON public.t FOR INSERT WITH CHECK false;
>
> and executing it fails at "false".
>
> So I think USING and WITH CHECK need to be parenthesized in non-pretty mode
> too, or the tests should include a round-trip execution check for generated
> DDL with simple boolean expressions.
>
> I used two small SQL reproducers for the manual checks; the complete repro
> is
> included above.
>
> I have not reviewed the broader pg_get_*_ddl API design or every possible
> policy expression form.
>
> Regards,
> Ilmar Yunusov
>
> The new status of this patch is: Waiting on Author
>

  30. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Japin Li <japinli@hotmail.com> — 2026-05-29T07:38:32Z

    On Fri, 29 May 2026 at 12:20, Akshay Joshi <akshay.joshi@enterprisedb.com> wrote:
> Thanks for the reviews. 
>
> My original patch (v9) was actually correct. After considering Japin's review comment, I initially thought the extra
> parentheses weren't necessary, but they are indeed required for handling boolean values properly in non-pretty mode too,
> so I kept them in USING (%s) / WITH CHECK (%s) for both modes.
>

My bad!  I had not considered this situation.

> `pg_get_expr()` only adds outer parentheses for composite expressions (via the deparsers for `OpExpr`, `BoolExpr`, etc.).
> For atomic top-level nodes like `Const`, `Var`, `current_user`, `NULL`, etc. 
> For example:
>
>     CREATE POLICY p ON t USING (true);
>     SELECT pg_get_policy_ddl('t', 'p');  -- previously: ... USING true;  (syntax error)
>
> This is exactly why `pg_dump` always wraps the expression unconditionally; see `src/bin/pg_dump/pg_dump.c`:4473-4477:
>
>     if (polinfo->polqual != NULL)
>         appendPQExpBuffer(query, " USING (%s)", polinfo->polqual);
>     if (polinfo->polwithcheck != NULL)
>         appendPQExpBuffer(query, " WITH CHECK (%s)", polinfo->polwithcheck);
>
> I've also added a round-trip regression test with `USING (true)` / `WITH CHECK (false)` that captures the generated DDL,
> drops the policies, re-executes the DDL, and verifies the policies are recreated. 
>
> v11 Patch attached for review.
>
> On Thu, May 28, 2026 at 7:12 PM Ilmar Y <tanswis42@gmail.com> wrote:
>
>  The following review has been posted through the commitfest application:
>  make installcheck-world:  not tested
>  Implements feature:       tested, failed
>  Spec compliant:           not tested
>  Documentation:            not tested
>
>  Hi,
>
>  I looked at v10, focused on whether the generated CREATE POLICY statement
>  can be executed again.
>
>  The patch applies cleanly on current master at
>  8a86aa313a714adc56c74e4b08793e4e6102b5ca.
>
>  git diff --check reports no issues.
>
>  I built with:
>
>  ./configure --prefix="$PWD/pg-install" --without-readline --without-zlib --without-icu
>  make -s -j8
>  make -s install
>
>  make -C src/test/regress check TESTS=rowsecurity
>
>  ended up running the full parallel_schedule in this makefile; all 245 tests
>  passed, including rowsecurity.
>
>  I found one correctness issue in the generated non-pretty DDL.  The code
>  assumes that pg_get_expr_ext(..., false) already returns the parentheses
>  required by CREATE POLICY syntax, but that is not true for simple boolean
>  constants.
>
>  For example:
>
>  CREATE TABLE t(a int);
>  CREATE POLICY p_true ON t USING (true);
>  SELECT ddl FROM pg_get_policy_ddl('t', 'p_true', 'pretty', 'false') AS ddl;
>
>  returns:
>
>  CREATE POLICY p_true ON public.t USING true;
>
>  If I drop the policy and execute that generated statement, it fails:
>
>  ERROR:  syntax error at or near "true"
>  LINE 1: CREATE POLICY p_true ON public.t USING true;
>                                                 ^
>
>  The same issue reproduces for WITH CHECK:
>
>  CREATE POLICY p_check ON t FOR INSERT WITH CHECK (false);
>
>  is reconstructed as:
>
>  CREATE POLICY p_check ON public.t FOR INSERT WITH CHECK false;
>
>  and executing it fails at "false".
>
>  So I think USING and WITH CHECK need to be parenthesized in non-pretty mode
>  too, or the tests should include a round-trip execution check for generated
>  DDL with simple boolean expressions.
>
>  I used two small SQL reproducers for the manual checks; the complete repro is
>  included above.
>
>  I have not reviewed the broader pg_get_*_ddl API design or every possible
>  policy expression form.
>
>  Regards,
>  Ilmar Yunusov
>
>  The new status of this patch is: Waiting on Author

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

  31. Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement

    Ilmar Y <tanswis42@gmail.com> — 2026-05-30T12:14:33Z

    The following review has been posted through the commitfest application:
make installcheck-world:  not tested
Implements feature:       tested, passed
Spec compliant:           not tested
Documentation:            not tested

Hi,

I re-checked v11 against current origin/master at
db5ed03217b9c238703df8b4b286115d6e940488.

The patch applies cleanly, and git diff --check reports no issues.

I built with:

./configure --prefix="$PWD/pg-install" --without-readline --without-zlib --without-icu
make -s -j8
make -s install

make -C src/test/regress check TESTS=rowsecurity

passed. The regression run completed successfully; all 245 tests passed,
including rowsecurity.

I also re-ran the two manual repro scripts from my previous review. Both now
complete successfully.

The non-pretty USING case now reconstructs:

CREATE POLICY p_true ON public.t USING (true);

and the generated statement executes successfully.

The non-pretty WITH CHECK case now reconstructs:

CREATE POLICY p_check ON public.t FOR INSERT WITH CHECK (false);

and that generated statement also executes successfully.

So the round-trip issue I reported for v10 is fixed for me in v11.

Regards,
Ilmar Yunusov