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

From: jian he <[email-masked]>
Date: Mon, 5 Jan 2026 22:30:04 +0800

On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
<[email-masked]> 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/

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 22 May 2026 19:02:46 +0530

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.

---

From: Japin Li <[email-masked]>
Date: Sat, 23 May 2026 00:24:54 +0800

Hi, Akshay

On Fri, 22 May 2026 at 19:02, Akshay Joshi <[email-masked]> 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.
>
> 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

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.

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 25 May 2026 12:47:41 +0530

Thanks Japin,

Attached is the updated patch.

On Fri, May 22, 2026 at 9:55 PM Japin Li <[email-masked]> wrote:

> > Reconstructs the CREATE POLICY statement for the named policy on the
> given table, returning the result as a single row.
> >
> > NULL inputs for table or policy_name return no rows. Unknown option
> names, invalid boolean values, and duplicate options
> > The patch includes documentation updates in func-info.sgml and
> regression tests in rowsecurity.sql covering
> > PERMISSIVE/RESTRICTIVE, each command type
>  TextDatumGetCString(expr));
>

---

From: Ilmar Y <[email-masked]>
Date: Thu, 28 May 2026 13:41:58 +0000

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

---

From: Akshay Joshi <[email-masked]>
Date: Fri, 29 May 2026 12:20:54 +0530

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.*

---

From: Japin Li <[email-masked]>
Date: Fri, 29 May 2026 15:38:32 +0800

On Fri, 29 May 2026 at 12:20, Akshay Joshi <[email-masked]> 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:
>
>     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:
>
> 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.
>

---

From: Ilmar Y <[email-masked]>
Date: Sat, 30 May 2026 12:14:33 +0000

Implements feature:       tested, passed

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:

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

---

From: solai v <[email-masked]>
Date: Mon, 8 Jun 2026 18:11:12 +0530

Hi all,

> >  The new status of this patch is: Waiting on Author

I reviewed and tested the V11 pg_get_policy_ddl() patch. I verified
the basic functionality by creating various row-level security
policies and checking the reconstructed DDL returned by
pg_get_policy_ddl(). The function correctly reconstructed policies for
different command types (SELECT, INSERT, UPDATE, DELETE), PERMISSIVE
and RESTRICTIVE policies, multiple role lists, quoted identifiers,
USING clauses, and WITH CHECK clauses.
I also tested more complex cases involving subqueries. Policies
containing EXISTS subqueries in both USING and WITH CHECK clauses were
reconstructed successfully. Nested subqueries were also handled
correctly, and I did not encounter any issues related to expression
reconstruction or variable handling. I verified NULL input handling as
well. Passing NULL values for the table name or policy name returned
no rows as expected. Invalid relation names resulted in the expected
regclass resolution error. One observation from testing is that the
generated DDL omits default clauses such as FOR ALL and TO PUBLIC. For
example, a policy created with FOR ALL TO PUBLIC is reconstructed
without those clauses, while still producing semantically equivalent
DDL. I'm not sure whether this is intentional or if the function is
expected to reproduce all clauses explicitly, but it may be worth
discussing.
Overall, the patch worked as expected in all scenarios I tested, and I
did not find any functional issues.

Regards,
Solai

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 22 Jun 2026 18:34:29 +0530

On Mon, Jun 8, 2026 at 6:10 PM solai v <[email-masked]> wrote:

> 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,
> > > `pg_get_expr()` only adds outer parentheses for composite expressions
> (via the deparsers for `OpExpr`, `BoolExpr`, etc.).
> > > This is exactly why `pg_dump` always wraps the expression
> unconditionally; see `src/bin/pg_dump/pg_dump.c`:4473-4477:
> > >
> > > I've also added a round-trip regression test with `USING (true)` /
> `WITH CHECK (false)` that captures the generated DDL,

Thanks for the review. The omission of FOR ALL and TO PUBLIC is intentional
and matches the long-standing convention used by pg_get_indexdef,
pg_get_constraintdef, pg_get_viewdef, etc.: emit a clause only when it
differs from the parser's default. The result is semantically equivalent to
the CREATE POLICY DDL.

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 22 Jun 2026 18:49:27 +0530

*Chao Li* found an issue in my other patch for *pg_get_table_ddl.*
Specifically,
the existing pattern in pg_proc.dat for variadic-text functions (e.g.,
jsonb_delete, json_extract_path) uses *_text* instead of *text* at the
variadic position in both proargtypes and proallargtypes, with provariadic
=> 'text'. This is the convention documented by the sanity check in
src/test/regress/sql/opr_sanity.sql.

I realized the same issue was present in this patch as well, so I have
fixed it and added corresponding test cases.

The v12 patch is now ready for review and commit.

On Mon, Jun 22, 2026 at 6:34 PM Akshay Joshi <[email-masked]>
wrote:

>> > > My original patch (v9) was actually correct. After considering
>> Japin's review comment, I initially thought the extra
> Thanks for the review. The omission of FOR ALL and TO PUBLIC is
> intentional and matches the long-standing convention used by
> pg_get_indexdef, pg_get_constraintdef, pg_get_viewdef, etc.: emit a clause
> only when it differs from the parser's default. The result is semantically
> equivalent to the CREATE POLICY DDL.
>
>>
>>
>> Regards,
>> Solai
>>
>

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 29 Jun 2026 19:12:06 +0530

Rebased *v13* patch is now ready for review and commit.

On Mon, Jun 22, 2026 at 6:49 PM Akshay Joshi <[email-masked]>
wrote:

> *Chao Li* found an issue in my other patch for *pg_get_table_ddl.* Specifically,
> On Mon, Jun 22, 2026 at 6:34 PM Akshay Joshi <
> [email-masked]> wrote:
>
>>
>>
>>> > > `pg_get_expr()` only adds outer parentheses for composite
>>> expressions (via the deparsers for `OpExpr`, `BoolExpr`, etc.).

---

From: Akshay Joshi <[email-masked]>
Date: Tue, 30 Jun 2026 16:48:26 +0530

All,

I've updated the patch to align with the interface change introduced by
commit *d6ed87d1989* (Andrew Dunstan, 2026-06-26 "Use named boolean
parameters for pg_get_*_ddl option arguments").

That commit replaced the VARIADIC text[] alternating key/value option
interface with typed named boolean parameters across pg_get_role_ddl(),
pg_get_tablespace_ddl(), and pg_get_database_ddl(), removing the
DdlOption/parse_ddl_options() machinery in favour of direct
PG_GETARG_BOOL() calls.

Updated patch v14 is ready for review/commit.

On Mon, Jun 29, 2026 at 7:12 PM Akshay Joshi <[email-masked]>
wrote:

> On Mon, Jun 22, 2026 at 6:49 PM Akshay Joshi <

---

From: solai v <[email-masked]>
Date: Wed, 1 Jul 2026 14:45:02 +0530

Hi all,

On Tue, Jun 30, 2026 at 4:48 PM Akshay Joshi
<[email-masked]> wrote:
>
> All,
>
> I've updated the patch to align with the interface change introduced by commit d6ed87d1989 (Andrew Dunstan, 2026-06-26 "Use named boolean parameters for pg_get_*_ddl option arguments").
>
> That commit replaced the VARIADIC text[] alternating key/value option interface with typed named boolean parameters across pg_get_role_ddl(), pg_get_tablespace_ddl(), and pg_get_database_ddl(), removing the DdlOption/parse_ddl_options() machinery in favour of direct PG_GETARG_BOOL() calls.
>

I reviewed and tested the v14 patch on the latest master. The patch
applied cleanly, built successfully, and passed make check without any
regression failures. I verified the new function signature and
confirmed that pg_get_policy_ddl() now uses the new interface with the
boolean DEFAULT false argument. Also I tested the function with a
variety of row-level security policies, including: Basic policy
reconstruction, PERMISSIVE and RESTRICTIVE policies, Different command
types (SELECT, INSERT, UPDATE, DELETE), Policies with multiple roles
and the PUBLIC role, Quoted table, policy, and role names, USING and
WITH CHECK clauses, Complex expressions including EXISTS, nested
subqueries, CASE expressions, COALESCE, ANY/ALL operators, and boolean
expressions, NULL inputs, invalid relation names, and non-existent
policies. The generated DDL was correct as per the intent, and the
expected errors were verified for the invalid inputs. I also verified
that the generated DDL is executable by dropping an existing policy,
recreating it using the output of pg_get_policy_ddl(), and confirming
that the recreated policy was reconstructed successfully again by the
function.
One observation I noted is that for policies using default attributes
(TO PUBLIC and AS PERMISSIVE), the generated DDL omits those clauses
and produces a semantically equivalent statement which seems
intentional but thought of sending it here for further clarification.
Overall, I did not encounter any functional issues during testing. The
patch looks good to me and worked as expected in all the scenarios I
tested.

Regards,
Solai

---

From: Akshay Joshi <[email-masked]>
Date: Wed, 1 Jul 2026 16:01:10 +0530

On Wed, Jul 1, 2026 at 2:44 PM solai v <[email-masked]> wrote:

> commit d6ed87d1989 (Andrew Dunstan, 2026-06-26 "Use named boolean

    Yes, it's intentional for all pg_get_***_ddl functions. Clause with
defaults won't be reconstructed.

---

From: solai v <[email-masked]>
Date: Wed, 1 Jul 2026 17:01:36 +0530

Hi all,

On Wed, Jul 1, 2026 at 4:01 PM Akshay Joshi
<[email-masked]> wrote:
>     Yes, it's intentional for all pg_get_***_ddl functions. Clause with defaults won't be reconstructed.
>>
>>

Ok. Thank you for the clarification.

Regards,
Solai

---

From: Akshay Joshi <[email-masked]>
Date: Mon, 6 Jul 2026 16:58:18 +0530

All,

I found that OID 6517 was already taken by the max(uuid) aggregate. I've
reassigned pg_get_policy_ddl to OID 9683.
The v15 patch is ready for review and commit.

On Wed, Jul 1, 2026 at 5:01 PM solai v <[email-masked]> wrote:

> >> > I've updated the patch to align with the interface change introduced
> by commit d6ed87d1989 (Andrew Dunstan, 2026-06-26 "Use named boolean

---

From: Rui Zhao <[email-masked]>
Date: Thu, 9 Jul 2026 00:58:10 +0800

Hi Akshay,

I tested v15 on current master (57f93af36f): builds, make check passes, and
basic reconstruction, pretty mode, NULL handling, and default-clause omission
all look good. Two things.

1) Back in the v3/v4 discussion you decided the ON <table> name should always
be schema-qualified for safety (the pg_get_triggerdef_worker thread with
jian he), and that pretty here only controls formatting, not schema (Phil's
point). That reasoning didn't reach the USING / WITH CHECK expressions,
though: object references inside them are still qualified only by the caller's
search_path, so they can lose their schema and rebind to a different object
when the DDL is replayed elsewhere:

    CREATE FUNCTION s1.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 > 0';
    CREATE FUNCTION s2.f(int) RETURNS bool LANGUAGE sql AS 'SELECT $1 < 0';
    CREATE POLICY pf ON t2 USING (s1.f(a));

    SET search_path = public, s1;
    SELECT ddl FROM pg_get_policy_ddl('t2', 'pf') AS ddl;
    --  CREATE POLICY pf ON public.t2 USING (f(a));      -- s1. dropped

    SET search_path = public, s2;
    CREATE POLICY pf ON t2 USING (f(a));                 -- now s2.f,
opposite meaning

So within one statement the ON clause is always qualified but the expression
body isn't -- and as Phil noted, the pretty flag can't fix this, since
pg_get_expr qualifies by search_path visibility regardless of pretty.

Worth settling the contract, given where this function sits. The old
pg_get_viewdef / ruledef / indexdef functions are search_path-aware and leave
qualification to the caller (pg_dump sets search_path empty around them so the
output is portable). The new pg_get_*_ddl functions committed so far (role,
database, tablespace) are on global, schemaless objects, so the question never
came up -- pg_get_policy_ddl is the first of the family whose output embeds
schema-qualifiable references. Your own pg_get_table_ddl patch already hit
this and deparses under a controlled search_path (narrowed to pg_catalog), so
the most consistent fix is to do the same here (NewGUCNestLevel + set_config);
an empty search_path already yields the fully-qualified s1.f(a). If instead
the intent is to follow the caller's search_path, that's fine too, but it
should be documented (and SET search_path = '' noted as the way to get
portable DDL).

2) The doc calls the second parameter policy_name, but the actual argument is
policyname, so the documented named-argument call fails:

    SELECT * FROM pg_get_policy_ddl("table" => 't'::regclass,
policy_name => 'p_all');
    ERROR:  function pg_get_policy_ddl(table => regclass, policy_name
=> unknown) does not exist

Regards,
Rui

---

From: Akshay Joshi <[email-masked]>
Date: Thu, 9 Jul 2026 16:27:41 +0530

Thanks Rui for the review. I have tried to fix the issues you raised.
The v16 patch is ready for review/commit.