Allowing ETL access to my Redshift cluster

Connect ETL to your Redshift cluster in order to write data to it, or read data from it. Watch this short video walkthrough or read below for further instructions.

In Redshift console

1. Start by navigating to the Redshift console --> Clusters screen and clicking on the Properties tab: 

2. Scroll down to the Network and Security section. Make sure that the cluster is set with the value for Publicly Accessible to Yes. Then, click on the VPC Security Group to verify and/or modify the security rules: 

 3. In the Security Group screen, select the Inbound tab:

4. There should be rules for the IP addresses listed hereIf those rules need to be altered or don't exist, click Edit:
5. Edit any existing rules or click Add Rule to add a new rule. For each rule, select the type of database and enter the Redshift port. Then click Save:
To configure your cluster to only accept SSL encrypted connections:
First, access the parameter group and edit it to set require_ssl to true. 
thumbnail image
Then, Navigate to Config --> Workload management. If you created the cluster with a default parameter group, create a new parameter group and modify the cluster to associate to that parameter group. Click Edit on the cluster homepage, then go to Database Configurations to associate the parameter group with the current cluster.
thumbnail image   

To verify or modify the security rules of an instance on EC2-Classic (without VPC):

In your Redshift Cluster Security Group, modify a rule or add a new rule for each IP address listed here:

  • In the Connection Type dropbox, choose CIDR/IP.
  • In the CIDR/IP to Authorize field, enter the IP addresses from this list.

Create a Redshift user

  • Create a Redshift user.
  • Grant it the following permissions:
    • If you intend to only append data into a table, give the user minimal permissions required to execute the COPY command.
    • If you intend to merge data into a table, give the user minimal permissions required to execute the COPY command, create a table, and insert and update to your target tables.
    • Note that truncate requires ETL to either be the owner of the target table or have supseruser access
    • .

To define a connection in ETL to Amazon Redshift

  1. Click Connections (the lightning bolt) on the left side of the dashboard.
  2. Click New connection. thumbnail image
  3. Select Amazon Redshift from the Analytical Database category.
  4. Type a name for the connection.
  5. If you allow direct access from ETL's IP addresses, enter the hostname and port. If direct access is not allowed, read more about setting a tunnel connection here.
  6. Enter the user name and password you created for ETL to use.
  7. Enter the default database to use. If you leave it empty, the user's default database will be used.
  8. Set the region to the AWS region in which the Redshift cluster was created. If the region requires AWS Signature v4 (see list here) you may need our support team's help with allowing ETL access to read from this Amazon Redshift connection.
  9. Click Test connection. If the credentials are correct, a message that the cloud storage connection test was successful appears.
  10. Click Create connection and the connection is created and appears in the list of connections. thumbnail image