AWS - Redshift Integration Setup

The Redshift integration with Indicative is available for Enterprise customers only. If interested, please contact us. You are required to grant Redshift access to Indicative by creating a standalone VPC Security Group, attaching the Security Group to your Redshift Cluster, and creating a read-only Redshift account for Indicative to use.

In order to perform the following steps you must have administrative access to the AWS Console as well as your Redshift database.

Create the VPC Security Group

The first step is to grant access to your Redshift cluster when accessed from Indicative's IP addresses. This involves creating a new Security Group in your VPC.

1. Go to https://console.aws.amazon.com/vpc/home

2. Click "Security Groups" on the left side

3. Click "Create Security Group"

4. Fill out Name tag, Group name, and Description like below.  Make sure you choose the VPC that also includes your Redshift cluster. Click “Yes, Create” to continue.  We recommend using the values below.

r1.png


5. Add Indicative's IP addresses to the newly created Security Group

a. Select your newly created Security Group

r2.png

b. Click the Inbound Rules tab

c. Click the Edit button

d. Add the following three rules to allow access to your Redshift cluster to Indicative's IPs:

  • Type: Redshift; Source: 54.227.242.108/32
  • Type: Redshift; Source: 104.196.66.86/32
  • Type: Redshift; Source: 108.30.103.45/32

Attach the Security Group to Your Redshift cluster

1. Go to https://console.aws.amazon.com/redshift

2. Click the name of your Redshift cluster

r3.png

3. Go to Cluster > Modify

r4.png

4. Select the Indicative security group, in addition to your existing security groups. Also, set "Publicly accessible" to "Yes".

r5.png

5. When complete, your cluster status should look like this:

r6.png

Create a Redshift User for Indicative

1. Connect to your Redshift cluster using your existing administrator username and password

r7.png

2. Create a new user for Indicative

a. In the following SQL statement, replace ‘INSERT_PASSWORD_HERE’ with a strong password and execute the SQL command.

CREATE USER indicative WITH PASSWORD 'INSERT_PASSWORD_HERE'; 

3. Grant access to the Indicative user. Replace placeholder values as necessary

a. For your Redshift schema, run:

GRANT USAGE ON SCHEMA YOUR_SCHEMA_NAME TO indicative;

b. For the tables in your schema you'd like Indicative to access run:

GRANT select ON TABLE YOUR_SCHEMA_NAME.YOUR_TABLE_NAME TO indicative;

c. Alternatively to grant access to all tables in a schema, run:

GRANT select ON ALL TABLES IN SCHEMA YOUR_SCHEMA_NAME TO indicative;

d. You can test the user and grants:

i. To test connecting using the Indicative user, run

psql --host YOUR_HOST -p 5439 -U indicative YOUR_DATABASE

ii. To list all schemas, run

\dn

iii. To list all tables in a given schema, run

\dt YOUR_SCHEMA.*

iv. To verify that Indicative can query your tables, run

SELECT count(*) FROM YOUR_SCHEMA.YOUR_TABLE;

4. Share the following information with Indicative:

JDBC URL: jdbc:redshift://YOUR_HOST:YOUR_PORT/YOUR_DATABASE
Schema: YOUR_SCHEMA_NAME
Username: indicative
Password: INSERT_PASSWORD_HERE

You can access this information from the Redshift cluster information page:

r8.png

5. Contact your Indicative account manager to verify that your Redshift cluster is properly configured for Indicative access.

0 users found this helpful