public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: solai v <[email protected]>
Cc: Ilmar Y <[email protected]>
Cc: [email protected]
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Tue, 30 Jun 2026 16:48:26 +0530
Message-ID: <CANxoLDcQyd=ojcmLCmU5zFT8j475AbXHpjSWHoVP4a5-vdY3Jw@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDeqR6wVCoXjsEWv4M_mhng-GCtt1KxFdfM-8PKwushUCA@mail.gmail.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<CANxoLDffrZGRTGpW_sPQ-hPEYs0hgjaFgJQh3PJFpPu5Zsbgvg@mail.gmail.com>
<177997571870.313758.10720313850275742354.pgcf@coridan.postgresql.org>
<CANxoLDe7xiCWY-UEmzoK_uQdKh3PPNcGXJ3qdzFy3c0o_F+P_Q@mail.gmail.com>
<SY7PR01MB1092118DD149062E230159D89B6162@SY7PR01MB10921.ausprd01.prod.outlook.com>
<CAF0whuefRgV3_xgMhPoKaid1LN34CTLhTU-r5dgfFyEGVAFrPg@mail.gmail.com>
<CANxoLDfBQn+q32DKo5yvb63eD+965umMi8zxqqV8GcsxdN6Trg@mail.gmail.com>
<CANxoLDf80gXFLSqWM7d-idA=Grot49HsmP9YXZw49nCFe+Mi+g@mail.gmail.com>
<CANxoLDeqR6wVCoXjsEWv4M_mhng-GCtt1KxFdfM-8PKwushUCA@mail.gmail.com>
All,
I've updated the patch to align with the interface change introduced by
commit *d6ed87d1989* (Andrew Dunstan, 2026-06-26 "Use named boolean
parameters for pg_get_*_ddl option arguments").
That commit replaced the VARIADIC text[] alternating key/value option
interface with typed named boolean parameters across pg_get_role_ddl(),
pg_get_tablespace_ddl(), and pg_get_database_ddl(), removing the
DdlOption/parse_ddl_options() machinery in favour of direct
PG_GETARG_BOOL() calls.
Updated patch v14 is ready for review/commit.
On Mon, Jun 29, 2026 at 7:12 PM Akshay Joshi <[email protected]>
wrote:
> Rebased *v13* patch is now ready for review and commit.
>
> On Mon, Jun 22, 2026 at 6:49 PM Akshay Joshi <
> [email protected]> wrote:
>
>> *Chao Li* found an issue in my other patch for *pg_get_table_ddl.* Specifically,
>> the existing pattern in pg_proc.dat for variadic-text functions (e.g.,
>> jsonb_delete, json_extract_path) uses *_text* instead of *text* at the
>> variadic position in both proargtypes and proallargtypes, with provariadic
>> => 'text'. This is the convention documented by the sanity check in
>> src/test/regress/sql/opr_sanity.sql.
>>
>> I realized the same issue was present in this patch as well, so I have
>> fixed it and added corresponding test cases.
>>
>> The v12 patch is now ready for review and commit.
>>
>> On Mon, Jun 22, 2026 at 6:34 PM Akshay Joshi <
>> [email protected]> wrote:
>>
>>>
>>>
>>> On Mon, Jun 8, 2026 at 6:10 PM solai v <[email protected]> wrote:
>>>
>>>> Hi all,
>>>>
>>>>
>>>> On Mon, Jun 8, 2026 at 5:32 PM Japin Li <[email protected]> wrote:
>>>> >
>>>> > 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
>>>> >
>>>>
>>>>
>>>> I reviewed and tested the V11 pg_get_policy_ddl() patch. I verified
>>>> the basic functionality by creating various row-level security
>>>> policies and checking the reconstructed DDL returned by
>>>> pg_get_policy_ddl(). The function correctly reconstructed policies for
>>>> different command types (SELECT, INSERT, UPDATE, DELETE), PERMISSIVE
>>>> and RESTRICTIVE policies, multiple role lists, quoted identifiers,
>>>> USING clauses, and WITH CHECK clauses.
>>>> I also tested more complex cases involving subqueries. Policies
>>>> containing EXISTS subqueries in both USING and WITH CHECK clauses were
>>>> reconstructed successfully. Nested subqueries were also handled
>>>> correctly, and I did not encounter any issues related to expression
>>>> reconstruction or variable handling. I verified NULL input handling as
>>>> well. Passing NULL values for the table name or policy name returned
>>>> no rows as expected. Invalid relation names resulted in the expected
>>>> regclass resolution error. One observation from testing is that the
>>>> generated DDL omits default clauses such as FOR ALL and TO PUBLIC. For
>>>> example, a policy created with FOR ALL TO PUBLIC is reconstructed
>>>> without those clauses, while still producing semantically equivalent
>>>> DDL. I'm not sure whether this is intentional or if the function is
>>>> expected to reproduce all clauses explicitly, but it may be worth
>>>> discussing.
>>>> Overall, the patch worked as expected in all scenarios I tested, and I
>>>> did not find any functional issues.
>>>>
>>>
>>> Thanks for the review. The omission of FOR ALL and TO PUBLIC is
>>> intentional and matches the long-standing convention used by
>>> pg_get_indexdef, pg_get_constraintdef, pg_get_viewdef, etc.: emit a clause
>>> only when it differs from the parser's default. The result is semantically
>>> equivalent to the CREATE POLICY DDL.
>>>
>>>>
>>>>
>>>> Regards,
>>>> Solai
>>>>
>>>
Attachments:
[application/octet-stream] v14-0001-Add-pg_get_policy_ddl-to-reconstruct-CREATE.patch (31.5K, ../CANxoLDcQyd=ojcmLCmU5zFT8j475AbXHpjSWHoVP4a5-vdY3Jw@mail.gmail.com/3-v14-0001-Add-pg_get_policy_ddl-to-reconstruct-CREATE.patch)
download | inline diff:
From 9b2d4e22060b80ead0ac33cbf8122463ba972573 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <[email protected]>
Date: Fri, 22 May 2026 18:18:07 +0530
Subject: [PATCH v14] Add pg_get_policy_ddl() to reconstruct CREATE POLICY
statements
pg_get_policy_ddl(table regclass,
policyname name,
pretty bool DEFAULT false)
RETURNS SETOF text
reconstructs the CREATE POLICY statement for the named row-level
security policy on the specified table. Although a single statement is
produced, the function returns a SETOF text result so its calling
convention matches the rest of the pg_get_*_ddl family.
The reconstructed DDL includes all clauses of the CREATE POLICY syntax:
the policy's permissiveness (AS RESTRICTIVE), command type (FOR
SELECT/INSERT/UPDATE/DELETE), role list (TO <roles>), USING
qualification, and WITH CHECK expression. Clauses whose value equals
the parser's default -- PERMISSIVE, FOR ALL, and TO PUBLIC -- are
omitted from the output, matching the convention used by pg_get_indexdef,
pg_get_constraintdef, pg_get_viewdef, and similar functions. The result
is therefore semantically equivalent to the original DDL, not lexically
identical.
The pretty parameter controls output formatting and defaults to false,
following the same convention as pg_get_role_ddl, pg_get_tablespace_ddl,
and pg_get_database_ddl. NULL passed explicitly for pretty is treated
as false.
NULL inputs for the table or policy name yield no rows. An invalid
relation name surfaces the regclass resolution error; a non-existent
policy raises an explicit "policy ... does not exist" error.
Usage examples:
-- compact form (default)
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1');
-- pretty-printed form
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', true);
Regression coverage is added to src/test/regress/sql/rowsecurity.sql
and exercises all valid combinations of the CREATE POLICY syntax:
PERMISSIVE/RESTRICTIVE, all FOR command variants (ALL/SELECT/INSERT/
UPDATE/DELETE), multi-role TO lists, USING-only, WITH CHECK-only, and
USING+WITH CHECK policies for both ALL and UPDATE commands (the only
two that accept both), RESTRICTIVE on a specific command type,
subquery expressions, pretty and non-pretty output, all boolean
representations for the pretty argument (true/false, on/off, 1/0),
NULL and error paths, and a round-trip test that drops and re-executes
the generated DDL.
Author: Akshay Joshi <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 19 ++
src/backend/utils/adt/ddlutils.c | 259 +++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/test/regress/expected/rowsecurity.out | 291 ++++++++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 128 ++++++++++
5 files changed, 703 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 69ef3857cfa..858ea4609a6 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3960,6 +3960,25 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
is false, the <literal>OWNER</literal> clause is omitted.
</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>, <parameter>pretty</parameter> <type>boolean</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. If <parameter>pretty</parameter> is
+ true, the output is formatted for readability; the default is false.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index a70f1c28655..cf7b596085f 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"
@@ -56,6 +57,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);
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);
/*
@@ -975,3 +979,258 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * get_policy_cmd_name
+ * Map a non-ALL pg_policy.polcmd char to its SQL keyword.
+ *
+ * Callers must guard against '*' (ALL) themselves; it is not passed here.
+ */
+static const char *
+get_policy_cmd_name(char cmd)
+{
+ switch (cmd)
+ {
+ 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);
+ return NULL; /* keep compiler quiet */
+ }
+}
+
+/*
+ * 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 *relname;
+ char *nspname;
+ char *targetTable;
+ List *statements = NIL;
+
+ /* Validate that the relation exists and build its qualified name. */
+ relname = get_rel_name(tableID);
+ 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);
+ pfree(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. polroles is always non-NULL.
+ */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polroles,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ Assert(!attrIsNull);
+ {
+ ArrayType *policy_roles = DatumGetArrayTypeP(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));
+ pfree(rolename);
+ }
+ }
+
+ if (role_names.len > 0)
+ append_ddl_option(&buf, pretty, 4, "TO %s", role_names.data);
+
+ pfree(role_names.data);
+ }
+
+ /* 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;
+ bool pretty;
+
+ 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);
+ pretty = !PG_ARGISNULL(2) && PG_GETARG_BOOL(2);
+
+ statements = pg_get_policy_ddl_internal(tableID,
+ NameStr(*policyName),
+ pretty);
+ 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 73bb7fbb430..9c46f3676de 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8620,6 +8620,12 @@
proargtypes => 'regdatabase bool bool bool',
proargnames => '{database,pretty,owner,tablespace}',
proargdefaults => '{false,true,true}', prosrc => 'pg_get_database_ddl' },
+{ oid => '6517', descr => 'get DDL to recreate a row-level security policy',
+ proname => 'pg_get_policy_ddl', prorows => '1', proisstrict => 'f',
+ proretset => 't', provolatile => 's', pronargdefaults => '1',
+ prorettype => 'text', proargtypes => 'regclass name bool',
+ proargnames => '{table,policyname,pretty}', proargdefaults => '{false}',
+ 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..bf54f66339f 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5195,6 +5195,297 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(table, policy_name[, pretty]) 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));
+-- Test UPDATE policy with both USING and WITH CHECK
+CREATE POLICY rls_p10 ON rls_tbl_1 FOR UPDATE
+ USING (dlevel > 0) WITH CHECK (dlevel < 100);
+-- Test ALL policy with both USING and WITH CHECK (AS PERMISSIVE and FOR ALL are defaults, omitted)
+CREATE POLICY rls_p11 ON rls_tbl_1
+ USING (dlevel >= 1) WITH CHECK (dlevel <= 99);
+-- Test RESTRICTIVE on a specific command
+CREATE POLICY rls_p12 ON rls_tbl_1 AS RESTRICTIVE FOR SELECT
+ USING (cid > 0);
+-- 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
+-- Without pretty formatting (default); also verify explicit false equals 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');
+ 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');
+ 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');
+ 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');
+ 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)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10');
+ pg_get_policy_ddl
+--------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p10 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING ((dlevel > 0)) WITH CHECK ((dlevel < 100));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p11');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p11 ON regress_rls_schema.rls_tbl_1 USING ((dlevel >= 1)) WITH CHECK ((dlevel <= 99));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p12');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p12 ON regress_rls_schema.rls_tbl_1 AS RESTRICTIVE FOR SELECT USING ((cid > 0));
+(1 row)
+
+-- Explicit false must match omitting the argument
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', false);
+ pg_get_policy_ddl
+--------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p10 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING ((dlevel > 0)) WITH CHECK ((dlevel < 100));
+(1 row)
+
+-- NULL pretty must also default to non-pretty
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', NULL);
+ pg_get_policy_ddl
+--------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p10 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING ((dlevel > 0)) WITH CHECK ((dlevel < 100));
+(1 row)
+
+-- pretty accepts all standard boolean representations: true/false, on/off, 1/0
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'on'::bool);
+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_p3', '1'::bool);
+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_p3', 'off'::bool);
+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_p3', '0'::bool);
+pg_get_policy_ddl
+CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 USING ((dauthor = CURRENT_USER));
+(1 row)
+\pset format aligned
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 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', 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', 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', 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', 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', 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', 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', 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', 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)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', true);
+pg_get_policy_ddl
+CREATE POLICY rls_p10 ON regress_rls_schema.rls_tbl_1
+ FOR UPDATE
+ USING (dlevel > 0)
+ WITH CHECK (dlevel < 100);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p11', true);
+pg_get_policy_ddl
+CREATE POLICY rls_p11 ON regress_rls_schema.rls_tbl_1
+ USING (dlevel >= 1)
+ WITH CHECK (dlevel <= 99);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p12', true);
+pg_get_policy_ddl
+CREATE POLICY rls_p12 ON regress_rls_schema.rls_tbl_1
+ AS RESTRICTIVE
+ FOR SELECT
+ USING (cid > 0);
+(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 POLICY rls_p10 ON rls_tbl_1;
+DROP POLICY rls_p11 ON rls_tbl_1;
+DROP POLICY rls_p12 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..e4d64813e65 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2598,6 +2598,134 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(table, policy_name[, pretty]) 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));
+
+-- Test UPDATE policy with both USING and WITH CHECK
+CREATE POLICY rls_p10 ON rls_tbl_1 FOR UPDATE
+ USING (dlevel > 0) WITH CHECK (dlevel < 100);
+
+-- Test ALL policy with both USING and WITH CHECK (AS PERMISSIVE and FOR ALL are defaults, omitted)
+CREATE POLICY rls_p11 ON rls_tbl_1
+ USING (dlevel >= 1) WITH CHECK (dlevel <= 99);
+
+-- Test RESTRICTIVE on a specific command
+CREATE POLICY rls_p12 ON rls_tbl_1 AS RESTRICTIVE FOR SELECT
+ USING (cid > 0);
+
+-- 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');
+
+-- Without pretty formatting (default); also verify explicit false equals 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');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p11');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p12');
+-- Explicit false must match omitting the argument
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', false);
+-- NULL pretty must also default to non-pretty
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', NULL);
+
+-- pretty accepts all standard boolean representations: true/false, on/off, 1/0
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'on'::bool);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', '1'::bool);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'off'::bool);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', '0'::bool);
+\pset format aligned
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p10', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p11', true);
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p12', 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 POLICY rls_p10 ON rls_tbl_1;
+DROP POLICY rls_p11 ON rls_tbl_1;
+DROP POLICY rls_p12 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+
--
-- Clean up objects
--
--
2.51.0
view thread (44+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDcQyd=ojcmLCmU5zFT8j475AbXHpjSWHoVP4a5-vdY3Jw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox