This guide provides the steps required to set up Kameleoon integration with Amazon Redshift. Setup is required both within the Kameleoon UI and within your own AWS account. Some of these actions may need to be adapted to your specific AWS environment. In any case these steps should be done by your AWS account administrator.
Step 1: Create AWS IAM user and role
You need to create an IAM user and role that Kameleoon will use to access your Redshift cluster. These can be created via the AWS Management Console or the AWS CLI, we provide 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
Be sure to keep the AccessKeyId and SecretAccessKey fields that are in the JSON returned by this command as you will be asked to provide them to Kameleoon when doing the setup in the Kameleoon UI.
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"
},
"Action": "sts:AssumeRole"
}
]
}'Replace {account_id} with your AWS account ID.
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. This should be done at the SQL user level, via SQL commands. The default SQL user created for an IAM role is called “IAMR:{role_name}”, so if the role you use is called “kameleoon_role” then you will be executing these commands for SQL user “IAMR:kameleoon_role”. Execute the following SQL commands in your Redshift SQL client:
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 ingest 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 on 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";Step 3: Enabling and configuring the integration in the Kameleoon UI
Go to the Integrations page in your Kameleoon account and find Amazon Redshift in the list
Click Install the tool and fill in the following fields to provide Kameleoon with access credentials to your Redshift cluster, after first selecting the project for which you want to configure this access from the list that will appear on the left.
The required fields are:
- AWS Account ID: the unique numerical identifier of your company’s Amazon Web Services account
- AWS Role name: the name of the AWS IAM role you created for Kameleoon according to the instructions from the previous sections
- AWS Access key ID and Secret access key ID: these two fields are provided to you when creating credentials for the AWS role. If you used the AWS command line interface to create this key as described in step 1 of this documentation, then these to values were provided in the JSON response to that command
Click Confirm, your credentials have been uploaded for that project. Below there is an ADVANCED SETUP thumbnail containing the IP addresses from which Kameleoon will connect to your warehouse. Many organizations filter the IP addresses that are authorized to connect to their warehouse. If this is the case you can authorize these IPs from within your AWS account with the following steps.
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. This can be done in the AWS console:
- Identify the security group: the security group associated with your Redshift cluster can be seen in the Redshift dashboard, and you can access the configuration page of this security group from there. If you are using Redshift Serverless, you are looking for the Security Group of your Workgroup.
- Add inbound rules:
- Navigate to the Inbound Rules tab.
- Click on Edit inbound rules and then 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)
- Save the changes.
Step 5: Open Workgroup VPC to external connections
Ensure that the VPC associated with your Redshift cluster is configured to allow external connections. You can either for simplicity 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. For this 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
