Thread

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