public inbox for [email protected]
help / color / mirror / Atom feedFrom: Akshay Joshi <[email protected]>
To: Japin Li <[email protected]>
Cc: jian he <[email protected]>
Cc: Marcos Pegoraro <[email protected]>
Cc: Mark Wong <[email protected]>
Cc: Álvaro Herrera <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Mon, 25 May 2026 12:47:41 +0530
Message-ID: <CANxoLDffrZGRTGpW_sPQ-hPEYs0hgjaFgJQh3PJFpPu5Zsbgvg@mail.gmail.com> (raw)
In-Reply-To: <SY7PR01MB109217288F5A2BD343E7A7727B60F2@SY7PR01MB10921.ausprd01.prod.outlook.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<[email protected]>
<CANxoLDfXKWRQ0KZFtaChr2NX3UWSQq3ji7OJqBp-_-th2Zj6Fg@mail.gmail.com>
<aP-hllbRdgqbmB8L@ltdrgnflg2>
<CANxoLDc8UnFuKA2RX6UR_KZWRH6itmrhXK7hoFyF=5kCyfFOGA@mail.gmail.com>
<CANxoLDfyk_+h91FNq5VNemXTBpES0aLPtLp2myTyWgquUHSQ3A@mail.gmail.com>
<CANxoLDfw=ERY89RR08s+qXwgUQvLCFF0pVG_e0fDT14rwZsWRg@mail.gmail.com>
<CAB-JLwYpfK3WCFJ1MT1sAB7Zj9w61wv7fMuCH2vZGW9W4JP15w@mail.gmail.com>
<CANxoLDdV-ciaYqhWiZCRh3Nv+iW9o1LOGvS83TCZ=5X261OjNA@mail.gmail.com>
<CAB-JLwaDPJupWecAsvmNc7y7L5cQjV5P9epSFsTed1hEDbqUSA@mail.gmail.com>
<CANxoLDczdpL9XZhqrA0f8aOocnr_V0az1dtw8iq9YJoN+tWNbw@mail.gmail.com>
<CANxoLDfD_MnK+2=XrWr_fZRh1qbLriO=7MbbZ9UAcwktydFnBA@mail.gmail.com>
<CACJufxETscX67o0t9S6Y8EBBVE21R0zAEG4eXJUYHLidLNfEKQ@mail.gmail.com>
<CANxoLDfdZTLLJqXnnfUYG-Uw4LHBKKnB5f1XOdaQ3ZET=K1qnw@mail.gmail.com>
<SY7PR01MB109217288F5A2BD343E7A7727B60F2@SY7PR01MB10921.ausprd01.prod.outlook.com>
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
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], [email protected], [email protected]
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDffrZGRTGpW_sPQ-hPEYs0hgjaFgJQh3PJFpPu5Zsbgvg@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