How to create and configure a proxy between MuleSoft CloudHub and Amazon AWS Redshift using an Amazon AWS EC2 instance.

cloudhub-redshift-proxy.md

Overview

Below are the steps needed to create and configure a proxy between MuleSoft CloudHub and Amazon AWS Redshift using an Amazon AWS EC2 instance.

The Problem

EC2 and Redshift instances are configured to support jumbo frames (MTU for ethernet interfaces is 9001). However, some routers between endpoints have a standard Ethernet MTU size (1500), which causes an inability to communicate with announced TCP MSS size (8961). The reason for this issue is that the PATH MTU discovery process relies on ICMP, specifically Type 3 Code 4 / Fragmentation Needed, and currently on Redshift ALL ICMP traffic is denied (regardless of Security Group configuration).

MuleSoft CloudHub uses the standard ethernet MTU (1500), and cannot connect to a RedShift cluster by default. The steps below document how to create a lightweight IP proxy using an EC2 instance.

Configuration Details

1. Create an AWS instance in the same Availability Zone (AZ) as the Redshift cluster using the following criteria:

   1. AMI: Ubuntu Server 14.04 LTS (HVM), SSD Volume Type - ami-d05e75b8 (or similar)
   2. Instance Type: t2.micro
      • initial performance tests have shown this to be adequate as the proxy is not CPU/RAM intensive.
   3. Instance Details: accept default or modify depending on VPC configuration
   4. Tag Instance: cloudhub-redshift-proxy
   5. Configure Security Group:
      1. Restrict SSH access to trusted IP Ranges
      2. Add Custom TCP Rule(s) for each Static CloudHub IP which will access the Redshift cluster
         • Protocol: TCP
         • Port Range: 5439 (default Redshift port)
         • Custom IP (using CIDR notation): x.x.x.x/32 (e.g. 54.127.64.101/32)

2. Launch instance, and choose an existing SSH key pair that will allow you to SSH to the instance.

3. Disable Source/Destination Check

   1. Select the instance from the EC2 Instances list
   2. Select Actions > Networking > Change Source/Dest. Check
   3. Click the Yes, Disable button

4. Once instance is launched, connect to the instance using the Public DNS/IP:

   ssh ubuntu@server.eu-west-1.compute.amazonaws.com

ssh ubuntu@54.152.137.105

1. Enable IP packet forwarding

   1. Open the /etc/sysctl.conf in vi or vim:

      # sudo vi /etc/sysctl.conf

   2. Uncomment the following line:

      net.ipv4.ip_forward = 1

   3. Save the file

   4. Apply the changes with the following command:

      # sudo sysctl -p

2. Apply iptables rules for TCP MSS adjustment (assuming using the default Redshift port 5439)

   1. Enter the following two commands:

   sudo iptables -A PREROUTING -t mangle -p tcp --sport 5439 --tcp-flags SYN,RST SYN -j TCPMSS --set-mss 1460

   sudo iptables -A PREROUTING -t mangle -p tcp --dport 5439 --tcp-flags SYN,RST SYN -j TCPMSS --set-mss 1460

3. Make NAT translation from “proxy” to a Redshift Cluster IP (RS_IP is the cluster IP address, LOCAL_IP is IP address for eth0 interface of “proxy” host)

   1. You will need the private IP of the EC2 Proxy instance. You can find this by looking at the AWS instance details or by typing ifconfig at the command line within your SSH session and look at the eth0 device.

   2. You will need the IP of the Redshift cluster as well.

   3. Enter the following command replacing RS_IP with the cluster IP, and LOCAL_IP with the EC2 private/local IP for eth0:

      sudo iptables -t nat -A PREROUTING -p tcp -d LOCAL_IP --dport 5439 -j DNAT --to-destination RS_IP

      Example:

      sudo iptables -t nat -A PREROUTING -p tcp -d 171.25.2.35 --dport 5439 -j DNAT --to-destination 54.102.36.165

   4. Enter the following command:

      sudo iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE

4. Install iptables-persistent package. By default the IPTABLES changes will be lost if/when the server restarts. This could lead to undesirable effects.

   1. Enter the followig two commands:

   sudo apt-get update

   sudo apt-get install iptables-persistent

   1. On screen you will the get the choices for IPV4 and IPV6 rule set,to be installed.The screen will come during installation of iptables-persistent Select Yes by hitting spacebar.
   2. The installation will take a few seconds/minutes. After installation of iptables-persistent get completed. Start the service with the following command:

   service iptables-persistent start

5. Logout of the SSH session. The proxy is configured.

Making Changes

Once your iptables rules are in place, you may have to make changes (e.g. destination IP changed). In this event you can run the following command to view the existing rules:

iptables -t nat -L --line-numbers (You may need to run this as sudo)

This will return results similar to:

Chain PREROUTING (policy ACCEPT)
num  target     prot opt source               destination
1    DNAT       tcp  --  anywhere             ip-172-0-0-0.us-east-1.compute.internal  tcp dpt:5439 to:192.168.1.1

Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination

Chain POSTROUTING (policy ACCEPT)
num  target     prot opt source               destination
1    MASQUERADE  all  --  anywhere             anywhere

Depending on how many additional rules exist, you will need to locate the PREROUTING rule for your Redshift instance. First you will need to remove the old rule. You do this by specifying the table (-t), and then delete (-D) followed by the chain (PREROUTING) and the rulenumber (1; or whichever rule matches your configuratiion).

Run the following command:

iptables -t nat -D PREROUTING 1

Once completed, you can repeat the step from above to add the new PREROUTING rule for the new destination IP of your Redshift instance.

sudo iptables -t nat -A PREROUTING -p tcp -d LOCAL_IP --dport 5439 -j DNAT --to-destination RS_IP