Thread

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