Before you create a workspace that uses the Amazon Redshift data connector, complete the configuration that is outlined in the following topics.
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.
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:
TONIC_LAMBDA_ROLE{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "lambda.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}{
"Version": "2012-10-17",
"Statement": [{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:ListBucket",
"sqs:ReceiveMessage",
"sqs:GetQueueAttributes",
"sqs:GetQueueUrl",
"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:*:*:*"
]
}]
}Tonic Structural allows you to set several Amazon Redshift-specific 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_KEYIf 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>"
}
]
}When it uses Amazon Redshift, Tonic Structural orchestrates the creation, usage, and deletion of 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.
These policies 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.
Note that these permissions are starting point. Based on your exact AWS setup, you might need to add additional permissions.
By default, Amazon Redshift workspaces use the Data Pipeline V2 data generation process. For this process, the required permissions are as follows:
For workspaces that use the previous data generation process, which uses Amazon SQS and AWS Lambda, the required permissions are as follows:
Note that 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": [
"s3:ListBucket",
"s3:GetBucketNotification",
"s3:PutBucketNotification"
],
"Resource": "arn:aws:s3:::tonic-*"
},
{
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:GetObjectVersion",
"s3:DeleteObject",
"s3:DeleteObjectVersion"
],
"Resource": "arn:aws:s3:::tonic-*/*"
}
]
}{
"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>
]
},
{
"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:logs:${region}:${account_id}:log-group:/aws/lambda/tonic-*"]
},
{
"Effect": "Allow",
"Action": "s3:ListBucket",
"Resource": "arn:aws:s3:::tonic-*",
"Condition": {
"StringLike": {
"s3:prefix": [
"*"
]
}
}
}
]
}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.
The destination database must exist before Structural can connect to it.
The required permissions for the user that Structural uses to connect to the destination database depend on whether the workspace .
By default, workspaces do not preserve the source ownership in the destination database. The destination user then requires the following permissions:
However, if the workspace does preserve the source database ownership, then the destination database user must be a superuser who holds ownership and privileges of all schemas and tables.
--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;
--add SELECT GRANT on pg_catalog.svv_table_info
GRANT SELECT ON pg_catalog.svv_table_info TO tonic_user; --create a superuser
CREATE USER tonic_user createuser PASSWORD 'tonic_password';-- Allows viewing table metadata.
GRANT SELECT ON pg_catalog.svv_table_info TO tonic_destination_user;
-- Allows creation of temporary tables on the destination database during a session.
GRANT TEMPORARY ON DATABASE tonic_destination_database TO tonic_destination_user;
-- Allows creation of schemas and permanent objects within the destination database.
GRANT CREATE ON DATABASE tonic_destination_database TO tonic_destination_user;
-- If any schemas from the source already exist in the destination database and the destination user is not the owner, run the followingfor each relevant schema.
GRANT DROP ON SCHEMA schema_name TO tonic_destination_user;