public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Akshay Joshi <akshay.joshi@enterprisedb.com>
To: jian he <jian.universality@gmail.com>
Cc: Philip Alger <paalger0@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Wed, 22 Oct 2025 18:51:50 +0530
Message-ID: <CANxoLDccMKZXA7qWAu6bGXRqVGu_DNPFxP4ssQ5Q4yq9Hwiq-g@mail.gmail.com> (raw)
In-Reply-To: <CACJufxGvfatGyM7RFqMcpSYyNkm-hUWYw2WRKn_=sQwVPGs+CA@mail.gmail.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<CAPXBC8+i=c7FCcGr6OR0y=mcx3EfdXuxyk_XYgSQ7+egGvb8vA@mail.gmail.com>
<CANxoLDdef6wW=T5czPSKPsk3xWeEHTeKxxxYMucmr-HURyoOgQ@mail.gmail.com>
<CAPXBC8J-ZBqaUh7ZcC-SBzTiOZG8P7ssUx6eLKYzVNkzKajpzQ@mail.gmail.com>
<CANxoLDcGLSHDj8Ve0qyM2UWMdgSFJA-28j7dEAtMBey8D_ktdA@mail.gmail.com>
<CACJufxGvfatGyM7RFqMcpSYyNkm-hUWYw2WRKn_=sQwVPGs+CA@mail.gmail.com>
On Wed, Oct 22, 2025 at 12:51 PM jian he <jian.universality@gmail.com>
wrote:
> On Thu, Oct 16, 2025 at 8:51 PM Akshay Joshi
> <akshay.joshi@enterprisedb.com> wrote:
> >
> > Please find attached the v3 patch, which resolves all compilation errors
> and warnings.
> >
>
> drop table if exists t, ts, ts1;
> create table t(a int);
> CREATE POLICY p0 ON t FOR ALL TO PUBLIC USING (a % 2 = 1);
> SELECT pg_get_policy_ddl('t', 'p0', false);
>
> pg_get_policy_ddl
> ---------------------------------------------------------------------
> CREATE POLICY p0 ON t AS PERMISSIVE FOR ALL USING (((a % 2) = 1));
> (1 row)
>
> "TO PUBLIC" part is missing, maybe it's ok.
>
I used the logic below, which did not return PUBLIC as a role. I have added
logic to default the TO clause to PUBLIC when no specific role name is
provided
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);
>
>
> SELECT pg_get_policy_ddl(-1, 'p0', false);
> ERROR: could not open relation with OID 4294967295
> as I mentioned in a nearby thread [1], this should be NULL instead of
> ERROR.
> [1]
> https://postgr.es/m/CACJufxGbE4uJWu1YuqdmOx+7PMBpHvX_fbRMmHu=r4SrsuW9tg@mail.gmail.com
>
> Fixed in v4 patch.
>
> IMHO, get_formatted_string is not needed, most of the time, if pretty is
> true,
> we append "\t" and "\n", for that we can simply do
> ```
> appendStringInfo(&buf, "CREATE POLICY %s ON %s ",
> quote_identifier(NameStr(*policyName)),
> generate_qualified_relation_name(policy_form->polrelid));
> if (pretty)
> appendStringInfoString(buf, "\t\n");
> ```
>
>
The get_formatted_string function is needed. Instead of using multiple if
statements for the pretty flag, it’s better to have a generic function.
This will be useful if the pretty-format DDL implementation is accepted by
the community, as it can be reused by other pg_get_<object>_ddl() DDL
functions added in the future.
>
> in pg_get_triggerdef_worker, I found the below code pattern:
> /*
> * In non-pretty mode, always schema-qualify the target table name for
> * safety. In pretty mode, schema-qualify only if not visible.
> */
> appendStringInfo(&buf, " ON %s ",
> pretty ?
> generate_relation_name(trigrec->tgrelid, NIL) :
> generate_qualified_relation_name(trigrec->tgrelid));
>
> maybe we can apply it too while construct query string:
> "CREATE POLICY %s ON %s",
>
In my opinion, the table name should always be schema-qualified, which I
have addressed in the v4 patch. The implementation of the pretty flag here
differs from pg_get_triggerdef_worker, which is used only for the target
table name. In my patch, the pretty flag adds \t and \n to each different
clause (example AS, FOR, USING ...)
Attachments:
[application/octet-stream] v4-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (24.5K, 3-v4-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From a86ff9fc2fe8f0b903beb6aaec1cd8e8667bbc4a 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 v4] 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', false); -- 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>
Reviewed-by: Philip Alger <paalger0@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 45 +++++
src/backend/commands/policy.c | 27 +++
src/backend/utils/adt/ruleutils.c | 199 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/include/commands/policy.h | 2 +
src/test/regress/expected/rowsecurity.out | 210 +++++++++++++++++++++-
src/test/regress/sql/rowsecurity.sql | 80 +++++++++
7 files changed, 565 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index c393832d94c..4b9c661c20b 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,49 @@ 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>, <parameter>pretty</parameter> <type>boolean</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the CREATE POLICY statement from the system catalogs for a specified table and policy name.
+ When the pretty flag is set to true, the function returns a well-formatted DDL statement.
+ The result is a comprehensive <command>CREATE POLICY</command> statement.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </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..c05e4786703 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"
@@ -546,6 +548,10 @@ 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,
+ bool pretty,
+ int noOfTabChars,
+ const char *fmt,...) pg_attribute_printf(4, 5);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13738,3 +13744,196 @@ get_range_partbound_string(List *bound_datums)
return buf->data;
}
+
+/*
+ * get_formatted_string
+ *
+ * Return a formatted version of the string.
+ *
+ * pretty - If pretty is true, 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, bool pretty, int noOfTabChars, const char *fmt,...)
+{
+ va_list args;
+
+ if (pretty)
+ {
+ /* 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);
+
+ /* If pretty mode, append newline at the end */
+ if (pretty)
+ appendStringInfoChar(buf, '\n');
+}
+
+/*
+ * 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);
+ bool pretty = PG_GETARG_BOOL(2);
+ bool attrIsNull;
+ int prettyFlags;
+ 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)
+ PG_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 */
+ get_formatted_string(&buf, pretty, 0, "CREATE POLICY %s ON %s",
+ quote_identifier(NameStr(*policyName)),
+ targetTable);
+
+ /* Check the type is PERMISSIVE or RESTRICTIVE */
+ get_formatted_string(&buf, pretty, 1,
+ policyForm->polpermissive ? "AS PERMISSIVE" : "AS RESTRICTIVE");
+
+ /* Check command to which the policy applies */
+ get_formatted_string(&buf, pretty, 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, pretty, 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, pretty, 1, "TO PUBLIC");
+ }
+
+ prettyFlags = GET_PRETTY_FLAGS(pretty);
+ /* 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, pretty, 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, pretty, 1, "WITH CHECK (%s)",
+ text_to_cstring(checkExpression));
+ }
+
+ /* Replace '\n' with ';' if newline at the end */
+ if (buf.len > 0 && buf.data[buf.len - 1] == '\n')
+ buf.data[buf.len - 1] = ';';
+ else
+ appendStringInfoChar(&buf, ';');
+
+ /* Clean up. */
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index eecb43ec6f0..536c5a857da 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -4021,6 +4021,9 @@
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 bool', prosrc => 'pg_get_policy_ddl' },
{ 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 42b78a24603..815a4ff72ce 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4842,11 +4842,217 @@ 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', false);
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+SELECT pg_get_policy_ddl('tab1', NULL, false);
+ERROR: relation "tab1" does not exist
+LINE 1: SELECT pg_get_policy_ddl('tab1', NULL, false);
+ ^
+SELECT pg_get_policy_ddl(NULL, NULL, false);
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+-- Test -1 as table oid
+ SELECT pg_get_policy_ddl(-1, 'rls_p1', false);
+ pg_get_policy_ddl
+-------------------
+
+(1 row)
+
+-- Table does not exist
+SELECT pg_get_policy_ddl('tab1', 'rls_p1', false);
+ERROR: relation "tab1" does not exist
+LINE 1: SELECT pg_get_policy_ddl('tab1', 'rls_p1', false);
+ ^
+-- Policy does not exist
+SELECT pg_get_policy_ddl('rls_tbl_1', 'pol1', false);
+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', false);
+ 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', false);
+ 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', false);
+ 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', false);
+ 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', false);
+ 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
@@ -4877,6 +5083,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 2d1be543391..f9f5bd0ae7d 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2403,6 +2403,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', false);
+SELECT pg_get_policy_ddl('tab1', NULL, false);
+SELECT pg_get_policy_ddl(NULL, NULL, false);
+
+-- Test -1 as table oid
+ SELECT pg_get_policy_ddl(-1, 'rls_p1', false);
+
+-- Table does not exist
+SELECT pg_get_policy_ddl('tab1', 'rls_p1', false);
+-- Policy does not exist
+SELECT pg_get_policy_ddl('rls_tbl_1', 'pol1', false);
+
+-- Without Pretty formatted
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p1', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p2', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p3', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p4', false);
+SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p5', false);
+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, jian.universality@gmail.com, paalger0@gmail.com
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDccMKZXA7qWAu6bGXRqVGu_DNPFxP4ssQ5Q4yq9Hwiq-g@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