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: Marcos Pegoraro <marcos@f10.com.br>
Cc: 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: Fri, 22 May 2026 19:02:46 +0530
Message-ID: <CANxoLDfdZTLLJqXnnfUYG-Uw4LHBKKnB5f1XOdaQ3ZET=K1qnw@mail.gmail.com> (raw)
In-Reply-To: <CACJufxETscX67o0t9S6Y8EBBVE21R0zAEG4eXJUYHLidLNfEKQ@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>
<CANxoLDfyk_+h91FNq5VNemXTBpES0aLPtLp2myTyWgquUHSQ3A@mail.gmail.com>
<CANxoLDfw=ERY89RR08s+qXwgUQvLCFF0pVG_e0fDT14rwZsWRg@mail.gmail.com>
<CAB-JLwYpfK3WCFJ1MT1sAB7Zj9w61wv7fMuCH2vZGW9W4JP15w@mail.gmail.com>
<CANxoLDdV-ciaYqhWiZCRh3Nv+iW9o1LOGvS83TCZ=5X261OjNA@mail.gmail.com>
<CAB-JLwaDPJupWecAsvmNc7y7L5cQjV5P9epSFsTed1hEDbqUSA@mail.gmail.com>
<CANxoLDczdpL9XZhqrA0f8aOocnr_V0az1dtw8iq9YJoN+tWNbw@mail.gmail.com>
<CANxoLDfD_MnK+2=XrWr_fZRh1qbLriO=7MbbZ9UAcwktydFnBA@mail.gmail.com>
<CACJufxETscX67o0t9S6Y8EBBVE21R0zAEG4eXJUYHLidLNfEKQ@mail.gmail.com>
Hi hackers,
Following the recently committed *pg_get_database_ddl()*, which adopted a
VARIADIC options text[] style for DDL-reconstruction functions, here is a
patch in the same spirit for row-level security policies.
The new function:
pg_get_policy_ddl(table regclass, policy_name name, VARIADIC options
text[]) RETURNS setof text
Reconstructs the CREATE POLICY statement for the named policy on the given
table, returning the result as a single row.
The currently supported option is pretty (boolean) for formatted output.
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true');
NULL inputs for table or policy_name return no rows. Unknown option names,
invalid boolean values, and duplicate options are reported as errors
consistent with the pattern established by pg_get_database_ddl().
The patch includes documentation updates in func-info.sgml and regression
tests in rowsecurity.sql covering PERMISSIVE/RESTRICTIVE, each command type
(ALL/SELECT/INSERT/UPDATE/DELETE), TO role lists, both USING and WITH CHECK
clauses, pretty/non-pretty output, and the error paths above.
Patch is ready for review.
On Mon, Jan 5, 2026 at 8:00 PM jian he <jian.universality@gmail.com> wrote:
> On Thu, Nov 20, 2025 at 5:27 PM Akshay Joshi
> <akshay.joshi@enterprisedb.com> wrote:
> >
> > Attached is the v8 patch for your review, with updated variable names
> and a rebase applied.
> >
> hi.
>
> + <tbody>
> + <row>
> + <entry role="func_table_entry"><para role="func_signature">
> + <indexterm>
> + <primary>pg_get_policy_ddl</primary>
> + </indexterm>
> + <function>pg_get_policy_ddl</function>
> + ( <parameter>table</parameter> <type>regclass</type>,
> <parameter>policy_name</parameter> <type>name</type>, <optional>
> <parameter>pretty</parameter> <type>boolean</type> </optional> )
> + <returnvalue>text</returnvalue>
> + </para>
> + <para>
> + Reconstructs the <command>CREATE POLICY</command> statement from
> the
> + system catalogs for a specified table and policy name. The result
> is a
> + comprehensive <command>CREATE POLICY</command> statement.
> + </para></entry>
> + </row>
> + </tbody>
>
> ( <parameter>table</parameter> <type>regclass</type> ...
> this line is way too long, we can split it into several lines, it
> won't affect the appearance.
>
> like:
> <function>pg_get_policy_ddl</function>
> ( <parameter>table</parameter> <type>regclass</type>,
> <parameter>policy_name</parameter> <type>name</type>,
> <optional> <parameter>pretty</parameter>
> <type>boolean</type> </optional> )
> <returnvalue>text</returnvalue>
>
> Also, the explanation does not mention that the default value of
> pretty is false.
>
>
> index 2d946d6d9e9..a5e22374668 100644
> --- a/src/backend/catalog/system_functions.sql
> +++ b/src/backend/catalog/system_functions.sql
> @@ -657,6 +657,12 @@ LANGUAGE INTERNAL
> STRICT VOLATILE PARALLEL UNSAFE
> AS 'pg_replication_origin_session_setup';
>
> +CREATE OR REPLACE FUNCTION
> + pg_get_policy_ddl(tableID regclass, policyName name, pretty bool
> DEFAULT false)
> +RETURNS text
> +LANGUAGE INTERNAL
> +AS 'pg_get_policy_ddl';
> +
>
> The partial upper casing above has no effect; it's the same as
> ``pg_get_policy_ddl(tableid regclass, policyname name, pretty bool
> DEFAULT false)``
>
> --
> jian
> https://www.enterprisedb.com/
>
Attachments:
[application/octet-stream] v9-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch (24.0K, 3-v9-0001-Add-pg_get_policy_ddl-function-to-reconstruct-CREATE.patch)
download | inline diff:
From aae0e58182b1dce11e771a0f14dcc1ab142f647e Mon Sep 17 00:00:00 2001
From: Akshay Joshi <akshay.joshi@enterprisedb.com>
Date: Fri, 22 May 2026 18:18:07 +0530
Subject: [PATCH v9] Add pg_get_policy_ddl() function to reconstruct CREATE
POLICY statements.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
This patch introduces a new system function:
pg_get_policy_ddl(table regclass, policy_name name,
VARIADIC options text[]) RETURNS setof text
which reconstructs the CREATE POLICY statement for the named row-level
security policy on the specified table. The result is returned as a single row.
The supported option is:
pretty (boolean) - format the output for readability.
Usage examples:
-- non-pretty formatted DDL (default)
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1');
-- pretty formatted DDL
SELECT * FROM pg_get_policy_ddl('rls_table', 'pol1', 'pretty', 'true');
SELECT * FROM pg_get_policy_ddl(16564, 'pol1', 'pretty', 'true');
Reference: PG-163
Author: Akshay Joshi <akshay.joshi@enterprisedb.com>
---
doc/src/sgml/func/func-info.sgml | 20 ++
src/backend/utils/adt/ddlutils.c | 262 ++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/rowsecurity.out | 193 ++++++++++++++++
src/test/regress/sql/rowsecurity.sql | 87 +++++++
5 files changed, 570 insertions(+)
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..44bf6455bb1 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,26 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_policy_ddl</primary>
+ </indexterm>
+ <function>pg_get_policy_ddl</function>
+ ( <parameter>table</parameter> <type>regclass</type>,
+ <parameter>policy_name</parameter> <type>name</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE POLICY</command> statement for the
+ named row-level security policy on the specified table. The result
+ is returned as a single row.
+ The following option is supported: <literal>pretty</literal>
+ (boolean) for formatted output.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..728d3648979 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -26,6 +26,7 @@
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_policy.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,6 +87,9 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
+static List *pg_get_policy_ddl_internal(Oid tableID, const char *policyName,
+ bool pretty);
+static const char *get_policy_cmd_name(char cmd);
/*
@@ -1185,3 +1189,261 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * get_policy_cmd_name
+ * Map a pg_policy.polcmd char to its SQL keyword.
+ */
+static const char *
+get_policy_cmd_name(char cmd)
+{
+ switch (cmd)
+ {
+ case '*':
+ return "ALL";
+ case ACL_SELECT_CHR:
+ return "SELECT";
+ case ACL_INSERT_CHR:
+ return "INSERT";
+ case ACL_UPDATE_CHR:
+ return "UPDATE";
+ case ACL_DELETE_CHR:
+ return "DELETE";
+ default:
+ elog(ERROR, "unrecognized policy command: %d", (int) cmd);
+ }
+}
+
+/*
+ * pg_get_policy_ddl_internal
+ * Generate the DDL statement to recreate a row-level security policy.
+ *
+ * Returns a List containing a single palloc'd string with the CREATE POLICY
+ * statement. Returning a List keeps the calling convention consistent with
+ * the rest of the pg_get_*_ddl family even though only one row is produced.
+ */
+static List *
+pg_get_policy_ddl_internal(Oid tableID, const char *policyName, bool pretty)
+{
+ Relation pgPolicyRel;
+ HeapTuple tuplePolicy;
+ Form_pg_policy policyForm;
+ ScanKeyData skey[2];
+ SysScanDesc sscan;
+ StringInfoData buf;
+ Datum valueDatum;
+ bool attrIsNull;
+ char *targetTable;
+ List *statements = NIL;
+
+ /* Validate that the relation exists */
+ {
+ char *relname = get_rel_name(tableID);
+ char *nspname;
+
+ if (relname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation with OID %u does not exist", tableID)));
+
+ nspname = get_namespace_name(get_rel_namespace(tableID));
+ if (nspname == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_SCHEMA),
+ errmsg("schema for relation with OID %u does not exist",
+ tableID)));
+
+ targetTable = quote_qualified_identifier(nspname, relname);
+ pfree(relname);
+ pfree(nspname);
+ }
+
+ pgPolicyRel = table_open(PolicyRelationId, AccessShareLock);
+
+ /* Set key - policy's relation id. */
+ ScanKeyInit(&skey[0],
+ Anum_pg_policy_polrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(tableID));
+
+ /* Set key - policy's name. */
+ ScanKeyInit(&skey[1],
+ Anum_pg_policy_polname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(policyName));
+
+ sscan = systable_beginscan(pgPolicyRel,
+ PolicyPolrelidPolnameIndexId, true, NULL, 2,
+ skey);
+
+ tuplePolicy = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuplePolicy))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("policy \"%s\" for table \"%s\" does not exist",
+ policyName, targetTable)));
+
+ policyForm = (Form_pg_policy) GETSTRUCT(tuplePolicy);
+
+ initStringInfo(&buf);
+
+ /* Build the CREATE POLICY statement */
+ appendStringInfo(&buf, "CREATE POLICY %s ON %s",
+ quote_identifier(policyName),
+ targetTable);
+
+ /*
+ * Emit AS RESTRICTIVE only when it differs from the default (PERMISSIVE).
+ */
+ if (!policyForm->polpermissive)
+ append_ddl_option(&buf, pretty, 4, "AS RESTRICTIVE");
+
+ /*
+ * Emit FOR <cmd> only when it differs from the default (ALL, encoded as
+ * '*').
+ */
+ if (policyForm->polcmd != '*')
+ append_ddl_option(&buf, pretty, 4, "FOR %s",
+ get_policy_cmd_name(policyForm->polcmd));
+
+ /*
+ * Emit TO <roles> only when it differs from the default (PUBLIC). PUBLIC
+ * is encoded in polroles as a single InvalidOid element, so we omit the
+ * clause whenever every entry is InvalidOid.
+ */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polroles,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ ArrayType *policy_roles = DatumGetArrayTypePCopy(valueDatum);
+ int nitems = ARR_DIMS(policy_roles)[0];
+ Oid *roles = (Oid *) ARR_DATA_PTR(policy_roles);
+ StringInfoData role_names;
+
+ initStringInfo(&role_names);
+
+ for (int i = 0; i < nitems; i++)
+ {
+ if (OidIsValid(roles[i]))
+ {
+ char *rolename = GetUserNameFromId(roles[i], false);
+
+ if (role_names.len > 0)
+ appendStringInfoString(&role_names, ", ");
+ appendStringInfoString(&role_names, quote_identifier(rolename));
+ }
+ }
+
+ if (role_names.len > 0)
+ append_ddl_option(&buf, pretty, 4, "TO %s", role_names.data);
+
+ pfree(role_names.data);
+ pfree(policy_roles);
+ }
+
+ /* USING expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polqual,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "USING (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ /* WITH CHECK expression */
+ valueDatum = heap_getattr(tuplePolicy,
+ Anum_pg_policy_polwithcheck,
+ RelationGetDescr(pgPolicyRel),
+ &attrIsNull);
+ if (!attrIsNull)
+ {
+ Datum expr;
+
+ expr = DirectFunctionCall3(pg_get_expr_ext,
+ valueDatum,
+ ObjectIdGetDatum(policyForm->polrelid),
+ BoolGetDatum(pretty));
+ append_ddl_option(&buf, pretty, 4, "WITH CHECK (%s)",
+ TextDatumGetCString(expr));
+ }
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ systable_endscan(sscan);
+ table_close(pgPolicyRel, AccessShareLock);
+ pfree(buf.data);
+
+ return statements;
+}
+
+/*
+ * pg_get_policy_ddl
+ * Return DDL to recreate a row-level security policy as a single text row.
+ */
+Datum
+pg_get_policy_ddl(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid tableID;
+ Name policyName;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (PG_ARGISNULL(0) || PG_ARGISNULL(1))
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ tableID = PG_GETARG_OID(0);
+ policyName = PG_GETARG_NAME(1);
+
+ parse_ddl_options(fcinfo, 2, opts, lengthof(opts));
+
+ statements = pg_get_policy_ddl_internal(tableID,
+ NameStr(*policyName),
+ opts[0].isset && opts[0].boolval);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..3e32c89fd6d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8615,6 +8615,14 @@
proargtypes => 'regdatabase text', proallargtypes => '{regdatabase,text}',
proargmodes => '{i,v}', proargdefaults => '{NULL}',
prosrc => 'pg_get_database_ddl' },
+{ oid => '6517', descr => 'get DDL to recreate a row-level security policy',
+ proname => 'pg_get_policy_ddl', prorows => '1', provariadic => 'text',
+ proisstrict => 'f', proretset => 't', provolatile => 's',
+ pronargdefaults => '1', prorettype => 'text',
+ proargtypes => 'regclass name text',
+ proallargtypes => '{regclass,name,text}',
+ proargmodes => '{i,i,v}', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_policy_ddl' },
{ oid => '2509',
descr => 'deparse an encoded expression with pretty-print option',
proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 3a5e82c35bd..9a20536584d 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -5195,6 +5195,199 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+ count
+-------
+ 0
+(1 row)
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+ERROR: relation "nonexistent_tbl" does not exist
+LINE 1: SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1')...
+ ^
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+ERROR: policy "nonexistent_pol" for table "regress_rls_schema.rls_tbl_1" does not exist
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+ERROR: unrecognized option: "badopt"
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+ERROR: invalid value for boolean option "pretty": maybe
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+ERROR: option "pretty" is specified more than once
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+ pg_get_policy_ddl
+-------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1 USING ((dlevel <= ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2 +
+ WHERE (rls_tbl_2.pguser = CURRENT_USER))));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1 AS RESTRICTIVE USING (((cid <> 44) AND (cid < 50)));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+ pg_get_policy_ddl
+----------------------------------------------------------------------------------------
+ CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1 USING ((dauthor = CURRENT_USER));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1 FOR SELECT USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1 FOR INSERT WITH CHECK (((cid % 2) = 1));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1 FOR UPDATE USING (((cid % 2) = 0));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+ pg_get_policy_ddl
+------------------------------------------------------------------------------------
+ CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1 FOR DELETE USING ((cid < 8));
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+(1 row)
+
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+ pg_get_policy_ddl
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1 TO regress_rls_exempt_user WITH CHECK ((cid = ( SELECT rls_tbl_2.seclv+
+ FROM rls_tbl_2)));
+(1 row)
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p1 ON regress_rls_schema.rls_tbl_1
+ USING (dlevel <= (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2
+ WHERE rls_tbl_2.pguser = CURRENT_USER)));
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p2 ON regress_rls_schema.rls_tbl_1
+ AS RESTRICTIVE
+ USING (cid <> 44 AND cid < 50);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p3 ON regress_rls_schema.rls_tbl_1
+ USING (dauthor = CURRENT_USER);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p4 ON regress_rls_schema.rls_tbl_1
+ FOR SELECT
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p5 ON regress_rls_schema.rls_tbl_1
+ FOR INSERT
+ WITH CHECK ((cid % 2) = 1);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p6 ON regress_rls_schema.rls_tbl_1
+ FOR UPDATE
+ USING ((cid % 2) = 0);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p7 ON regress_rls_schema.rls_tbl_1
+ FOR DELETE
+ USING (cid < 8);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p8 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_dave, regress_rls_alice
+ USING (true);
+(1 row)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+pg_get_policy_ddl
+CREATE POLICY rls_p9 ON regress_rls_schema.rls_tbl_1
+ TO regress_rls_exempt_user
+ WITH CHECK (cid = (( SELECT rls_tbl_2.seclv
+ FROM rls_tbl_2)));
+(1 row)
+\pset format aligned
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+--
-- Clean up objects
--
RESET SESSION AUTHORIZATION;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 6b3566271df..aca6102de71 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -2598,6 +2598,93 @@ reset rls_test.blah;
drop function rls_f(text);
drop table rls_t, test_t;
+--
+-- Test for pg_get_policy_ddl(table, policy_name, VARIADIC options) function.
+--
+CREATE TABLE rls_tbl_1 (
+ did int primary key,
+ cid int,
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON rls_tbl_1 TO public;
+CREATE TABLE rls_tbl_2 (
+ pguser name primary key,
+ seclv int
+);
+GRANT SELECT ON rls_tbl_2 TO public;
+
+-- Test PERMISSIVE and RESTRICTIVE
+CREATE POLICY rls_p1 ON rls_tbl_1 AS PERMISSIVE
+ USING (dlevel <= (SELECT seclv FROM rls_tbl_2 WHERE pguser = current_user));
+CREATE POLICY rls_p2 ON rls_tbl_1 AS RESTRICTIVE USING (cid <> 44 AND cid < 50);
+
+-- Test FOR ALL | SELECT | INSERT | UPDATE | DELETE
+CREATE POLICY rls_p3 ON rls_tbl_1 FOR ALL USING (dauthor = current_user);
+CREATE POLICY rls_p4 ON rls_tbl_1 FOR SELECT USING (cid % 2 = 0);
+CREATE POLICY rls_p5 ON rls_tbl_1 FOR INSERT WITH CHECK (cid % 2 = 1);
+CREATE POLICY rls_p6 ON rls_tbl_1 FOR UPDATE USING (cid % 2 = 0);
+CREATE POLICY rls_p7 ON rls_tbl_1 FOR DELETE USING (cid < 8);
+
+-- Test TO { role_name ... }
+CREATE POLICY rls_p8 ON rls_tbl_1 TO regress_rls_dave, regress_rls_alice USING (true);
+CREATE POLICY rls_p9 ON rls_tbl_1 TO regress_rls_exempt_user WITH CHECK (cid = (SELECT seclv FROM rls_tbl_2));
+
+-- NULL inputs should return no rows
+SELECT count(*) FROM pg_get_policy_ddl(NULL, 'rls_p1');
+SELECT count(*) FROM pg_get_policy_ddl('rls_tbl_1', NULL);
+SELECT count(*) FROM pg_get_policy_ddl(NULL, NULL);
+
+-- Table does not exist
+SELECT * FROM pg_get_policy_ddl('nonexistent_tbl', 'rls_p1');
+-- Policy does not exist
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'nonexistent_pol');
+
+-- Invalid option name
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'badopt', 'true');
+-- Invalid boolean value for option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'maybe');
+-- Duplicate option
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true', 'pretty', 'false');
+
+-- Without pretty formatting (default)
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'false');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'false');
+
+-- With pretty formatting
+\pset format unaligned
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p1', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p2', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p3', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p4', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p5', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p6', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p7', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p8', 'pretty', 'true');
+SELECT * FROM pg_get_policy_ddl('rls_tbl_1', 'rls_p9', 'pretty', 'true');
+\pset format aligned
+
+-- Clean up objects created for testing pg_get_policy_ddl function.
+DROP POLICY rls_p1 ON rls_tbl_1;
+DROP POLICY rls_p2 ON rls_tbl_1;
+DROP POLICY rls_p3 ON rls_tbl_1;
+DROP POLICY rls_p4 ON rls_tbl_1;
+DROP POLICY rls_p5 ON rls_tbl_1;
+DROP POLICY rls_p6 ON rls_tbl_1;
+DROP POLICY rls_p7 ON rls_tbl_1;
+DROP POLICY rls_p8 ON rls_tbl_1;
+DROP POLICY rls_p9 ON rls_tbl_1;
+DROP TABLE rls_tbl_1;
+DROP TABLE rls_tbl_2;
+
--
-- Clean up objects
--
--
2.51.0
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: akshay.joshi@enterprisedb.com, jian.universality@gmail.com, marcos@f10.com.br, markwkm@gmail.com, alvherre@kurilemu.de
Subject: Re: [PATCH] Add pg_get_policy_ddl() function to reconstruct CREATE POLICY statement
In-Reply-To: <CANxoLDfdZTLLJqXnnfUYG-Uw4LHBKKnB5f1XOdaQ3ZET=K1qnw@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