hi. People have complained about the lack of CREATE POLICY IF NOT EXISTS syntax in [1]. The attached patch adds support for syntax "CREATE POLICY IF NOT EXISTS".
[1] https://postgr.es/m/cwxp265mb500957dd1918490cd4ab439ef7...@cwxp265mb5009.gbrp265.prod.outlook.com
From acaf26a8a89b61ab274647d99e3eadd52b75534d Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> 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
