public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Akshay Joshi <akshay.joshi@enterprisedb.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
Date: Wed, 15 Oct 2025 19:07:12 +0530
Message-ID: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com> (raw)
Hi Hackers,
I’m submitting a patch as part of the broader Retail DDL Functions project
described by Andrew Dunstan
https://www.postgresql.org/message-id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
This patch adds a new system function pg_get_policy_ddl(table, policy_name,
pretty), which reconstructs the CREATE POLICY statement for a given table
and policy. When the pretty flag is set to true, the function returns a
neatly formatted, multi-line DDL statement instead of a single-line
statement.
Usage examples:
1) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', false); -- *non-pretty
formatted DDL*
pg_get_policy_ddl
---------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE POLICY rls_p8 ON rls_tbl_1 AS PERMISSIVE FOR ALL TO
regress_rls_alice, regress_rls_dave USING (true);
2) SELECT pg_get_policy_ddl('rls_tbl_1', 'rls_p8', true); -- *pretty
formatted DDL*
pg_get_policy_ddl
------------------------------------------------
CREATE POLICY rls_p8 ON rls_tbl_1
AS PERMISSIVE
FOR ALL
TO regress_rls_alice, regress_rls_dave
USING (true)
;
The patch includes documentation, in-code comments, and regression tests,
all of which pass successfully.
-----
Regards,
Akshay Joshi
EDB (EnterpriseDB)
Attachments:
[application/octet-stream] 0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (22.3K, 3-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From 7445dc629c2139905f73a6128d4ed30597bdecb1 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] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY
statements
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
---
doc/src/sgml/func/func-info.sgml | 45 +++++
src/backend/commands/policy.c | 27 +++
src/backend/utils/adt/ruleutils.c | 178 +++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/include/commands/policy.h | 2 +
src/test/regress/expected/rowsecurity.out | 199 +++++++++++++++++++++-
src/test/regress/sql/rowsecurity.sql | 78 +++++++++
7 files changed, 531 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 050eef97a4c..6aefbd0de07 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"
@@ -13738,3 +13740,179 @@ 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++)
+ {
+ appendStringInfoString(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);
+ HeapTuple tuplePolicy;
+ Relation pgPolicyRel;
+ Relation targetTable;
+ Form_pg_policy policyForm;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ bool attrIsNull;
+
+ StringInfoData buf;
+
+ initStringInfo(&buf);
+
+ targetTable = relation_open(tableID, NoLock);
+ /* Find policy to begin scan */
+ pgPolicyRel = table_open(PolicyRelationId, RowExclusiveLock);
+
+ /* 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),
+ RelationGetRelationName(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)),
+ RelationGetRelationName(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 */
+ Datum 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);
+ }
+
+ /* 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, false);
+
+ 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, false);
+
+ get_formatted_string(&buf, pretty, 1, "WITH CHECK (%s)",
+ text_to_cstring(checkExpression));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ /* Clean up. */
+ systable_endscan(sscan);
+ relation_close(targetTable, NoLock);
+ table_close(pgPolicyRel, RowExclusiveLock);
+
+ 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 b51d2b17379..d28039e4c08 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 5a172c5d91c..7763f31388d 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4821,11 +4821,206 @@ 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)
+
+-- 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 "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 rls_tbl_1 AS PERMISSIVE FOR ALL 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 rls_tbl_1 AS RESTRICTIVE FOR ALL 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 rls_tbl_1 AS PERMISSIVE FOR ALL 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 rls_tbl_1 AS PERMISSIVE FOR SELECT 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 rls_tbl_1 AS PERMISSIVE FOR INSERT 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 rls_tbl_1 AS PERMISSIVE FOR UPDATE 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 rls_tbl_1 AS PERMISSIVE FOR DELETE 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 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 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR ALL
+ 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 rls_tbl_1
+ AS RESTRICTIVE
+ FOR ALL
+ 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR ALL
+ 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR SELECT
+ 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR INSERT
+ 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR UPDATE
+ 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 rls_tbl_1
+ AS PERMISSIVE
+ FOR DELETE
+ 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 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 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
@@ -4856,6 +5051,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 21ac0ca51ee..75c99b01e27 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2400,6 +2400,84 @@ 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);
+
+
+-- 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
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@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