v9-0003-Introduce-pg_create_connection-predefined-role.patch

From bc3cbaac821d10dc33f2b64843a83c1af13ecbe2 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 v9 3/3] 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 (CREATE
SUBSCRIPTION ... CONNECTION '...'). 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 is, permit CREATE SUBSCRIPTION ... SERVER, but not
permit CREATE SUBSCRIPTION ... CONNECTION.

Discussion: https://postgr.es/m/61831790a0a937038f78ce09f8dd4cef7de7456a.camel@j-davis.com
---
 .../postgres_fdw/expected/postgres_fdw.out    |  2 +-
 contrib/postgres_fdw/sql/postgres_fdw.sql     |  2 +-
 contrib/postgres_fdw/t/010_subscription.pl    |  2 +-
 doc/src/sgml/ref/alter_server.sgml            | 14 ++++++
 doc/src/sgml/ref/alter_subscription.sgml      |  4 +-
 doc/src/sgml/ref/create_server.sgml           | 14 ++++++
 doc/src/sgml/ref/create_subscription.sgml     |  4 +-
 doc/src/sgml/user-manag.sgml                  | 12 ++++-
 src/backend/catalog/system_functions.sql      |  2 +
 src/backend/commands/foreigncmds.c            | 31 ++++++++++++
 src/backend/commands/subscriptioncmds.c       | 31 ++++++++++--
 src/backend/foreign/foreign.c                 |  1 +
 src/backend/parser/gram.y                     | 30 ++++++++++--
 src/include/catalog/pg_authid.dat             |  5 ++
 src/include/catalog/pg_foreign_server.h       |  1 +
 src/include/foreign/foreign.h                 |  1 +
 src/include/nodes/parsenodes.h                |  3 ++
 src/test/regress/expected/subscription.out    | 47 +++++++++++++++++--
 src/test/regress/sql/subscription.sql         | 47 +++++++++++++++++--
 src/test/subscription/t/001_rep_changes.pl    |  2 +-
 20 files changed, 234 insertions(+), 21 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ecd0230738..eec57c0aa6 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2,7 +2,7 @@
 -- create FDW objects
 -- ===================================================================
 CREATE EXTENSION postgres_fdw;
-CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
+CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION;
 DO $d$
     BEGIN
         EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 1c9c12703f..c35e974a94 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4,7 +4,7 @@
 
 CREATE EXTENSION postgres_fdw;
 
-CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
+CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION;
 DO $d$
     BEGIN
         EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
diff --git a/contrib/postgres_fdw/t/010_subscription.pl b/contrib/postgres_fdw/t/010_subscription.pl
index a39e8fdbba..3ae2b6da4a 100644
--- a/contrib/postgres_fdw/t/010_subscription.pl
+++ b/contrib/postgres_fdw/t/010_subscription.pl
@@ -38,7 +38,7 @@ $node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub FOR TABLE tab
 my $publisher_host = $node_publisher->host;
 my $publisher_port = $node_publisher->port;
 $node_subscriber->safe_psql('postgres',
-	"CREATE SERVER tap_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+	"CREATE SERVER tap_server FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
 );
 
 $node_subscriber->safe_psql('postgres',
diff --git a/doc/src/sgml/ref/alter_server.sgml b/doc/src/sgml/ref/alter_server.sgml
index 467bf85589..1a4227e548 100644
--- a/doc/src/sgml/ref/alter_server.sgml
+++ b/doc/src/sgml/ref/alter_server.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 ALTER SERVER <replaceable class="parameter">name</replaceable> [ VERSION '<replaceable class="parameter">new_version</replaceable>' ]
+    [ { FOR | NO } SUBSCRIPTION ]
     [ OPTIONS ( [ ADD | SET | DROP ] <replaceable class="parameter">option</replaceable> ['<replaceable class="parameter">value</replaceable>'] [, ... ] ) ]
 ALTER SERVER <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
 ALTER SERVER <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -70,6 +71,19 @@ ALTER SERVER <replaceable class="parameter">name</replaceable> RENAME TO <replac
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>{ FOR | NO } SUBSCRIPTION</literal></term>
+    <listitem>
+     <para>
+      This clause specifies whether the foreign server may be used for a
+      subscription (see <xref linkend="sql-createsubscription"/>). The default
+      is <literal>NO SUBSCRIPTION</literal>. Only members of the role
+      <literal>pg_create_connection</literal> may specify <literal>FOR
+      SUBSCRIPTION</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable class="parameter">option</replaceable> ['<replaceable class="parameter">value</replaceable>'] [, ... ] )</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 6d219145a9..513f54c4b4 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -101,7 +101,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
      <para>
       This clause replaces the foreign server or connection string originally
       set by <xref linkend="sql-createsubscription"/> with the foreign server
-      <replaceable>servername</replaceable>.
+      <replaceable>servername</replaceable>. The foreign server must have been
+      created with <literal>FOR SUBSCRIPTION</literal> (see <xref
+      linkend="sql-createserver"/>).
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml
index 05f4019453..913cebabf2 100644
--- a/doc/src/sgml/ref/create_server.sgml
+++ b/doc/src/sgml/ref/create_server.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
 <synopsis>
 CREATE SERVER [ IF NOT EXISTS ] <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
     FOREIGN DATA WRAPPER <replaceable class="parameter">fdw_name</replaceable>
+    [ { FOR | NO } SUBSCRIPTION ]
     [ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ]
 </synopsis>
  </refsynopsisdiv>
@@ -104,6 +105,19 @@ CREATE SERVER [ IF NOT EXISTS ] <replaceable class="parameter">server_name</repl
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>{ FOR | NO } SUBSCRIPTION</literal></term>
+    <listitem>
+     <para>
+      This clause specifies whether the foreign server may be used for a
+      subscription (see <xref linkend="sql-createsubscription"/>). The default
+      is <literal>NO SUBSCRIPTION</literal>. Only members of the role
+      <literal>pg_create_connection</literal> may specify <literal>FOR
+      SUBSCRIPTION</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] )</literal></term>
     <listitem>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index 24538baf98..f80a027ddc 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -81,7 +81,9 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
     <term><literal>SERVER <replaceable class="parameter">servername</replaceable></literal></term>
     <listitem>
      <para>
-      A foreign server to use for the connection.
+      A foreign server to use for the connection. The foreign server must have
+      been created with <literal>FOR SUBSCRIPTION</literal> (see <xref
+      linkend="sql-createserver"/>).
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 1c011ac62b..da1a37e60b 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/foreigncmds.c b/src/backend/commands/foreigncmds.c
index cf61bbac1f..f76689b8a7 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -21,6 +21,7 @@
 #include "catalog/dependency.h"
 #include "catalog/indexing.h"
 #include "catalog/objectaccess.h"
+#include "catalog/pg_authid_d.h"
 #include "catalog/pg_foreign_data_wrapper.h"
 #include "catalog/pg_foreign_server.h"
 #include "catalog/pg_foreign_table.h"
@@ -923,6 +924,18 @@ CreateForeignServer(CreateForeignServerStmt *stmt)
 	else
 		nulls[Anum_pg_foreign_server_srvversion - 1] = true;
 
+	if (stmt->forsubscription)
+	{
+		if (!has_privs_of_role(ownerId, ROLE_PG_CREATE_CONNECTION))
+			ereport(ERROR,
+					(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+					 errmsg("permission denied to create server for subscription"),
+					 errdetail("Only roles with privileges of the \"%s\" role may create foreign servers with FOR SUBSCRIPTION specified.",
+							   "pg_create_subscription")));
+
+		values[Anum_pg_foreign_server_srvforsubscription - 1] = true;
+	}
+
 	/* Start with a blank acl */
 	nulls[Anum_pg_foreign_server_srvacl - 1] = true;
 
@@ -979,6 +992,7 @@ AlterForeignServer(AlterForeignServerStmt *stmt)
 	bool		repl_null[Natts_pg_foreign_server];
 	bool		repl_repl[Natts_pg_foreign_server];
 	Oid			srvId;
+	bool		forsubscription;
 	Form_pg_foreign_server srvForm;
 	ObjectAddress address;
 
@@ -1020,6 +1034,23 @@ AlterForeignServer(AlterForeignServerStmt *stmt)
 		repl_repl[Anum_pg_foreign_server_srvversion - 1] = true;
 	}
 
+	if (stmt->has_forsubscription)
+	{
+		repl_val[Anum_pg_foreign_server_srvforsubscription - 1] = stmt->forsubscription;
+		repl_repl[Anum_pg_foreign_server_srvforsubscription - 1] = true;
+		forsubscription = stmt->forsubscription;
+	}
+	else
+		forsubscription = srvForm->srvforsubscription;
+
+	if (forsubscription &&
+		!has_privs_of_role(srvForm->srvowner, ROLE_PG_CREATE_CONNECTION))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to alter server for subscription"),
+				 errdetail("Only roles with privileges of the \"%s\" role may alter foreign servers with FOR SUBSCRIPTION specified.",
+						   "pg_create_connection")));
+
 	if (stmt->options)
 	{
 		ForeignDataWrapper *fdw = GetForeignDataWrapper(srvForm->srvfdw);
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 983b5d17fe..1e0c2e5b99 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -608,9 +608,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,
@@ -685,6 +685,12 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
 		if (aclresult != ACLCHECK_OK)
 			aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER, server->servername);
 
+		if (!server->forsubscription)
+			ereport(ERROR,
+					(errmsg("foreign server \"%s\" not usable for subscription",
+							server->servername),
+					 errhint("Specify FOR SUBSCRIPTION when creating the foreign server.")));
+
 		/* make sure a user mapping exists */
 		GetUserMapping(owner, server->serverid);
 
@@ -695,6 +701,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;
 		conninfo = stmt->conninfo;
 	}
@@ -1334,6 +1353,12 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
 									GetUserNameFromId(form->subowner, false),
 									ForeignServerName(new_server->serverid))));
 
+				if (!new_server->forsubscription)
+					ereport(ERROR,
+							(errmsg("foreign server \"%s\" not usable for subscription",
+									new_server->servername),
+							 errhint("Specify FOR SUBSCRIPTION when creating the foreign server.")));
+
 				/* make sure a user mapping exists */
 				GetUserMapping(form->subowner, new_server->serverid);
 
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index db2cf6780d..8606d57b39 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -148,6 +148,7 @@ GetForeignServerExtended(Oid serverid, bits16 flags)
 	server->servername = pstrdup(NameStr(serverform->srvname));
 	server->owner = serverform->srvowner;
 	server->fdwid = serverform->srvfdw;
+	server->forsubscription = serverform->srvforsubscription;
 
 	/* Extract server type */
 	datum = SysCacheGetAttr(FOREIGNSERVEROID,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c27e0b8b5d..3abcebd8b3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -366,6 +366,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 
 %type <str>		opt_type
 %type <str>		foreign_server_version opt_foreign_server_version
+%type <boolean>	for_subscription opt_for_subscription
 %type <str>		opt_in_database
 
 %type <str>		parameter_name
@@ -5397,7 +5398,7 @@ generic_option_arg:
  *****************************************************************************/
 
 CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
-						 FOREIGN DATA_P WRAPPER name create_generic_options
+						 FOREIGN DATA_P WRAPPER name opt_for_subscription create_generic_options
 				{
 					CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
 
@@ -5405,12 +5406,13 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
 					n->servertype = $4;
 					n->version = $5;
 					n->fdwname = $9;
-					n->options = $10;
+					n->forsubscription = $10;
+					n->options = $11;
 					n->if_not_exists = false;
 					$$ = (Node *) n;
 				}
 				| CREATE SERVER IF_P NOT EXISTS name opt_type opt_foreign_server_version
-						 FOREIGN DATA_P WRAPPER name create_generic_options
+						 FOREIGN DATA_P WRAPPER name opt_for_subscription create_generic_options
 				{
 					CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
 
@@ -5418,7 +5420,8 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
 					n->servertype = $7;
 					n->version = $8;
 					n->fdwname = $12;
-					n->options = $13;
+					n->forsubscription = $13;
+					n->options = $14;
 					n->if_not_exists = true;
 					$$ = (Node *) n;
 				}
@@ -5440,6 +5443,16 @@ opt_foreign_server_version:
 			| /*EMPTY*/				{ $$ = NULL; }
 		;
 
+for_subscription:
+			FOR SUBSCRIPTION		{ $$ = true; }
+			| NO SUBSCRIPTION		{ $$ = false; }
+		;
+
+opt_for_subscription:
+			for_subscription		{ $$ = $1; }
+			| /*EMPTY*/				{ $$ = false; }
+		;
+
 /*****************************************************************************
  *
  *		QUERY :
@@ -5457,6 +5470,15 @@ AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o
 					n->has_version = true;
 					$$ = (Node *) n;
 				}
+			| ALTER SERVER name for_subscription
+				{
+					AlterForeignServerStmt *n = makeNode(AlterForeignServerStmt);
+
+					n->servername = $3;
+					n->forsubscription = $4;
+					n->has_forsubscription = true;
+					$$ = (Node *) n;
+				}
 			| ALTER SERVER name foreign_server_version
 				{
 					AlterForeignServerStmt *n = makeNode(AlterForeignServerStmt);
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/include/catalog/pg_foreign_server.h b/src/include/catalog/pg_foreign_server.h
index a4b81936b0..6736af24f5 100644
--- a/src/include/catalog/pg_foreign_server.h
+++ b/src/include/catalog/pg_foreign_server.h
@@ -31,6 +31,7 @@ CATALOG(pg_foreign_server,1417,ForeignServerRelationId)
 	NameData	srvname;		/* foreign server name */
 	Oid			srvowner BKI_LOOKUP(pg_authid); /* server owner */
 	Oid			srvfdw BKI_LOOKUP(pg_foreign_data_wrapper); /* server FDW */
+	bool		srvforsubscription BKI_DEFAULT(f); /* usable for subscription */
 
 #ifdef CATALOG_VARLEN			/* variable-length fields start here */
 	text		srvtype;
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index a2f04ce9af..e1d93c26ba 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -36,6 +36,7 @@ typedef struct ForeignServer
 	Oid			serverid;		/* server Oid */
 	Oid			fdwid;			/* foreign-data wrapper */
 	Oid			owner;			/* server owner user Oid */
+	bool		forsubscription;	/* usable for a subscription */
 	char	   *servername;		/* name of the server */
 	char	   *servertype;		/* server type, optional */
 	char	   *serverversion;	/* server version, optional */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6d6b242cec..00547bbd88 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2726,6 +2726,7 @@ typedef struct CreateForeignServerStmt
 	char	   *version;		/* optional server version */
 	char	   *fdwname;		/* FDW name */
 	bool		if_not_exists;	/* just do nothing if it already exists? */
+	bool		forsubscription;	/* usable for subscription */
 	List	   *options;		/* generic options to server */
 } CreateForeignServerStmt;
 
@@ -2734,8 +2735,10 @@ typedef struct AlterForeignServerStmt
 	NodeTag		type;
 	char	   *servername;		/* server name */
 	char	   *version;		/* optional server version */
+	bool		forsubscription;	/* usable for subscription */
 	List	   *options;		/* generic options to server */
 	bool		has_version;	/* version specified */
+	bool		has_forsubscription; /* [FOR|NO] SUBSCRIPTION specified */
 } AlterForeignServerStmt;
 
 /* ----------------------
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index b0a1a3cc26..5bd812b393 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -153,19 +153,58 @@ HINT:  To initiate replication, you must manually create the replication slot, e
 DROP SUBSCRIPTION regress_testsub6;
 -- test using a server object instead of connection string
 RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_connection_role;
 CREATE FOREIGN DATA WRAPPER regress_connection_fdw
   VALIDATOR pg_connection_validator;
-CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER regress_connection_fdw;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER regress_connection_fdw
+  FOR SUBSCRIPTION;
 CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER regress_connection_fdw;
+ALTER SERVER regress_testserver1 OWNER TO regress_connection_role;
+ALTER SERVER regress_testserver2 OWNER TO regress_connection_role;
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
   OPTIONS (password 'secret');
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver2 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.
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
-  WITH (slot_name = NONE, connect = false); -- fails
+  WITH (slot_name = NONE, connect = false); -- fail - no USAGE
 ERROR:  permission denied for foreign server regress_testserver1
+RESET SESSION AUTHORIZATION;
+GRANT USAGE ON FOREIGN SERVER regress_testserver1 TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
+  WITH (slot_name = NONE, connect = false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver2; -- fail - not FOR SUBSCRIPTION
+ERROR:  foreign server "regress_testserver2" not usable for subscription
+HINT:  Specify FOR SUBSCRIPTION when creating the foreign server.
+DROP SUBSCRIPTION regress_testsub6;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
+  WITH (slot_name = NONE, connect = false); -- fail - not FOR SUBSCRIPTION
+ERROR:  foreign server "regress_testserver2" not usable for subscription
+HINT:  Specify FOR SUBSCRIPTION when creating the foreign server.
+RESET SESSION AUTHORIZATION;
+REVOKE USAGE ON FOREIGN SERVER regress_testserver1 FROM regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION; -- fails - need pg_create_connection
+ERROR:  permission denied to alter server for subscription
+DETAIL:  Only roles with privileges of the "pg_create_connection" role may alter foreign servers with FOR SUBSCRIPTION specified.
+RESET SESSION AUTHORIZATION;
+GRANT pg_create_connection TO regress_connection_role;
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION;
+SET SESSION AUTHORIZATION regress_subscription_user3;
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
   WITH (slot_name = NONE, connect = false);
 WARNING:  subscription was created, but is not connected
@@ -180,7 +219,7 @@ DROP SERVER regress_testserver2;
 -- test an FDW with no validator
 CREATE FOREIGN DATA WRAPPER regress_fdw;
 CREATE SERVER regress_testserver3 FOREIGN DATA WRAPPER regress_fdw
-  OPTIONS (abc 'xyz');
+  FOR SUBSCRIPTION OPTIONS (abc 'xyz');
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver3 TO regress_subscription_user3;
@@ -196,6 +235,8 @@ DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
 DROP SERVER regress_testserver1;
 DROP FOREIGN DATA WRAPPER regress_connection_fdw;
 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 4d44f141b7..068a8f8c47 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -98,19 +98,56 @@ DROP SUBSCRIPTION regress_testsub6;
 -- test using a server object instead of connection string
 
 RESET SESSION AUTHORIZATION;
+CREATE ROLE regress_connection_role;
 CREATE FOREIGN DATA WRAPPER regress_connection_fdw
   VALIDATOR pg_connection_validator;
-CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER regress_connection_fdw;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER regress_connection_fdw
+  FOR SUBSCRIPTION;
 CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER regress_connection_fdw;
+ALTER SERVER regress_testserver1 OWNER TO regress_connection_role;
+ALTER SERVER regress_testserver2 OWNER TO regress_connection_role;
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
   OPTIONS (password 'secret');
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver2 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);
+
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
+  WITH (slot_name = NONE, connect = false); -- fail - no USAGE
+
+RESET SESSION AUTHORIZATION;
+GRANT USAGE ON FOREIGN SERVER regress_testserver1 TO regress_subscription_user3;
 SET SESSION AUTHORIZATION regress_subscription_user3;
+
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
-  WITH (slot_name = NONE, connect = false); -- fails
+  WITH (slot_name = NONE, connect = false);
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver2; -- fail - not FOR SUBSCRIPTION
+DROP SUBSCRIPTION regress_testsub6;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
+  WITH (slot_name = NONE, connect = false); -- fail - not FOR SUBSCRIPTION
+
+RESET SESSION AUTHORIZATION;
+REVOKE USAGE ON FOREIGN SERVER regress_testserver1 FROM regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION; -- fails - need pg_create_connection
+RESET SESSION AUTHORIZATION;
+GRANT pg_create_connection TO regress_connection_role;
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION;
+
+SET SESSION AUTHORIZATION regress_subscription_user3;
+
 CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
   WITH (slot_name = NONE, connect = false);
 RESET SESSION AUTHORIZATION;
@@ -124,7 +161,7 @@ DROP SERVER regress_testserver2;
 -- test an FDW with no validator
 CREATE FOREIGN DATA WRAPPER regress_fdw;
 CREATE SERVER regress_testserver3 FOREIGN DATA WRAPPER regress_fdw
-  OPTIONS (abc 'xyz');
+  FOR SUBSCRIPTION OPTIONS (abc 'xyz');
 CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3
   OPTIONS (password 'secret');
 GRANT USAGE ON FOREIGN SERVER regress_testserver3 TO regress_subscription_user3;
@@ -144,6 +181,10 @@ DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
 DROP SERVER regress_testserver1;
 DROP FOREIGN DATA WRAPPER regress_connection_fdw;
 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+
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index 8653423d08..81861f77e1 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -119,7 +119,7 @@ $node_subscriber->safe_psql('postgres',
 	"CREATE FOREIGN DATA WRAPPER test_connection_fdw VALIDATOR pg_connection_validator"
 );
 $node_subscriber->safe_psql('postgres',
-	"CREATE SERVER tap_sub2_server FOREIGN DATA WRAPPER test_connection_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+	"CREATE SERVER tap_sub2_server FOREIGN DATA WRAPPER test_connection_fdw FOR SUBSCRIPTION OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
 );
 
 $node_subscriber->safe_psql('postgres',
-- 
2.34.1