1 of 6

Before you create an Amazon Redshift workspace

Before you create a workspace that uses the Amazon Redshift data connector, complete the configuration that is outlined in the following topics.

Required AWS instance profile permissions for Amazon Redshift

When it uses Amazon Redshift, Tonic Structural orchestrates the creation, usage, and deletion of several AWS components.

The required permissions to do so are taken from the instance profile role of the machine that runs Structural's server. This role (EC2) needs the permissions listed below.

Note that these permissions are starting point. Based on your exact AWS setup, you might need to add additional permissions. For example, if you use AWS KMS on your S3 buckets, then you might need to grant AWS Key Management Service (AWS KMS) access. Go to the instructions on how to use AWS KMS encryption with Structural's programmatically generated Amazon SQS queues.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "sqs:ListQueues",
                "lambda:CreateEventSourceMapping",
                "lambda:DeleteEventSourceMapping",
                "lambda:GetEventSourceMapping",
                "lambda:ListFunctions",
                "ecr:DescribeRepositories",
                "ecr:ListImages"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "sqs:ChangeMessageVisibility",
                "sqs:CreateQueue",
                "sqs:DeleteMessage",
                "sqs:DeleteQueue",
                "sqs:GetQueueAttributes",
                "sqs:GetQueueUrl",
                "sqs:ListDeadLetterSourceQueues",
                "sqs:ListQueueTags",
                "sqs:ReceiveMessage",
                "sqs:SendMessage",
                "sqs:SetQueueAttributes",
                "s3:ListBucket",
                "s3:GetBucketNotification",
                "s3:PutBucketNotification",
                "lambda:CreateFunction",
                "lambda:GetFunctionConfiguration",
                "lambda:UpdateFunctionConfiguration"
            ],
            "Resource": [
                "arn:aws:sqs:*:*:tonic-*",
                "arn:aws:s3:::tonic-*",
                "arn:aws:lambda:*:*:function:tonic-*"

            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "iam:PassRole"
            ],
            "Resource": [
                <ARN to Structural Lambda Role (see below docs)>
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:GetObjectVersion",
                "s3:DeleteObject",
                "s3:DeleteObjectVersion"
            ],
            "Resource": "arn:aws:s3:::tonic-*/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:DescribeLogStreams",
                "logs:GetQueryResults",
                "logs:GetLogEvents",
                "logs:FilterLogEvents",
                "logs:StartQuery",
                "logs:StopQuery"
            ],
            "Resource": ["arn:aws:lambda:${region}:${account_id}:log-group:/aws/lambda/tonic-*"]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::tonic-*",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "*"
                    ]
                }
            }
        }
    ]
}

The above policy allows Structural to properly orchestrate jobs in your AWS infrastructure. It assumes that you use default names for objects in AWS, and that your source and destination S3 buckets begin with the tonic- prefix.

Setting up the AWS Lambda role for Amazon Redshift

Creating the role

The AWS Lambda function that Tonic Structural sets up requires an AWS role. The name of this role is set by the following environment setting:

TONIC_LAMBDA_ROLE

The policy for this role should look like this:

{
	"Version": "2012-10-17",
	"Statement": [{
		"Sid": "VisualEditor0",
		"Effect": "Allow",
		"Action": [
			"s3:PutObject",
			"s3:GetObject",
			"s3:ListBucket",
			"sqs:ReceiveMessage",
			"sqs:GetQueueAttributes",
			"sqs:SendMessage",
			"sqs:DeleteMessage",
			"logs:CreateLogGroup",
			"logs:PutLogEvents"
			"logs:CreateLogStream",
		],
		"Resource": [
			"arn:aws:sqs:*:<aws account id>:tonic-*",
			"arn:aws:s3:::tonic-*",
			"arn:aws:logs:*:*:*"
		]
	}]
}

The above policy grants the Lambda function the required access to Amazon SQS, Amazon S3, and CloudWatch.

This policy assumes that the S3 buckets and Amazon SQS queues that are used begin with the tonic- prefix.

Enabling Lambda to assume the role

After you create the role, you must allow the Lambda service to assume the role.

For the role, the Trust relationships in the AWS IAM role should look like the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "lambda.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

AWS KMS permissions for Amazon SQS message encryption

If you use AWS KMS for Amazon SQS encryption, make sure that you provided the correct key ID for the Tonic Structural environment setting TONIC_LAMBDA_KMS_MASTER_KEY.

Also provide Amazon S3 access under your AWS KMS key policy:

{
    "Sid": "Allow access for Amazon S3 Event Notifications to Amazon SQS",
    "Effect": "Allow",
    "Principal": {
        "Service": "s3.amazonaws.com"
    },
    "Action": [
        "kms:Decrypt",
        "kms:GenerateDataKey"
    ],
    "Resource": "*"
}

Additional key permissions must be added to your Amazon EC2 and Lambda roles:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "kms:Decrypt",
                "kms:Encrypt",
                "kms:GenerateDataKey"
            ],
            "Resource": "<ARN to AWS KMS key>"
        }
    ]
}

Amazon Redshift-specific Structural environment settings

Tonic Structural allows you to set several Amazon Redshift-specific environment settings that make it easier to adapt our Amazon Redshift integration into your specific AWS environment.

You configure these settings in the Structural worker container.

# No default value
# This setting is required to be set by user
# ARN of AWS Role to be assumed by Tonic's Lambda function
TONIC_LAMBDA_ROLE

# Default value of 30 secs
# Timeout of Lambda used to process data files
# Maximum allowed duration of Lambda function is 15 min
TONIC_LAMBDA_TIMEOUT

# Default value of 1024MB
# Memory limit of Lambda used to process data files
# Maximum allowed memory of Lambda function is 10240 MB
TONIC_LAMBDA_MEMORY_SIZE

# Default value of 30 secs
# Visibility of SQS which stores messages sent to Lambda
# Note that this value must be >= TONIC_LAMBDA_TIMEOUT
TONIC_LAMBDA_SQS_VISIBILITY_TIMEOUT

# No default value
# This setting is required to be set by user if using AWS KMS encryption
# AWS KMS Key ID for encrypting messages sent to Amazon SQS
TONIC_LAMBDA_KMS_MASTER_KEY

Source and destination database permissions for Amazon Redshift

User permissions on the source database

The following is an example of how to create an Amazon Redshift user with the permissions needed to connect to Tonic Structural.

We recommend that you use a backup as your source database instead of connecting directly to your production environment.

If your database contains additional schemas that are included, then you must also run the same commands for those schemas.

--create user
CREATE USER tonic_user WITH PASSWORD 'tonic_password';

--add USAGE GRANTs on all schemas in the DB 
GRANT USAGE ON SCHEMA public TO tonic_user;

--add SELECT GRANTs on all tables in each schema in the DB 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic_user;

User permissions on destination database

The destination database must exist before Structural can connect to it. The user provided to Structural for connecting to the destination database must be a superuser who holds ownership and privileges of all schemas and tables.

--create a superuser
CREATE USER tonic_user createuser PASSWORD 'tonic_password';

Setting up the AWS Lambda role for Amazon Redshift

Creating the role

The AWS Lambda function that Tonic Structural sets up requires an AWS role. The name of this role is set by the following environment setting:

TONIC_LAMBDA_ROLE

The policy for this role should look like this:

{
	"Version": "2012-10-17",
	"Statement": [{
		"Sid": "VisualEditor0",
		"Effect": "Allow",
		"Action": [
			"s3:PutObject",
			"s3:GetObject",
			"s3:ListBucket",
			"sqs:ReceiveMessage",
			"sqs:GetQueueAttributes",
			"sqs:SendMessage",
			"sqs:DeleteMessage",
			"logs:CreateLogGroup",
			"logs:PutLogEvents"
			"logs:CreateLogStream",
		],
		"Resource": [
			"arn:aws:sqs:*:<aws account id>:tonic-*",
			"arn:aws:s3:::tonic-*",
			"arn:aws:logs:*:*:*"
		]
	}]
}

The above policy grants the Lambda function the required access to Amazon SQS, Amazon S3, and CloudWatch.

This policy assumes that the S3 buckets and Amazon SQS queues that are used begin with the tonic- prefix.

Enabling Lambda to assume the role

After you create the role, you must allow the Lambda service to assume the role.

For the role, the Trust relationships in the AWS IAM role should look like the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "lambda.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Required AWS instance profile permissions for Amazon Redshift

When it uses Amazon Redshift, Tonic Structural orchestrates the creation, usage, and deletion of several AWS components.

The required permissions to do so are taken from the instance profile role of the machine that runs Structural's server. This role (EC2) needs the permissions listed below.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "sqs:ListQueues",
                "lambda:CreateEventSourceMapping",
                "lambda:DeleteEventSourceMapping",
                "lambda:GetEventSourceMapping",
                "lambda:ListFunctions",
                "ecr:DescribeRepositories",
                "ecr:ListImages"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "sqs:ChangeMessageVisibility",
                "sqs:CreateQueue",
                "sqs:DeleteMessage",
                "sqs:DeleteQueue",
                "sqs:GetQueueAttributes",
                "sqs:GetQueueUrl",
                "sqs:ListDeadLetterSourceQueues",
                "sqs:ListQueueTags",
                "sqs:ReceiveMessage",
                "sqs:SendMessage",
                "sqs:SetQueueAttributes",
                "s3:ListBucket",
                "s3:GetBucketNotification",
                "s3:PutBucketNotification",
                "lambda:CreateFunction",
                "lambda:GetFunctionConfiguration",
                "lambda:UpdateFunctionConfiguration"
            ],
            "Resource": [
                "arn:aws:sqs:*:*:tonic-*",
                "arn:aws:s3:::tonic-*",
                "arn:aws:lambda:*:*:function:tonic-*"

            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "iam:PassRole"
            ],
            "Resource": [
                <ARN to Structural Lambda Role (see below docs)>
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:GetObjectVersion",
                "s3:DeleteObject",
                "s3:DeleteObjectVersion"
            ],
            "Resource": "arn:aws:s3:::tonic-*/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:DescribeLogStreams",
                "logs:GetQueryResults",
                "logs:GetLogEvents",
                "logs:FilterLogEvents",
                "logs:StartQuery",
                "logs:StopQuery"
            ],
            "Resource": ["arn:aws:lambda:${region}:${account_id}:log-group:/aws/lambda/tonic-*"]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::tonic-*",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "*"
                    ]
                }
            }
        }
    ]
}