public inbox for [email protected]
help / color / mirror / Atom feedRe: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
7+ messages / 4 participants
[nested] [flat]
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-01-05 14:30 jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: jian he @ 2026-01-05 14:30 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; Mark Wong <[email protected]>; Álvaro Herrera <[email protected]>; pgsql-hackers
On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
<[email protected]> 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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-22 13:32 Akshay Joshi <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Akshay Joshi @ 2026-05-22 13:32 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: Marcos Pegoraro <[email protected]>; Mark Wong <[email protected]>; Álvaro Herrera <[email protected]>; pgsql-hackers
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 <[email protected]> wrote:
> On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
> <[email protected]> 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/
>
Attachments:
[application/octet-stream] v9-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (24.0K, 3-v9-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From aae0e58182b1dce11e771a0f14dcc1ab142f647e Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 22 May 2026 18:18:07 +0530
Subject: [PATCH v9] Add pg_get_policy_ddl() function to reconstruct CREATE
POLICY statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch introduces a new system function:
pg_get_policy_ddl(table regclass, policy_name name,
VARIADIC options text[]) RETURNS setof text
which reconstructs the CREATE POLICY statement for the named row-level
security policy on the specified table. The result is returned as a single row.
The supported option is:
pretty (boolean) - format the output for readability.
Usage examples:
-- non-pretty formatted DDL (default)
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1');
-- pretty formatted DDL
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1', 'pretty', 'true');
Reference: PG-163
Author: Akshay Joshi <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 20 ++
src/backend/utils/adt/ddlutils.c | 262 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/rowsecurity.out | 193 ++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 87 +++++++
5 files changed, 570 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..44bf6455bb1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,26 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <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>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE POLICY</command> statement for the
+ named row-level security policy on the specified table. The result
+ is returned as a single row.
+ The following option is supported: <literal>pretty</literal>
+ (boolean) for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..728d3648979 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -26,6 +26,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,6 +87,9 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
+static List *pg_get_policy_ddl_internal(Oid tableID, const char *policyName,
+ bool pretty);
+static const char *get_policy_cmd_name(char cmd);
/*
@@ -1185,3 +1189,261 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * get_policy_cmd_name
+ * Map a pg_policy.polcmd char to its SQL keyword.
+ */
+static const char *
+get_policy_cmd_name(char cmd)
+{
+ switch (cmd)
+ {
+ case '*':
+ return "ALL";
+ case ACL_SELECT_CHR:
+ return "SELECT";
+ case ACL_INSERT_CHR:
+ return "INSERT";
+ case ACL_UPDATE_CHR:
+ return "UPDATE";
+ case ACL_DELETE_CHR:
+ return "DELETE";
+ default:
+ elog(ERROR, "unrecognized policy command: %d", (int) cmd);
+ }
+}
+
+/*
+ * pg_get_policy_ddl_internal
+ * Generate the DDL statement to recreate a row-level security policy.
+ *
+ * Returns a List containing a single palloc'd string with the CREATE POLICY
+ * statement. Returning a List keeps the calling convention consistent with
+ * the rest of the pg_get_*_ddl family even though only one row is produced.
+ */
+static List *
+pg_get_policy_ddl_internal(Oid tableID, const char *policyName, bool pretty)
+{
+ Relation pgPolicyRel;
+ HeapTuple tuplePolicy;
+ Form_pg_policy policyForm;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ StringInfoData buf;
+ Datum valueDatum;
+ bool attrIsNull;
+ char *targetTable;
+ List *statements = NIL;
+
+ /* Validate that the relation exists */
+ {
+ char *relname = get_rel_name(tableID);
+ char *nspname;
+
+ if (relname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation with OID %u does not exist", tableID)));
+
+ nspname = get_namespace_name(get_rel_namespace(tableID));
+ if (nspname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema for relation with OID %u does not exist",
+ tableID)));
+
+ targetTable = quote_qualified_identifier(nspname, relname);
+ pfree(relname);
+ pfree(nspname);
+ }
+
+ pgPolicyRel = table_open(PolicyRelationId, AccessShareLock);
+
+ /* Set key - policy's relation id. */
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(tableID));
+
+ /* Set key - policy's name. */
+ ScanKeyInit(&skey[1],
+ Anum_pg_policy_polname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(policyName));
+
+ sscan = systable_beginscan(pgPolicyRel,
+ PolicyPolrelidPolnameIndexId, true, NULL, 2,
+ skey);
+
+ tuplePolicy = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuplePolicy))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("policy \"%s\" for table \"%s\" does not exist",
+ policyName, targetTable)));
+
+ policyForm = (Form_pg_policy) GETSTRUCT(tuplePolicy);
+
+ initStringInfo(&buf);
+
+ /* Build the CREATE POLICY statement */
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s",
+ quote_identifier(policyName),
+ targetTable);
+
+ /*
+ * Emit AS RESTRICTIVE only when it differs from the default (PERMISSIVE).
+ */
+ if (!policyForm->polpermissive)
+ append_ddl_option(&buf, pretty, 4, "AS RESTRICTIVE");
+
+ /*
+ * Emit FOR <cmd> only when it differs from the default (ALL, encoded as
+ * '*').
+ */
+ if (policyForm->polcmd != '*')
+ append_ddl_option(&buf, pretty, 4, "FOR %s",
+ get_policy_cmd_name(policyForm->polcmd));
+
+ /*
+ * Emit TO <roles> only when it differs from the default (PUBLIC). PUBLIC
+ * is encoded in polroles as a single InvalidOid element, so we omit the
+ * clause whenever every entry is InvalidOid.
+ */
+ 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);
+ StringInfoData role_names;
+
+ initStringInfo(&role_names);
+
+ for (int i = 0; i < nitems; i++)
+ {
+ if (OidIsValid(roles[i]))
+ {
+ char *rolename = GetUserNameFromId(roles[i], false);
+
+ if (role_names.len > 0)
+ appendStringInfoString(&role_names, ", ");
+ appendStringInfoString(&role_names, quote_identifier(rolename));
+ }
+ }
+
+ if (role_names.len > 0)
+ append_ddl_option(&buf, pretty, 4, "TO %s", role_names.data);
+
+ pfree(role_names.data);
+ pfree(policy_roles);
+ }
+
+ /* USING expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polqual,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "USING (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ /* WITH CHECK expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "WITH CHECK (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_policy_ddl
+ * Return DDL to recreate a row-level security policy as a single text row.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tableID;
+ Name policyName;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ tableID = PG_GETARG_OID(0);
+ policyName = PG_GETARG_NAME(1);
+
+ parse_ddl_options(fcinfo, 2, opts, lengthof(opts));
+
+ statements = pg_get_policy_ddl_internal(tableID,
+ NameStr(*policyName),
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..3e32c89fd6d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8615,6 +8615,14 @@
proargtypes => 'regdatabase text', proallargtypes => '{regdatabase,text}',
proargmodes => '{i,v}', proargdefaults => '{NULL}',
prosrc => 'pg_get_database_ddl' },
+{ oid => '6517', descr => 'get DDL to recreate a row-level security policy',
+ proname => 'pg_get_policy_ddl', prorows => '1', provariadic => 'text',
+ proisstrict => 'f', proretset => 't', provolatile => 's',
+ pronargdefaults => '1', prorettype => 'text',
+ proargtypes => 'regclass name text',
+ proallargtypes => '{regclass,name,text}',
+ proargmodes => '{i,i,v}', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_policy_ddl' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..9a20536584d 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5195,6 +5195,199 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+ERROR: relation "nonexistent_tbl" does not exist
+LINE 1: SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1')...
+ ^
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+ERROR: policy "nonexistent_pol" for table "regress_rls_schema.rls_tbl_1" does not exist
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+ERROR: unrecognized option: "badopt"
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+ERROR: invalid value for boolean option "pretty": maybe
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+ERROR: option "pretty" is specified more than once
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+ pg_get_policy_ddl
+-------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1 USING ((dlevel <= ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2 +
+ WHERE (rls_tbl_2.pguser = CURRENT_USER))));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1 AS RESTRICTIVE USING (((cid <> 44) AND (cid < 50)));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------
+ CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 USING ((dauthor = CURRENT_USER));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1 FOR SELECT USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1 FOR INSERT WITH CHECK (((cid % 2) = 1));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------
+ CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1 FOR DELETE USING ((cid < 8));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1 TO regress_rls_exempt_user WITH CHECK ((cid = ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2)));
+(1 row)
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1
+ USING (dlevel <= (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2
+ WHERE rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1
+ AS RESTRICTIVE
+ USING (cid <> 44 AND cid < 50);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1
+ USING (dauthor = CURRENT_USER);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1
+ FOR SELECT
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1
+ FOR INSERT
+ WITH CHECK ((cid % 2) = 1);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1
+ FOR UPDATE
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1
+ FOR DELETE
+ USING (cid < 8);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_dave, regress_rls_alice
+ USING (true);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_exempt_user
+ WITH CHECK (cid = (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2)));
+(1 row)
+\pset format aligned
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..aca6102de71 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2598,6 +2598,93 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+\pset format aligned
+
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+
--
-- Clean up objects
--
--
2.51.0
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-22 16:24 Japin Li <[email protected]>
parent: Akshay Joshi <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Japin Li @ 2026-05-22 16:24 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: jian he <[email protected]>; Marcos Pegoraro <[email protected]>; Mark Wong <[email protected]>; Álvaro Herrera <[email protected]>; pgsql-hackers
Hi, Akshay
On Fri, 22 May 2026 at 19:02, Akshay Joshi <[email protected]> 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 <[email protected]> wrote:
>
> On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
> <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-25 07:17 Akshay Joshi <[email protected]>
parent: Japin Li <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Akshay Joshi @ 2026-05-25 07:17 UTC (permalink / raw)
To: Japin Li <[email protected]>; +Cc: jian he <[email protected]>; Marcos Pegoraro <[email protected]>; Mark Wong <[email protected]>; Álvaro Herrera <[email protected]>; pgsql-hackers
Thanks Japin,
Attached is the updated patch.
On Fri, May 22, 2026 at 9:55 PM Japin Li <[email protected]> wrote:
>
> Hi, Akshay
>
> On Fri, 22 May 2026 at 19:02, Akshay Joshi <[email protected]>
> 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 <[email protected]>
> wrote:
> >
> > On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
> > <[email protected]> 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.
>
Attachments:
[application/octet-stream] v10-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (24.3K, 3-v10-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From e3c100793993f009ba5245b9d8a566cf832d2d88 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 22 May 2026 18:18:07 +0530
Subject: [PATCH v10] Add pg_get_policy_ddl() function to reconstruct CREATE
POLICY statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch introduces a new system function:
pg_get_policy_ddl(table regclass, policy_name name,
VARIADIC options text[]) RETURNS setof text
which reconstructs the CREATE POLICY statement for the named row-level
security policy on the specified table. The result is returned as a single row.
The supported option is:
pretty (boolean) - format the output for readability.
Usage examples:
-- non-pretty formatted DDL (default)
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1');
-- pretty formatted DDL
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1', 'pretty', 'true');
Reference: PG-163
Author: Akshay Joshi <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 20 ++
src/backend/utils/adt/ddlutils.c | 270 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/rowsecurity.out | 193 ++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 87 +++++++
5 files changed, 578 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..44bf6455bb1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,26 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <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>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE POLICY</command> statement for the
+ named row-level security policy on the specified table. The result
+ is returned as a single row.
+ The following option is supported: <literal>pretty</literal>
+ (boolean) for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..15696b8c801 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -26,6 +26,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,6 +87,9 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
+static List *pg_get_policy_ddl_internal(Oid tableID, const char *policyName,
+ bool pretty);
+static const char *get_policy_cmd_name(char cmd);
/*
@@ -1185,3 +1189,269 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * get_policy_cmd_name
+ * Map a pg_policy.polcmd char to its SQL keyword.
+ */
+static const char *
+get_policy_cmd_name(char cmd)
+{
+ switch (cmd)
+ {
+ case '*':
+ return "ALL";
+ case ACL_SELECT_CHR:
+ return "SELECT";
+ case ACL_INSERT_CHR:
+ return "INSERT";
+ case ACL_UPDATE_CHR:
+ return "UPDATE";
+ case ACL_DELETE_CHR:
+ return "DELETE";
+ default:
+ elog(ERROR, "unrecognized policy command: %d", (int) cmd);
+ }
+}
+
+/*
+ * pg_get_policy_ddl_internal
+ * Generate the DDL statement to recreate a row-level security policy.
+ *
+ * Returns a List containing a single palloc'd string with the CREATE POLICY
+ * statement. Returning a List keeps the calling convention consistent with
+ * the rest of the pg_get_*_ddl family even though only one row is produced.
+ */
+static List *
+pg_get_policy_ddl_internal(Oid tableID, const char *policyName, bool pretty)
+{
+ Relation pgPolicyRel;
+ HeapTuple tuplePolicy;
+ Form_pg_policy policyForm;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ StringInfoData buf;
+ Datum valueDatum;
+ bool attrIsNull;
+ char *targetTable;
+ List *statements = NIL;
+
+ /* Validate that the relation exists */
+ {
+ char *relname = get_rel_name(tableID);
+ char *nspname;
+
+ if (relname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation with OID %u does not exist", tableID)));
+
+ nspname = get_namespace_name(get_rel_namespace(tableID));
+ if (nspname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema for relation with OID %u does not exist",
+ tableID)));
+
+ targetTable = quote_qualified_identifier(nspname, relname);
+ pfree(relname);
+ pfree(nspname);
+ }
+
+ pgPolicyRel = table_open(PolicyRelationId, AccessShareLock);
+
+ /* Set key - policy's relation id. */
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(tableID));
+
+ /* Set key - policy's name. */
+ ScanKeyInit(&skey[1],
+ Anum_pg_policy_polname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(policyName));
+
+ sscan = systable_beginscan(pgPolicyRel,
+ PolicyPolrelidPolnameIndexId, true, NULL, 2,
+ skey);
+
+ tuplePolicy = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuplePolicy))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("policy \"%s\" for table \"%s\" does not exist",
+ policyName, targetTable)));
+
+ policyForm = (Form_pg_policy) GETSTRUCT(tuplePolicy);
+
+ initStringInfo(&buf);
+
+ /* Build the CREATE POLICY statement */
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s",
+ quote_identifier(policyName),
+ targetTable);
+
+ /*
+ * Emit AS RESTRICTIVE only when it differs from the default (PERMISSIVE).
+ */
+ if (!policyForm->polpermissive)
+ append_ddl_option(&buf, pretty, 4, "AS RESTRICTIVE");
+
+ /*
+ * Emit FOR <cmd> only when it differs from the default (ALL, encoded as
+ * '*').
+ */
+ if (policyForm->polcmd != '*')
+ append_ddl_option(&buf, pretty, 4, "FOR %s",
+ get_policy_cmd_name(policyForm->polcmd));
+
+ /*
+ * Emit TO <roles> only when it differs from the default (PUBLIC). PUBLIC
+ * is encoded in polroles as a single InvalidOid element, so we omit the
+ * clause whenever every entry is InvalidOid.
+ */
+ 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);
+ StringInfoData role_names;
+
+ initStringInfo(&role_names);
+
+ for (int i = 0; i < nitems; i++)
+ {
+ if (OidIsValid(roles[i]))
+ {
+ char *rolename = GetUserNameFromId(roles[i], false);
+
+ if (role_names.len > 0)
+ appendStringInfoString(&role_names, ", ");
+ appendStringInfoString(&role_names, quote_identifier(rolename));
+ }
+ }
+
+ if (role_names.len > 0)
+ append_ddl_option(&buf, pretty, 4, "TO %s", role_names.data);
+
+ pfree(role_names.data);
+ pfree(policy_roles);
+ }
+
+ /* USING expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polqual,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ /*
+ * In non-pretty mode pg_get_expr() already wraps the expression in
+ * parentheses, so emit it verbatim; in pretty mode we have to add
+ * the parentheses required by CREATE POLICY syntax ourselves.
+ */
+ append_ddl_option(&buf, pretty, 4,
+ pretty ? "USING (%s)" : "USING %s",
+ TextDatumGetCString(expr));
+ }
+
+ /* WITH CHECK expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ /* See comment above about parenthesization. */
+ append_ddl_option(&buf, pretty, 4,
+ pretty ? "WITH CHECK (%s)" : "WITH CHECK %s",
+ TextDatumGetCString(expr));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_policy_ddl
+ * Return DDL to recreate a row-level security policy as a single text row.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tableID;
+ Name policyName;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ tableID = PG_GETARG_OID(0);
+ policyName = PG_GETARG_NAME(1);
+
+ parse_ddl_options(fcinfo, 2, opts, lengthof(opts));
+
+ statements = pg_get_policy_ddl_internal(tableID,
+ NameStr(*policyName),
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..3e32c89fd6d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8615,6 +8615,14 @@
proargtypes => 'regdatabase text', proallargtypes => '{regdatabase,text}',
proargmodes => '{i,v}', proargdefaults => '{NULL}',
prosrc => 'pg_get_database_ddl' },
+{ oid => '6517', descr => 'get DDL to recreate a row-level security policy',
+ proname => 'pg_get_policy_ddl', prorows => '1', provariadic => 'text',
+ proisstrict => 'f', proretset => 't', provolatile => 's',
+ pronargdefaults => '1', prorettype => 'text',
+ proargtypes => 'regclass name text',
+ proallargtypes => '{regclass,name,text}',
+ proargmodes => '{i,i,v}', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_policy_ddl' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..4e5dd039e91 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5195,6 +5195,199 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+ERROR: relation "nonexistent_tbl" does not exist
+LINE 1: SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1')...
+ ^
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+ERROR: policy "nonexistent_pol" for table "regress_rls_schema.rls_tbl_1" does not exist
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+ERROR: unrecognized option: "badopt"
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+ERROR: invalid value for boolean option "pretty": maybe
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+ERROR: option "pretty" is specified more than once
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1 USING (dlevel <= ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2 +
+ WHERE (rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+ pg_get_policy_ddl
+---------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1 AS RESTRICTIVE USING ((cid <> 44) AND (cid < 50));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+ pg_get_policy_ddl
+--------------------------------------------------------------------------------------
+ CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 USING (dauthor = CURRENT_USER);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------
+ CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1 FOR SELECT USING ((cid % 2) = 0);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+ pg_get_policy_ddl
+---------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1 FOR INSERT WITH CHECK ((cid % 2) = 1);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------
+ CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING ((cid % 2) = 0);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------
+ CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1 FOR DELETE USING (cid < 8);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+ pg_get_policy_ddl
+---------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING true;
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2));
+(1 row)
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1
+ USING (dlevel <= (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2
+ WHERE rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1
+ AS RESTRICTIVE
+ USING (cid <> 44 AND cid < 50);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1
+ USING (dauthor = CURRENT_USER);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1
+ FOR SELECT
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1
+ FOR INSERT
+ WITH CHECK ((cid % 2) = 1);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1
+ FOR UPDATE
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1
+ FOR DELETE
+ USING (cid < 8);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_dave, regress_rls_alice
+ USING (true);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_exempt_user
+ WITH CHECK (cid = (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2)));
+(1 row)
+\pset format aligned
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..aca6102de71 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2598,6 +2598,93 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+\pset format aligned
+
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+
--
-- Clean up objects
--
--
2.51.0
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-28 13:41 Ilmar Y <[email protected]>
parent: Akshay Joshi <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Ilmar Y @ 2026-05-28 13:41 UTC (permalink / raw)
To: [email protected]; +Cc: Akshay Joshi <[email protected]>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-29 06:50 Akshay Joshi <[email protected]>
parent: Ilmar Y <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Akshay Joshi @ 2026-05-29 06:50 UTC (permalink / raw)
To: Ilmar Y <[email protected]>; +Cc: [email protected]
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 <[email protected]> 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
>
Attachments:
[application/x-patch] v11-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (25.5K, 3-v11-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From f62ea394cdefed5fe7c6760cceaf2c70f5ec09cc Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 22 May 2026 18:18:07 +0530
Subject: [PATCH v11] Add pg_get_policy_ddl() function to reconstruct CREATE
POLICY statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch introduces a new system function:
pg_get_policy_ddl(table regclass, policy_name name,
VARIADIC options text[]) RETURNS setof text
which reconstructs the CREATE POLICY statement for the named row-level
security policy on the specified table. The result is returned as a single row.
The supported option is:
pretty (boolean) - format the output for readability.
Usage examples:
-- non-pretty formatted DDL (default)
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1');
-- pretty formatted DDL
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1', 'pretty', 'true');
Reference: PG-163
Author: Akshay Joshi <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 20 ++
src/backend/utils/adt/ddlutils.c | 262 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/rowsecurity.out | 215 ++++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 102 +++++++++
5 files changed, 607 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..44bf6455bb1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,26 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <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>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE POLICY</command> statement for the
+ named row-level security policy on the specified table. The result
+ is returned as a single row.
+ The following option is supported: <literal>pretty</literal>
+ (boolean) for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..728d3648979 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -26,6 +26,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,6 +87,9 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
+static List *pg_get_policy_ddl_internal(Oid tableID, const char *policyName,
+ bool pretty);
+static const char *get_policy_cmd_name(char cmd);
/*
@@ -1185,3 +1189,261 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * get_policy_cmd_name
+ * Map a pg_policy.polcmd char to its SQL keyword.
+ */
+static const char *
+get_policy_cmd_name(char cmd)
+{
+ switch (cmd)
+ {
+ case '*':
+ return "ALL";
+ case ACL_SELECT_CHR:
+ return "SELECT";
+ case ACL_INSERT_CHR:
+ return "INSERT";
+ case ACL_UPDATE_CHR:
+ return "UPDATE";
+ case ACL_DELETE_CHR:
+ return "DELETE";
+ default:
+ elog(ERROR, "unrecognized policy command: %d", (int) cmd);
+ }
+}
+
+/*
+ * pg_get_policy_ddl_internal
+ * Generate the DDL statement to recreate a row-level security policy.
+ *
+ * Returns a List containing a single palloc'd string with the CREATE POLICY
+ * statement. Returning a List keeps the calling convention consistent with
+ * the rest of the pg_get_*_ddl family even though only one row is produced.
+ */
+static List *
+pg_get_policy_ddl_internal(Oid tableID, const char *policyName, bool pretty)
+{
+ Relation pgPolicyRel;
+ HeapTuple tuplePolicy;
+ Form_pg_policy policyForm;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ StringInfoData buf;
+ Datum valueDatum;
+ bool attrIsNull;
+ char *targetTable;
+ List *statements = NIL;
+
+ /* Validate that the relation exists */
+ {
+ char *relname = get_rel_name(tableID);
+ char *nspname;
+
+ if (relname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation with OID %u does not exist", tableID)));
+
+ nspname = get_namespace_name(get_rel_namespace(tableID));
+ if (nspname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema for relation with OID %u does not exist",
+ tableID)));
+
+ targetTable = quote_qualified_identifier(nspname, relname);
+ pfree(relname);
+ pfree(nspname);
+ }
+
+ pgPolicyRel = table_open(PolicyRelationId, AccessShareLock);
+
+ /* Set key - policy's relation id. */
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(tableID));
+
+ /* Set key - policy's name. */
+ ScanKeyInit(&skey[1],
+ Anum_pg_policy_polname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(policyName));
+
+ sscan = systable_beginscan(pgPolicyRel,
+ PolicyPolrelidPolnameIndexId, true, NULL, 2,
+ skey);
+
+ tuplePolicy = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuplePolicy))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("policy \"%s\" for table \"%s\" does not exist",
+ policyName, targetTable)));
+
+ policyForm = (Form_pg_policy) GETSTRUCT(tuplePolicy);
+
+ initStringInfo(&buf);
+
+ /* Build the CREATE POLICY statement */
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s",
+ quote_identifier(policyName),
+ targetTable);
+
+ /*
+ * Emit AS RESTRICTIVE only when it differs from the default (PERMISSIVE).
+ */
+ if (!policyForm->polpermissive)
+ append_ddl_option(&buf, pretty, 4, "AS RESTRICTIVE");
+
+ /*
+ * Emit FOR <cmd> only when it differs from the default (ALL, encoded as
+ * '*').
+ */
+ if (policyForm->polcmd != '*')
+ append_ddl_option(&buf, pretty, 4, "FOR %s",
+ get_policy_cmd_name(policyForm->polcmd));
+
+ /*
+ * Emit TO <roles> only when it differs from the default (PUBLIC). PUBLIC
+ * is encoded in polroles as a single InvalidOid element, so we omit the
+ * clause whenever every entry is InvalidOid.
+ */
+ 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);
+ StringInfoData role_names;
+
+ initStringInfo(&role_names);
+
+ for (int i = 0; i < nitems; i++)
+ {
+ if (OidIsValid(roles[i]))
+ {
+ char *rolename = GetUserNameFromId(roles[i], false);
+
+ if (role_names.len > 0)
+ appendStringInfoString(&role_names, ", ");
+ appendStringInfoString(&role_names, quote_identifier(rolename));
+ }
+ }
+
+ if (role_names.len > 0)
+ append_ddl_option(&buf, pretty, 4, "TO %s", role_names.data);
+
+ pfree(role_names.data);
+ pfree(policy_roles);
+ }
+
+ /* USING expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polqual,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "USING (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ /* WITH CHECK expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "WITH CHECK (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_policy_ddl
+ * Return DDL to recreate a row-level security policy as a single text row.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tableID;
+ Name policyName;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ tableID = PG_GETARG_OID(0);
+ policyName = PG_GETARG_NAME(1);
+
+ parse_ddl_options(fcinfo, 2, opts, lengthof(opts));
+
+ statements = pg_get_policy_ddl_internal(tableID,
+ NameStr(*policyName),
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..3e32c89fd6d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8615,6 +8615,14 @@
proargtypes => 'regdatabase text', proallargtypes => '{regdatabase,text}',
proargmodes => '{i,v}', proargdefaults => '{NULL}',
prosrc => 'pg_get_database_ddl' },
+{ oid => '6517', descr => 'get DDL to recreate a row-level security policy',
+ proname => 'pg_get_policy_ddl', prorows => '1', provariadic => 'text',
+ proisstrict => 'f', proretset => 't', provolatile => 's',
+ pronargdefaults => '1', prorettype => 'text',
+ proargtypes => 'regclass name text',
+ proallargtypes => '{regclass,name,text}',
+ proargmodes => '{i,i,v}', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_policy_ddl' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..502f9b86f64 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5195,6 +5195,221 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+ERROR: relation "nonexistent_tbl" does not exist
+LINE 1: SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1')...
+ ^
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+ERROR: policy "nonexistent_pol" for table "regress_rls_schema.rls_tbl_1" does not exist
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+ERROR: unrecognized option: "badopt"
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+ERROR: invalid value for boolean option "pretty": maybe
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+ERROR: option "pretty" is specified more than once
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+ pg_get_policy_ddl
+-------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1 USING ((dlevel <= ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2 +
+ WHERE (rls_tbl_2.pguser = CURRENT_USER))));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1 AS RESTRICTIVE USING (((cid <> 44) AND (cid < 50)));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------
+ CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 USING ((dauthor = CURRENT_USER));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1 FOR SELECT USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1 FOR INSERT WITH CHECK (((cid % 2) = 1));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------
+ CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1 FOR DELETE USING ((cid < 8));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1 TO regress_rls_exempt_user WITH CHECK ((cid = ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2)));
+(1 row)
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1
+ USING (dlevel <= (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2
+ WHERE rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1
+ AS RESTRICTIVE
+ USING (cid <> 44 AND cid < 50);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1
+ USING (dauthor = CURRENT_USER);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1
+ FOR SELECT
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1
+ FOR INSERT
+ WITH CHECK ((cid % 2) = 1);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1
+ FOR UPDATE
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1
+ FOR DELETE
+ USING (cid < 8);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_dave, regress_rls_alice
+ USING (true);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_exempt_user
+ WITH CHECK (cid = (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2)));
+(1 row)
+\pset format aligned
+-- Round-trip: the generated DDL must be re-executable, including for atomic
+-- boolean expressions that pg_get_expr() does not parenthesize.
+CREATE TABLE rls_rt (a int);
+CREATE POLICY rt_true ON rls_rt USING (true);
+CREATE POLICY rt_false ON rls_rt FOR INSERT WITH CHECK (false);
+CREATE TEMP TABLE rt_ddl AS
+ SELECT pg_get_policy_ddl('rls_rt', 'rt_true') AS ddl
+ UNION ALL
+ SELECT pg_get_policy_ddl('rls_rt', 'rt_false');
+DROP POLICY rt_true ON rls_rt;
+DROP POLICY rt_false ON rls_rt;
+SELECT ddl FROM rt_ddl ORDER BY ddl \gexec
+CREATE POLICY rt_false ON regress_rls_schema.rls_rt FOR INSERT WITH CHECK (false);
+CREATE POLICY rt_true ON regress_rls_schema.rls_rt USING (true);
+SELECT polname FROM pg_policy WHERE polrelid = 'rls_rt'::regclass ORDER BY polname;
+ polname
+----------
+ rt_false
+ rt_true
+(2 rows)
+
+DROP TABLE rls_rt;
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..d3b2e226d07 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2598,6 +2598,108 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+\pset format aligned
+
+-- Round-trip: the generated DDL must be re-executable, including for atomic
+-- boolean expressions that pg_get_expr() does not parenthesize.
+CREATE TABLE rls_rt (a int);
+CREATE POLICY rt_true ON rls_rt USING (true);
+CREATE POLICY rt_false ON rls_rt FOR INSERT WITH CHECK (false);
+CREATE TEMP TABLE rt_ddl AS
+ SELECT pg_get_policy_ddl('rls_rt', 'rt_true') AS ddl
+ UNION ALL
+ SELECT pg_get_policy_ddl('rls_rt', 'rt_false');
+DROP POLICY rt_true ON rls_rt;
+DROP POLICY rt_false ON rls_rt;
+SELECT ddl FROM rt_ddl ORDER BY ddl \gexec
+SELECT polname FROM pg_policy WHERE polrelid = 'rls_rt'::regclass ORDER BY polname;
+DROP TABLE rls_rt;
+
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+
--
-- Clean up objects
--
--
2.51.0
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
@ 2026-05-29 07:38 Japin Li <[email protected]>
parent: Akshay Joshi <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Japin Li @ 2026-05-29 07:38 UTC (permalink / raw)
To: Akshay Joshi <[email protected]>; +Cc: Ilmar Y <[email protected]>; [email protected]
On Fri, 29 May 2026 at 12:20, Akshay Joshi <[email protected]> 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 <[email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2026-05-29 07:38 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-05 14:30 Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement jian he <[email protected]>
2026-05-22 13:32 ` Akshay Joshi <[email protected]>
2026-05-22 16:24 ` Japin Li <[email protected]>
2026-05-25 07:17 ` Akshay Joshi <[email protected]>
2026-05-28 13:41 ` Ilmar Y <[email protected]>
2026-05-29 06:50 ` Akshay Joshi <[email protected]>
2026-05-29 07:38 ` Japin Li <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox