Access a private Amazon Redshift from a local machine via a private EC2 instance

Description

Connect to a private Amazon Redshift from an SQL client tool on a local machine via a private Amazon EC2 instance using AWS Systems Manager Session Manager port forwarding.

Diagram

Steps

EC2 instance

1. Grant Session Manager permissions to the EC2 instance IAM role by either attaching the AWS-provided default policy AmazonSSMManagedInstanceCore or adding the Session Manager permissions through a new or existing policy to the EC2 instance IAM role.
2. Verify that the EC2 instance is displayed as a managed node in AWS Systems Manager. If not, please refer to the following article. Please note that NAT Gateway or VPC endpoints can be used in order to privately access an EC2 instance and Systems Manager APIs.
3. There is no need to add inbound rules to the EC2 instance security group.
4. There is no need for an EC2 instance key pair to establish the connection from the local machine.

Redshift

1. Add the EC2 instance security group to the Amazon Redshift security group on the Redshift port.

Local machine

1. If AWS CLI is not yet configured on the local machine, use the AWS configure command for setup.
2. Start a Session Manager port forwarding session to remote host using the AWS SSM start-session.

aws ssm start-session ^
        --region region-name ^
        --target instance-id ^
        --document-name AWS-StartPortForwardingSessionToRemoteHost ^
        --parameters host="redshift-cluster-endpoint",portNumber="redshift-port",localPortNumber="local-machine-port"

Example:

aws ssm start-session ^
        --region eu-west-1 ^
        --target i-0a1b2c3456798 ^
        --document-name AWS-StartPortForwardingSessionToRemoteHost ^
        --parameters host="my-redshift-cluster.eu-west-1.redshift.amazonaws.com ",portNumber="5439",localPortNumber="5040"

3. Connect to Amazon Redshift from the SQL client tool on local machine using jdbc.

jdbc:redshift://localhost:localPortNumberValue/databasename

Example:

jdbc:redshift://localhost:5040/dev