public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Akshay Joshi <akshay.joshi@enterprisedb.com>
To: Mark Wong <markwkm@gmail.com>
Cc: Álvaro Herrera <alvherre@kurilemu.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Mon, 3 Nov 2025 17:17:40 +0530
Message-ID: <CANxoLDfyk_+h91FNq5VNemXTBpES0aLPtLp2myTyWgquUHSQ3A@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDc8UnFuKA2RX6UR_KZWRH6itmrhXK7hoFyF=5kCyfFOGA@mail.gmail.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<202510151529.s3fpwsgben57@alvherre.pgsql>
<CANxoLDfXKWRQ0KZFtaChr2NX3UWSQq3ji7OJqBp-_-th2Zj6Fg@mail.gmail.com>
<aP-hllbRdgqbmB8L@ltdrgnflg2>
<CANxoLDc8UnFuKA2RX6UR_KZWRH6itmrhXK7hoFyF=5kCyfFOGA@mail.gmail.com>
Hi Hackers,
Added a new #define GET_DDL_PRETTY_FLAGS because the existing #define
GET_PRETTY_FLAGS is not suitable for formatting reconstructed DDLs. The
existing #define GET_PRETTY_FLAGS always indents the code, regardless of
whether the flag is set to true or false, which is not the desired behavior
for pg_get_<object>_ddl functions.
Updated the logic of the get_formatted_string function based on Tim
Waizenegger’s suggestion.
I am attaching the new v6 patch, which is ready for review.
On Tue, Oct 28, 2025 at 3:08 PM Akshay Joshi <akshay.joshi@enterprisedb.com>
wrote:
> Thanks, Mark, for your review comments and the updated patch.
>
> I’ve incorporated your changes and prepared a combined v5 patch. The v5
> patch is attached for further review.
>
> On Mon, Oct 27, 2025 at 10:15 PM Mark Wong <markwkm@gmail.com> wrote:
>
>> Hi everyone,
>>
>> On Thu, Oct 16, 2025 at 01:47:53PM +0530, Akshay Joshi wrote:
>> >
>> >
>> > On Wed, Oct 15, 2025 at 10:55 PM Álvaro Herrera <alvherre@kurilemu.de>
>> wrote:
>> >
>> > Hello,
>> >
>> > I have reviewed this patch before and provided a number of comments
>> that
>> > have been addressed by Akshay (so I encourage you to list my name
>> and
>> > this address in a Reviewed-by trailer line in the commit message).
>> One
>> > thing I had not noticed is that while this function has a "pretty"
>> flag,
>> > it doesn't use it to pass anything to pg_get_expr_worker()'s
>> prettyFlags
>> > argument, and I think it should -- probably just
>> >
>> > prettyFlags = GET_PRETTY_FLAGS(pretty);
>> >
>> > same as pg_get_querydef() does.
>>
>> Kinda sorta similar thought, I've noticed some existing functions like
>> pg_get_constraintdef make the "pretty" flag optional, so I'm wondering
>> if that scheme is also preferred here.
>>
>> I've attached a small diff to the original
>> 0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch to
>> illustrate the additional work to follow suit, if so desired.
>>
>> Regards,
>> Mark
>> --
>> Mark Wong <markwkm@gmail.com>
>> EDB https://enterprisedb.com
>>
>
Attachments:
[application/octet-stream] v6-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (25.7K, 3-v6-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From 0d505dc15d89ba59a845d4057a67096d4e5f4284 Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Fri, 10 Oct 2025 15:46:13 +0530
Subject: [PATCH v6] 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(regclass table, name policy_name, bool pretty),
which reconstructs the CREATE POLICY statement for the specified policy.
Usage examples:
SELECT pg_get_policy_ddl('rls_table', 'pol1'); -- non-pretty formatted DDL
SELECT pg_get_policy_ddl('rls_table', 'pol1', true); -- pretty formatted DDL
Reference: PG-163
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
---
doc/src/sgml/func/func-info.sgml | 55 ++++++
src/backend/commands/policy.c | 27 +++
src/backend/utils/adt/ruleutils.c | 227 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 6 +
src/include/commands/policy.h | 2 +
src/test/regress/expected/rowsecurity.out | 207 +++++++++++++++++++-
src/test/regress/sql/rowsecurity.sql | 80 ++++++++
7 files changed, 603 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..2210a49a478 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,59 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions described in <xref linkend="functions-get-object-ddl-table"/>
+ return the Data Definition Language (DDL) statement for any given database object.
+ This feature is implemented as a set of distinct functions for each object type.
+ </para>
+
+ <table id="functions-get-object-ddl-table">
+ <title>Get Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <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>
+ </tgroup>
+ </table>
+
+ <para>
+ Most of the functions that reconstruct (decompile) database objects have an
+ optional <parameter>pretty</parameter> flag, which if
+ <literal>true</literal> causes the result to be
+ <quote>pretty-printed</quote>. Pretty-printing adds tab character and new
+ line character for legibility. Passing <literal>false</literal> for the
+ <parameter>pretty</parameter> parameter yields the same result as omitting
+ the parameter.
+ </para>
+
+ </sect2>
+
</sect1>
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 83056960fe4..1abe0c44353 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -128,6 +128,33 @@ parse_policy_command(const char *cmd_name)
return polcmd;
}
+/*
+ * get_policy_cmd_name -
+ * helper function to convert char representation to full command strings.
+ *
+ * cmd - Valid values are '*', 'r', 'a', 'w' and 'd'.
+ *
+ */
+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");
+ }
+}
+
/*
* policy_role_list_to_array
* helper function to convert a list of RoleSpecs to an array of
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 79ec136231b..5bf8a1e6467 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -33,12 +33,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/tablespace.h"
+#include "commands/policy.h"
#include "common/keywords.h"
#include "executor/spi.h"
#include "funcapi.h"
@@ -94,6 +96,10 @@
((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
: PRETTYFLAG_INDENT)
+#define GET_DDL_PRETTY_FLAGS(pretty) \
+ ((pretty) ? (PRETTYFLAG_PAREN | PRETTYFLAG_INDENT | PRETTYFLAG_SCHEMA) \
+ : 0)
+
/* Default line length for pretty-print wrapping: 0 means wrap always */
#define WRAP_COLUMN_DEFAULT 0
@@ -546,6 +552,12 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
deparse_context *context,
bool showimplicit,
bool needcomma);
+static void get_formatted_string(StringInfo buf,
+ int prettyFlags,
+ int noOfTabChars,
+ const char *fmt,...) pg_attribute_printf(4, 5);
+static char *pg_get_policy_ddl_worker(Oid tableID, Name policyName,
+ int prettyFlags);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13738,3 +13750,218 @@ get_range_partbound_string(List *bound_datums)
return buf->data;
}
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * prettyFlags - Based on prettyFlags the output includes tabs (\t) and
+ * newlines (\n).
+ * noOfTabChars - indent with specified no of tabs.
+ * fmt - printf-style format string used by appendStringInfoVA.
+ */
+static void
+get_formatted_string(StringInfo buf, int prettyFlags, int noOfTabChars, const char *fmt,...)
+{
+ va_list args;
+
+ if (prettyFlags & PRETTYFLAG_INDENT)
+ {
+ appendStringInfoChar(buf, '\n');
+ /* Indent with tabs */
+ for (int i = 0; i < noOfTabChars; i++)
+ {
+ appendStringInfoChar(buf, '\t');
+ }
+ }
+ else
+ appendStringInfoChar(buf, ' ');
+
+ va_start(args, fmt);
+ appendStringInfoVA(buf, fmt, args);
+ va_end(args);
+}
+
+/*
+ * pg_get_policy_ddl
+ *
+ * Generate a CREATE POLICY statement for the specified policy.
+ *
+ * tableID - Table ID of the policy.
+ * policyName - Name of the policy for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+ Oid tableID = PG_GETARG_OID(0);
+ Name policyName = PG_GETARG_NAME(1);
+ char *res;
+
+ res = pg_get_policy_ddl_worker(tableID, policyName, false);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+Datum
+pg_get_policy_ddl_ext(PG_FUNCTION_ARGS)
+{
+ Oid tableID = PG_GETARG_OID(0);
+ Name policyName = PG_GETARG_NAME(1);
+ bool pretty = PG_GETARG_BOOL(2);
+ int prettyFlags;
+ char *res;
+
+ prettyFlags = GET_DDL_PRETTY_FLAGS(pretty);
+ res = pg_get_policy_ddl_worker(tableID, policyName, prettyFlags);
+
+ if (res == NULL)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(string_to_text(res));
+}
+
+static char *
+pg_get_policy_ddl_worker(Oid tableID, Name policyName, int prettyFlags)
+{
+ bool attrIsNull;
+ Datum valueDatum;
+ HeapTuple tuplePolicy;
+ Relation pgPolicyRel;
+ char *targetTable;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ Form_pg_policy policyForm;
+ StringInfoData buf;
+
+ /* Validate that the relation exists */
+ if (!OidIsValid(tableID) || get_rel_name(tableID) == NULL)
+ return NULL;
+
+ initStringInfo(&buf);
+
+ targetTable = generate_qualified_relation_name(tableID);
+ /* Find policy to begin scan */
+ 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(NameStr(*policyName)));
+
+ sscan = systable_beginscan(pgPolicyRel,
+ PolicyPolrelidPolnameIndexId, true, NULL, 2,
+ skey);
+
+ tuplePolicy = systable_getnext(sscan);
+ /* Check that the policy is found, raise an error if not. */
+ if (!HeapTupleIsValid(tuplePolicy))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("policy \"%s\" for table \"%s\" does not exist",
+ NameStr(*policyName),
+ targetTable)));
+
+ policyForm = (Form_pg_policy) GETSTRUCT(tuplePolicy);
+
+ /* Build the CREATE POLICY statement */
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s",
+ quote_identifier(NameStr(*policyName)),
+ targetTable);
+
+ /* Check the type is PERMISSIVE or RESTRICTIVE */
+ get_formatted_string(&buf, prettyFlags, 1,
+ policyForm->polpermissive ? "AS PERMISSIVE" : "AS RESTRICTIVE");
+
+ /* Check command to which the policy applies */
+ get_formatted_string(&buf, prettyFlags, 1, "FOR %s",
+ get_policy_cmd_name(policyForm->polcmd));
+
+ /* Check if the policy has a TO list */
+ 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 (i > 0)
+ appendStringInfoString(&role_names, ", ");
+ appendStringInfoString(&role_names, rolename);
+ }
+ }
+
+ if (role_names.len > 0)
+ get_formatted_string(&buf, prettyFlags, 1, "TO %s", role_names.data);
+ else
+
+ /*
+ * When no specific role is provided, generate the TO clause with
+ * the PUBLIC role.
+ */
+ get_formatted_string(&buf, prettyFlags, 1, "TO PUBLIC");
+ }
+
+ /* Check if the policy has a USING expr */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polqual,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ text *exprtext = DatumGetTextPP(valueDatum);
+ text *usingExpression = pg_get_expr_worker(exprtext,
+ policyForm->polrelid,
+ prettyFlags);
+
+ get_formatted_string(&buf, prettyFlags, 1, "USING (%s)",
+ text_to_cstring(usingExpression));
+ }
+
+ /* Check if the policy has a WITH CHECK expr */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ text *exprtext = DatumGetTextPP(valueDatum);
+ text *checkExpression = pg_get_expr_worker(exprtext,
+ policyForm->polrelid,
+ prettyFlags);
+
+ get_formatted_string(&buf, prettyFlags, 1, "WITH CHECK (%s)",
+ text_to_cstring(checkExpression));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ /* Clean up. */
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+
+ return buf.data;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 9121a382f76..69683fb37f7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,12 @@
proname => 'pg_get_function_sqlbody', provolatile => 's',
prorettype => 'text', proargtypes => 'oid',
prosrc => 'pg_get_function_sqlbody' },
+{ oid => '8811', descr => 'get CREATE statement for policy',
+ proname => 'pg_get_policy_ddl', prorettype => 'text',
+ proargtypes => 'regclass name', prosrc => 'pg_get_policy_ddl' },
+{ oid => '8812', descr => 'get CREATE statement for policy with pretty option',
+ proname => 'pg_get_policy_ddl', prorettype => 'text',
+ proargtypes => 'regclass name bool', prosrc => 'pg_get_policy_ddl_ext' },
{ oid => '1686', descr => 'list of SQL keywords',
proname => 'pg_get_keywords', procost => '10', prorows => '500',
diff --git a/src/include/commands/policy.h b/src/include/commands/policy.h
index f06aa1df439..40e45b738f4 100644
--- a/src/include/commands/policy.h
+++ b/src/include/commands/policy.h
@@ -35,4 +35,6 @@ extern ObjectAddress rename_policy(RenameStmt *stmt);
extern bool relation_has_policies(Relation rel);
+extern char *get_policy_cmd_name(char cmd);
+
#endif /* POLICY_H */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index c958ef4d70a..4ebe32711cd 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5101,11 +5101,214 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(tableName, policyName, 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 NULL value
+SELECT pg_get_policy_ddl(NULL, 'rls_p1');
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+SELECT pg_get_policy_ddl('tab1', NULL);
+ERROR: relation "tab1" does not exist
+LINE 1: SELECT pg_get_policy_ddl('tab1', NULL);
+ ^
+SELECT pg_get_policy_ddl(NULL, NULL);
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+-- Test -1 as table oid
+ SELECT pg_get_policy_ddl(-1, 'rls_p1');
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+-- Table does not exist
+SELECT pg_get_policy_ddl('tab1', 'rls_p1');
+ERROR: relation "tab1" does not exist
+LINE 1: SELECT pg_get_policy_ddl('tab1', 'rls_p1');
+ ^
+-- Policy does not exist
+SELECT pg_get_policy_ddl('rls_tbl_1', 'pol1');
+ERROR: policy "pol1" for table "regress_rls_schema.rls_tbl_1" does not exist
+-- Without Pretty formatted
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+ pg_get_policy_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR ALL TO PUBLIC USING ((dlevel <= (SELECT rls_tbl_2.seclv FROM rls_tbl_2 WHERE (rls_tbl_2.pguser = CURRENT_USER))));
+(1 row)
+
+SELECT 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 FOR ALL TO PUBLIC USING (((cid <> 44) AND (cid < 50)));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR ALL TO PUBLIC USING ((dauthor = CURRENT_USER));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR SELECT TO PUBLIC USING (((cid % 2) = 0));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR INSERT TO PUBLIC WITH CHECK (((cid % 2) = 1));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p6', false);
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR UPDATE TO PUBLIC USING (((cid % 2) = 0));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p7', false);
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR DELETE TO PUBLIC USING ((cid < 8));
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', false);
+ pg_get_policy_ddl
+---------------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR ALL TO regress_rls_dave, regress_rls_alice USING (true);
+(1 row)
+
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p9', false);
+ pg_get_policy_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1 AS PERMISSIVE FOR ALL TO regress_rls_exempt_user WITH CHECK ((cid = (SELECT rls_tbl_2.seclv FROM rls_tbl_2)));
+(1 row)
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT 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
+ AS PERMISSIVE
+ FOR ALL
+ TO PUBLIC
+ USING (dlevel <= (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2
+ WHERE rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+SELECT 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
+ FOR ALL
+ TO PUBLIC
+ USING (cid <> 44 AND cid < 50);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR ALL
+ TO PUBLIC
+ USING (dauthor = CURRENT_USER);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR SELECT
+ TO PUBLIC
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR INSERT
+ TO PUBLIC
+ WITH CHECK ((cid % 2) = 1);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR UPDATE
+ TO PUBLIC
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR DELETE
+ TO PUBLIC
+ USING (cid < 8);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR ALL
+ TO regress_rls_dave, regress_rls_alice
+ USING (true);
+(1 row)
+SELECT 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
+ AS PERMISSIVE
+ FOR ALL
+ TO regress_rls_exempt_user
+ WITH CHECK (cid = (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2)));
+(1 row)
+-- 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;
+--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
DROP SCHEMA regress_rls_schema CASCADE;
-NOTICE: drop cascades to 30 other objects
+NOTICE: drop cascades to 32 other objects
DETAIL: drop cascades to function f_leak(text)
drop cascades to table uaccount
drop cascades to table category
@@ -5136,6 +5339,8 @@ drop cascades to table dep1
drop cascades to table dep2
drop cascades to table dob_t1
drop cascades to table dob_t2
+drop cascades to table rls_tbl_1
+drop cascades to table rls_tbl_2
DROP USER regress_rls_alice;
DROP USER regress_rls_bob;
DROP USER regress_rls_carol;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 5d923c5ca3b..b90f5309578 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2542,6 +2542,86 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(tableName, policyName, 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 NULL value
+SELECT pg_get_policy_ddl(NULL, 'rls_p1');
+SELECT pg_get_policy_ddl('tab1', NULL);
+SELECT pg_get_policy_ddl(NULL, NULL);
+
+-- Test -1 as table oid
+ SELECT pg_get_policy_ddl(-1, 'rls_p1');
+
+-- Table does not exist
+SELECT pg_get_policy_ddl('tab1', 'rls_p1');
+-- Policy does not exist
+SELECT pg_get_policy_ddl('rls_tbl_1', 'pol1');
+
+-- Without Pretty formatted
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p6', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p7', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p9', false);
+
+-- With Pretty formatted
+\pset format unaligned
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p1', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p2', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p3', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p4', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p5', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p6', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p7', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p9', true);
+
+-- 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;
+
--
-- 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: pgsql-hackers@postgresql.org
Cc: akshay.joshi@enterprisedb.com, markwkm@gmail.com, alvherre@kurilemu.de
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDfyk_+h91FNq5VNemXTBpES0aLPtLp2myTyWgquUHSQ3A@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