Setting up Amazon Redshift - Kameleoon Documentation

Kameleoon can’t read from or write to your Amazon Redshift cluster until your AWS account grants it access. That access has two parts: an IAM role that Kameleoon assumes, and SQL permissions on your Redshift database that the role holds. Your AWS account administrator should perform the AWS steps, and you enter the credentials in the Kameleoon UI in Step 3. You may need to adapt some of these actions to your AWS environment.

Step 1: Create AWS IAM user and role

You must create an IAM user and role that Kameleoon uses to access your Redshift cluster. You can create these in the AWS Management Console or the AWS CLI. Kameleoon provides example commands for the AWS CLI.

1. Create an IAM user

Use the following command to create a dedicated IAM user for Kameleoon: aws iam create-user --user-name kameleoon_user Next, generate an access key for the kameleoon_user: aws iam create-access-key --user-name kameleoon_user Keep the AccessKeyId and SecretAccessKey fields from the JSON that this command returns. You provide them to Kameleoon in Step 3.

2. Create an IAM role

Create a role that Kameleoon can assume, allowing it to interact with your Redshift cluster. Use the following command to create the role with an inline JSON trust policy:

aws iam create-role --role-name kameleoon_role --assume-role-policy-document '{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::{account_id}:user/kameleoon_user",
                "Service": [
    				"s3.amazonaws.com",
    				"redshift.amazonaws.com",
   				"iam.amazonaws.com",
    				"redshift-serverless.amazonaws.com"
]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}'

Replace {account_id} with your AWS account ID. If you use Redshift only as a source, you can omit s3.amazonaws.com. If you use a provisioned cluster, you can omit redshift-serverless.amazonaws.com.

For Amazon Redshift as a destination — grant S3 Redshift access rights to an IAM role

To let Kameleoon stage events in S3 and load them into Redshift, attach a policy to the role with the following command.

aws iam put-role-policy \
  --role-name kameleoon_role \
  --policy-name kameleoon_redshift_destination \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Effect": "Allow",
        "Action": [
          "s3:PutObject",
          "s3:GetObject",
          "s3:ListBucket",
          "s3:DeleteObject"
        ],
        "Resource": "arn:aws:s3:::{s3_bucket_name}/*"
      },
      {
        "Effect": "Allow",
        "Action": [
          "redshift:GetClusterCredentialsWithIAM",
          "redshift:GetClusterCredentials",
          "redshift:DescribeClusters"
        ],
        "Resource": "arn:aws:redshift:{aws_region}:{account_id}:dbuser:kameleoon_user"
      }
    ]
  }'

Replace {s3_bucket_name}, {aws_region} and {account_id} with your data. Alternatively, use the existing AmazonS3FullAccess and AmazonRedshiftFullAccess policies.

Step 2: Grant permissions to Kameleoon role

Grant the necessary permissions to the IAM role associated with Kameleoon to allow interaction with your Redshift database. Grant permissions at the SQL user level via SQL commands. AWS names the default SQL user created for an IAM role IAMR:{role_name}, so if you name the role kameleoon_role, run these commands for SQL user IAMR:kameleoon_role.

For Amazon Redshift as a source

1. Grant access to existing schema

Provide the Kameleoon IAM role access to the relevant schema and tables in your Redshift database. For example, if you want Kameleoon to import data from your “users” table in schema schema_containing_data, run the following SQL commands:

GRANT USAGE ON SCHEMA "schema_containing_data" TO "IAMR:kameleoon_role";
GRANT SELECT ON TABLE "schema_containing_data"."users" TO "IAMR:kameleoon_role";

2. Create Kameleoon configuration schema

Create a new schema for Kameleoon configurations and grant the necessary permissions:

CREATE SCHEMA "kameleoon_configuration";

GRANT USAGE ON SCHEMA "kameleoon_configuration" TO "IAMR:kameleoon_role";
GRANT CREATE ON SCHEMA "kameleoon_configuration" TO "IAMR:kameleoon_role";

3. Grant DML permissions in Kameleoon configuration schema

Grant the Kameleoon role permissions to insert, update, and delete data in the kameleoon_configuration schema:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "kameleoon_configuration" TO "IAMR:kameleoon_role";

4. Set default privileges for future tables

Ensure that any future tables created in the kameleoon_configuration schema automatically grant the necessary permissions to the Kameleoon role:

ALTER DEFAULT PRIVILEGES IN SCHEMA "kameleoon_configuration"
GRANT INSERT, UPDATE, DELETE ON TABLES TO "IAMR:kameleoon_role";

For Amazon Redshift as a destination

1. Create Kameleoon events schema

Create a new schema for Kameleoon events and grant the necessary permissions:

CREATE SCHEMA "kameleoon_events";

GRANT USAGE ON SCHEMA "kameleoon_events" TO "IAMR:kameleoon_role";
GRANT CREATE ON SCHEMA "kameleoon_events" TO "IAMR:kameleoon_role";

2. Grant DML permissions on Kameleoon events schema

Grant the Kameleoon role permissions to insert, update, and delete data in the kameleoon_events schema:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "kameleoon_events" TO "IAMR:kameleoon_role";

3. Set default privileges for future tables

Ensure that any future tables created in the kameleoon_events schema automatically grant the necessary permissions to the Kameleoon role:

ALTER DEFAULT PRIVILEGES IN SCHEMA "kameleoon_events"
GRANT INSERT, UPDATE, DELETE ON TABLES TO "IAMR:kameleoon_role";

Step 3: Enable and configure the integration in the Kameleoon UI

Amazon Redshift card on the Integrations page, with the Install the tool button in the bottom right corner.

Click Install the tool, select the project that you want to configure from the list on the left, and fill in the following fields with the credentials for your Redshift cluster.

Amazon Redshift settings form with the AWS Account ID, AWS Role name, AWS Access key ID and AWS Secret access key ID fields.

The required fields are:

AWS Account ID: the unique numerical identifier of your Amazon Web Services account
AWS Role name: the name of the AWS IAM role you created for Kameleoon, for example kameleoon_role
AWS Access key ID and AWS Secret access key ID: AWS provides these two fields when you create an access key for the IAM user. If you used the AWS command line interface to create this key as described in Step 1 of this article, you received these two values in the JSON response to that command

Click Confirm to upload your credentials for the project. Under Advanced setup, expand Authorize IP addresses to see the IP addresses that Kameleoon uses to connect to your warehouse. Many organizations filter which IP addresses can connect to their warehouse. If yours does, authorize these IP addresses in your AWS account by following Step 4 and Step 5.

Step 4: Configure security group for Redshift

To allow Kameleoon to connect to your Redshift cluster, authorize queries from Kameleoon’s IP addresses in your security group. You can authorize these IP addresses in the AWS console:

Identify the security group: You can see the security group associated with your Redshift cluster in the Redshift dashboard, and you can access the configuration page of this security group from there. If you use Redshift Serverless, look for the security group of your workgroup.
Add inbound rules
- Navigate to the Inbound Rules tab.
- Click Edit inbound rules > Add rule.
- Set the following parameters:
  - Type: Custom TCP rule
  - Port range: 5439 (default Redshift port)
  - Source: Custom (add the Kameleoon IP addresses displayed in the Redshift integration section of the Kameleoon UI)

Step 5: Open the VPC to external connections

Configure the VPC associated with your Redshift cluster to allow external connections. For simplicity, you can make the VPC publicly accessible and rely solely on the security group settings. A more secure approach is to fine-tune the network access control list of your VPC subnet. To do so, modify the VPC settings in your AWS console as follows:

Access VPC settings: Go to the VPC dashboard in the AWS Management Console.
Modify Network ACLs:
- Navigate to the Network ACLs section.
- Locate the ACL associated with your VPC.
- Modify the inbound and outbound rules to allow traffic from Kameleoon’s IP addresses on port 5439.
Update route tables: Ensure that the route tables associated with your VPC are correctly configured to route traffic to your Redshift cluster.

Data region

Kameleoon stores the data from this integration in the Kameleoon data region of your account, which is the US or the EU.

Integrations

​Step 1: Create AWS IAM user and role

​1. Create an IAM user

​2. Create an IAM role

​For Amazon Redshift as a destination — grant S3 Redshift access rights to an IAM role

​Step 2: Grant permissions to Kameleoon role

​For Amazon Redshift as a source

​1. Grant access to existing schema

​2. Create Kameleoon configuration schema

​3. Grant DML permissions in Kameleoon configuration schema

​4. Set default privileges for future tables

​For Amazon Redshift as a destination

​1. Create Kameleoon events schema

​2. Grant DML permissions on Kameleoon events schema

​3. Set default privileges for future tables

​Step 3: Enable and configure the integration in the Kameleoon UI

​Step 4: Configure security group for Redshift

​Step 5: Open the VPC to external connections

​Data region