CREATE POLICY IF NOT EXISTS

From acaf26a8a89b61ab274647d99e3eadd52b75534d Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 21 Oct 2025 11:31:55 +0800
Subject: [PATCH v1 1/1] CREATE POLICY IF NOT EXISTS

discussion: https://postgr.es/m/
---
 doc/src/sgml/ref/create_policy.sgml       | 15 ++++-
 src/backend/commands/policy.c             | 79 +++++++++++++++--------
 src/backend/parser/gram.y                 | 19 +++++-
 src/include/nodes/parsenodes.h            |  1 +
 src/test/regress/expected/rowsecurity.out |  4 ++
 src/test/regress/sql/rowsecurity.sql      |  3 +
 6 files changed, 91 insertions(+), 30 deletions(-)

diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index e76c342d3da..5cd4cd87c19 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>
+CREATE POLICY [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>
     [ AS { PERMISSIVE | RESTRICTIVE } ]
     [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
     [ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC | CURRENT_ROLE | CURRENT_USER | SESSION_USER } [, ...] ]
@@ -101,6 +101,19 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
   <title>Parameters</title>
 
   <variablelist>
+
+   <varlistentry>
+    <term><literal>IF NOT EXISTS</literal></term>
+    <listitem>
+     <para>
+      Do not throw an error if the policy object with the same name already
+      exists.  A notice is issued in this case.  Note that only the name of
+      the policy object is considered here, not the details of its
+      definition.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><replaceable class="parameter">name</replaceable></term>
     <listitem>
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index 83056960fe4..47129270bf5 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -629,6 +629,57 @@ CreatePolicy(CreatePolicyStmt *stmt)
 										RangeVarCallbackForPolicy,
 										stmt);
 
+	/* Open pg_policy catalog */
+	pg_policy_rel = table_open(PolicyRelationId, RowExclusiveLock);
+
+	/* Set key - policy's relation id. */
+	ScanKeyInit(&skey[0],
+				Anum_pg_policy_polrelid,
+				BTEqualStrategyNumber, F_OIDEQ,
+				ObjectIdGetDatum(table_id));
+
+	/* Set key - policy's name. */
+	ScanKeyInit(&skey[1],
+				Anum_pg_policy_polname,
+				BTEqualStrategyNumber, F_NAMEEQ,
+				CStringGetDatum(stmt->policy_name));
+
+	sscan = systable_beginscan(pg_policy_rel,
+							   PolicyPolrelidPolnameIndexId, true, NULL, 2,
+							   skey);
+
+	policy_tuple = systable_getnext(sscan);
+
+	/* Complain or bail out if the policy name already exists for the table */
+	if (HeapTupleIsValid(policy_tuple))
+	{
+		/*
+		 * If the policy already exists and the user specified "IF NOT EXISTS",
+		 * bail out with a NOTICE.
+		 */
+		if (stmt->if_not_exists)
+		{
+			/*
+			 * Since policy objects aren't members of extensions no need for
+			 * checkMembershipInCurrentExtension here.
+			 */
+			ereport(NOTICE,
+					errcode(ERRCODE_DUPLICATE_OBJECT),
+					errmsg("policy \"%s\" for table \"%s\" already exists",
+						   stmt->policy_name, get_rel_name(table_id)));
+
+			systable_endscan(sscan);
+			table_close(pg_policy_rel, RowExclusiveLock);
+
+			return InvalidObjectAddress;
+		}
+		else
+			ereport(ERROR,
+					errcode(ERRCODE_DUPLICATE_OBJECT),
+					errmsg("policy \"%s\" for table \"%s\" already exists",
+						   stmt->policy_name, get_rel_name(table_id)));
+	}
+
 	/* Open target_table to build quals. No additional lock is necessary. */
 	target_table = relation_open(table_id, NoLock);
 
@@ -658,34 +709,6 @@ CreatePolicy(CreatePolicyStmt *stmt)
 	assign_expr_collations(qual_pstate, qual);
 	assign_expr_collations(with_check_pstate, with_check_qual);
 
-	/* Open pg_policy catalog */
-	pg_policy_rel = table_open(PolicyRelationId, RowExclusiveLock);
-
-	/* Set key - policy's relation id. */
-	ScanKeyInit(&skey[0],
-				Anum_pg_policy_polrelid,
-				BTEqualStrategyNumber, F_OIDEQ,
-				ObjectIdGetDatum(table_id));
-
-	/* Set key - policy's name. */
-	ScanKeyInit(&skey[1],
-				Anum_pg_policy_polname,
-				BTEqualStrategyNumber, F_NAMEEQ,
-				CStringGetDatum(stmt->policy_name));
-
-	sscan = systable_beginscan(pg_policy_rel,
-							   PolicyPolrelidPolnameIndexId, true, NULL, 2,
-							   skey);
-
-	policy_tuple = systable_getnext(sscan);
-
-	/* Complain if the policy name already exists for the table */
-	if (HeapTupleIsValid(policy_tuple))
-		ereport(ERROR,
-				(errcode(ERRCODE_DUPLICATE_OBJECT),
-				 errmsg("policy \"%s\" for table \"%s\" already exists",
-						stmt->policy_name, RelationGetRelationName(target_table))));
-
 	policy_id = GetNewOidWithIndex(pg_policy_rel, PolicyOidIndexId,
 								   Anum_pg_policy_oid);
 	values[Anum_pg_policy_oid - 1] = ObjectIdGetDatum(policy_id);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ef8498ddb23..15de9f65490 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5932,7 +5932,7 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi
 /*****************************************************************************
  *
  *		QUERIES:
- *				CREATE POLICY name ON table
+ *				CREATE POLICY [IF NOT EXISTS] name ON table
  *					[AS { PERMISSIVE | RESTRICTIVE } ]
  *					[FOR { SELECT | INSERT | UPDATE | DELETE } ]
  *					[TO role, ...]
@@ -5950,6 +5950,7 @@ CreatePolicyStmt:
 					CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
 
 					n->policy_name = $3;
+					n->if_not_exists = false;
 					n->table = $5;
 					n->permissive = $6;
 					n->cmd_name = $7;
@@ -5958,6 +5959,22 @@ CreatePolicyStmt:
 					n->with_check = $10;
 					$$ = (Node *) n;
 				}
+		|	CREATE POLICY IF_P NOT EXISTS name ON qualified_name RowSecurityDefaultPermissive
+				RowSecurityDefaultForCmd RowSecurityDefaultToRole
+				RowSecurityOptionalExpr RowSecurityOptionalWithCheck
+				{
+					CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
+
+					n->policy_name = $6;
+					n->if_not_exists = true;
+					n->table = $8;
+					n->permissive = $9;
+					n->cmd_name = $10;
+					n->roles = $11;
+					n->qual = $12;
+					n->with_check = $13;
+					$$ = (Node *) n;
+				}
 		;
 
 AlterPolicyStmt:
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4f9b8e3e381..e4629726788 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3066,6 +3066,7 @@ typedef struct CreatePolicyStmt
 {
 	NodeTag		type;
 	char	   *policy_name;	/* Policy's name */
+	bool		if_not_exists;	/* just do nothing if it already exists? */
 	RangeVar   *table;			/* the table name the policy applies to */
 	char	   *cmd_name;		/* the command name the policy applies to */
 	bool		permissive;		/* restrictive or permissive policy */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 5a172c5d91c..5bd5987b140 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -76,6 +76,10 @@ ALTER TABLE document ENABLE ROW LEVEL SECURITY;
 -- user's security level must be higher than or equal to document's
 CREATE POLICY p1 ON document AS PERMISSIVE
     USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+CREATE POLICY p1 ON document AS PERMISSIVE USING (true); --error
+ERROR:  policy "p1" for table "document" already exists
+CREATE POLICY IF NOT EXISTS p1 ON document AS PERMISSIVE USING (true); --notice, no-op
+NOTICE:  policy "p1" for table "document" already exists
 -- try to create a policy of bogus type
 CREATE POLICY p1 ON document AS UGLY
     USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 21ac0ca51ee..52109aad32c 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -92,6 +92,9 @@ ALTER TABLE document ENABLE ROW LEVEL SECURITY;
 CREATE POLICY p1 ON document AS PERMISSIVE
     USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
 
+CREATE POLICY p1 ON document AS PERMISSIVE USING (true); --error
+CREATE POLICY IF NOT EXISTS p1 ON document AS PERMISSIVE USING (true); --notice, no-op
+
 -- try to create a policy of bogus type
 CREATE POLICY p1 ON document AS UGLY
     USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
-- 
2.34.1