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.
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:
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 settingTONIC_LAMBDA_KMS_MASTER_KEY.
Also provide Amazon S3 access under your AWS KMS key policy:
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';