public inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Akshay Joshi <akshay.joshi@enterprisedb.com>
To: 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: Thu, 16 Oct 2025 14:04:21 +0530
Message-ID: <CANxoLDdef6wW=T5czPSKPsk3xWeEHTeKxxxYMucmr-HURyoOgQ@mail.gmail.com> (raw)
In-Reply-To: <CAPXBC8+i=c7FCcGr6OR0y=mcx3EfdXuxyk_XYgSQ7+egGvb8vA@mail.gmail.com>
References: <CANxoLDdJsRJqnjMXV3yjsk07Z5iRWxG-c2hZJC7bAKqf8ZXj_A@mail.gmail.com>
<CAPXBC8+i=c7FCcGr6OR0y=mcx3EfdXuxyk_XYgSQ7+egGvb8vA@mail.gmail.com>
On Wed, Oct 15, 2025 at 11:00 PM Philip Alger <paalger0@gmail.com> wrote:
> Hi Akshay,
>
> When applying the patch, I got a number of errors and the tests failed. I
> think it stems from:
>
> + targetTable = relation_open(tableID, NoLock);
> + relation_close(targetTable, NoLock);
>
>
> I changed them to use "AccessShareLock" and it worked, and it's probably
> relevant to change table_close to "AccessShareLock" as well. You're just
> reading from the table.
>
> + table_close(pgPolicyRel, RowExclusiveLock);
>
>
> You might move "Datum valueDatum" to the top of the function. The
> compiler screamed at me for that, so I went ahead and made that change and
> the screaming stopped.
>
> + /* Check if the policy has a TO list */
> + Datum valueDatum = heap_getattr(tuplePolicy,
>
>
Fixed all the above review comments in the v2 patch.
>
> I also don't think you need the extra parenthesis around "USING (%s)" and
> ""WITH CHECK (%s)" in the code; it seems to print it just fine without
> them. Other people might have different opinions.
>
We need to add extra parentheses for the USING and CHECK clauses. Without
them, expressions like USING true or CHECK true will throw a syntax error
at or near "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)
>> ;
>>
>>
> As for the "pretty" part. In my opinion, I don't think it's necessary, and
> putting the statement terminator (;) seems strange.
>
I think the pretty format option is a nice-to-have parameter. Users can
simply set it to false if they don’t want the DDL to be formatted.
As for the statement terminator, it’s useful to include it, while running
multiple queries together could result in a syntax error. In my opinion,
there’s no harm in providing the statement terminator.
However, I’ve modified the logic to add the statement terminator at the end
instead of appending to a new line.
>
> However, I think you're going to get a lot of opinions on what
> well-formatted SQL looks like.
>
> --
> Best,
> Phil Alger
>
Attachments:
[application/octet-stream] v2-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (22.7K, 3-v2-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From cfc9ce4918ca05e4558104177f7d6320364a4642 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 v2] 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>
---
doc/src/sgml/func/func-info.sgml | 45 +++++
src/backend/commands/policy.c | 27 +++
src/backend/utils/adt/ruleutils.c | 186 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +
src/include/commands/policy.h | 2 +
src/test/regress/expected/rowsecurity.out | 196 +++++++++++++++++++++-
src/test/regress/sql/rowsecurity.sql | 78 +++++++++
7 files changed, 536 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..61fe35590f6 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,187 @@ 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);
+ bool attrIsNull;
+ int prettyFlags;
+ Datum valueDatum;
+ HeapTuple tuplePolicy;
+ Relation pgPolicyRel;
+ Relation targetTable;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ Form_pg_policy policyForm;
+
+ StringInfoData buf;
+
+ initStringInfo(&buf);
+
+ targetTable = relation_open(tableID, AccessShareLock);
+ /* 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),
+ 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 */
+ 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);
+ }
+
+ 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);
+ relation_close(targetTable, AccessShareLock);
+ 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 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..992c88f2e3f 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -4821,11 +4821,203 @@ 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 +5048,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, paalger0@gmail.com
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDdef6wW=T5czPSKPsk3xWeEHTeKxxxYMucmr-HURyoOgQ@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