v5-0004-Introduce-pg_create_connection-predefined-role.patch

From 30a8482ac5a0981a6ebefe6f4a24d2e3b2a01d13 Mon Sep 17 00:00:00 2001
From: Jeff Davis <jeff@j-davis.com>
Date: Tue, 2 Jan 2024 13:13:54 -0800
Subject: [PATCH v5 4/4] Introduce pg_create_connection predefined role.

In addition to pg_create_subscription, membership in this role is
necessary to create a subscription with a connection string. The
pg_create_subscription role is a member of pg_create_connection, so by
default pg_create_subscription has the same capability as before.

An administrator may revoke pg_create_connection from
pg_create_subscription, which will enable the privileges to be
separated. That will be useful in an upcoming change to introduce
CREATE SUBSCRIPTION ... SERVER, which will not use a raw connection
string, and therefore not require membership in the
pg_create_connection role.
---
 doc/src/sgml/user-manag.sgml               | 12 ++++++++++--
 src/backend/catalog/system_functions.sql   |  2 ++
 src/backend/commands/subscriptioncmds.c    | 19 ++++++++++++++++---
 src/include/catalog/pg_authid.dat          |  5 +++++
 src/test/regress/expected/subscription.out | 11 +++++++++++
 src/test/regress/sql/subscription.sql      | 14 ++++++++++++++
 6 files changed, 58 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 92a299d2d3..4f4c20ba3c 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -687,11 +687,19 @@ DROP ROLE doomed_role;
        <entry>Allow use of connection slots reserved via
        <xref linkend="guc-reserved-connections"/>.</entry>
       </row>
+      <row>
+       <entry>pg_create_connection</entry>
+       <entry>Allow users to specify a connection string directly in <link
+       linkend="sql-createsubscription"><command>CREATE
+       SUBSCRIPTION</command></link>.</entry>
+      </row>
       <row>
        <entry>pg_create_subscription</entry>
        <entry>Allow users with <literal>CREATE</literal> permission on the
-       database to issue
-       <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
+       database to issue <link
+       linkend="sql-createsubscription"><command>CREATE
+       SUBSCRIPTION</command></link>.  This role is a member of
+       <literal>pg_create_connection</literal>.</entry>
       </row>
      </tbody>
     </tgroup>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index f315fecf18..73512688de 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -781,3 +781,5 @@ GRANT pg_read_all_settings TO pg_monitor;
 GRANT pg_read_all_stats TO pg_monitor;
 
 GRANT pg_stat_scan_tables TO pg_monitor;
+
+GRANT pg_create_connection TO pg_create_subscription;
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 60287c73e7..03555d5159 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -609,9 +609,9 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
 		PreventInTransactionBlock(isTopLevel, "CREATE SUBSCRIPTION ... WITH (create_slot = true)");
 
 	/*
-	 * We don't want to allow unprivileged users to be able to trigger
-	 * attempts to access arbitrary network destinations, so require the user
-	 * to have been specifically authorized to create subscriptions.
+	 * We don't want to allow unprivileged users to utilize the resources that
+	 * a subscription requires (such as a background worker), so require the
+	 * user to have been specifically authorized to create subscriptions.
 	 */
 	if (!has_privs_of_role(owner, ROLE_PG_CREATE_SUBSCRIPTION))
 		ereport(ERROR,
@@ -697,6 +697,19 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
 	{
 		Assert(stmt->conninfo);
 
+		/*
+		 * We don't want to allow unprivileged users to be able to trigger
+		 * attempts to access arbitrary network destinations, so require the user
+		 * to have been specifically authorized to create connections.
+		 */
+		if (!has_privs_of_role(owner, ROLE_PG_CREATE_CONNECTION))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("permission denied to create subscription with a connection string"),
+					 errdetail("Only roles with privileges of the \"%s\" role may create subscriptions with CONNECTION specified.",
+							   "pg_create_connection"),
+					 errhint("Create a subscription to a foreign server by specifying SERVER instead.")));
+
 		serverid = InvalidOid;
 		umid = InvalidOid;
 		conninfo = stmt->conninfo;
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..dcfad7a0c0 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
   rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
   rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
   rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6122', oid_symbol => 'ROLE_PG_CREATE_CONNECTION',
+  rolname => 'pg_create_connection', rolsuper => 'f', rolinherit => 't',
+  rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+  rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+  rolpassword => '_null_', rolvaliduntil => '_null_' },
 
 ]
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 7b866a6fe6..2737901751 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -157,7 +157,16 @@ CREATE SERVER regress_testserver FOREIGN DATA WRAPPER pg_connection_fdw;
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver TO regress_subscription_user3;
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
 SET SESSION AUTHORIZATION regress_subscription_user3;
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+ERROR:  permission denied to create subscription with a connection string
+DETAIL:  Only roles with privileges of the "pg_create_connection" role may create subscriptions with CONNECTION specified.
+HINT:  Create a subscription to a foreign server by specifying SERVER instead.
+-- succeed - subscription to foreign server
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver PUBLICATION testpub
   WITH (slot_name = NONE, connect = false);
 WARNING:  subscription was created, but is not connected
@@ -183,6 +192,8 @@ DROP SUBSCRIPTION regress_testsub6;
 DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver;
 DROP SERVER regress_testserver;
 REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
 SET SESSION AUTHORIZATION regress_subscription_user;
 \dRs+
                                                                                                            List of subscriptions
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 95c826030b..b041a6d542 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -103,9 +103,19 @@ CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver TO regress_subscription_user3;
 
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
+
 SET SESSION AUTHORIZATION regress_subscription_user3;
+
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+
+-- succeed - subscription to foreign server
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver PUBLICATION testpub
   WITH (slot_name = NONE, connect = false);
+
 RESET SESSION AUTHORIZATION;
 
 -- test an FDW with no validator
@@ -131,6 +141,10 @@ DROP SUBSCRIPTION regress_testsub6;
 DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver;
 DROP SERVER regress_testserver;
 REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
+
 SET SESSION AUTHORIZATION regress_subscription_user;
 
 \dRs+
-- 
2.34.1