Polytomic accesses data in Redshift Serverless through either the Redshift Data API or a Redshift Serverless endpoint. Unlike the standard Redshift connection, Redshift Serverless uses IAM roles for authorization.

Connecting to Redshift Serverless

1. In Polytomic, go to Connections → Add connection → Redshift Serverless.
2. Enter the Database, Workgroup, and Region of your workgroup, and the IAM role ARN for Polytomic to assume (see below).
3. Choose a Connection method:
   • Redshift Data API (default): Polytomic runs queries through the AWS Redshift Data API. You don't need to allow network access to the workgroup. If you reach the Data API through a VPC interface endpoint or another non-default URL, tick Override Redshift Data API endpoint and enter the endpoint URL (for example, https://redshift-data.us-west-2.amazonaws.com).
   • Redshift Serverless Endpoint: Polytomic connects directly to your workgroup's endpoint (for example, acme.12345.us-west-2.redshift-serverless.amazonaws.com:5439) using temporary database credentials from redshift-serverless:GetCredentials. The workgroup must be publicly accessible, and its security group must allow inbound traffic from Polytomic's IP addresses. Choose an SSL mode: verify-full (default), verify-ca, or require.
4. Click Save.

Syncing from Redshift Serverless

The role must have permission for the following IAM actions:

• redshift-data:CancelStatement
• redshift-data:DescribeStatement
• redshift-data:DescribeTable
• redshift-data:ExecuteStatement
• redshift-data:GetStatementResult
• redshift-data:ListDatabases
• redshift-data:ListSchemas
• redshift-data:ListTables
• redshift-serverless:GetCredentials

As an example, this permission policy grants the necessary permissions for all Redshift Serverless workgroups in the account:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift-data:CancelStatement",
                "redshift-data:DescribeStatement",
                "redshift-data:DescribeTable",
                "redshift-data:ExecuteStatement",
                "redshift-data:GetStatementResult",
                "redshift-data:ListDatabases",
                "redshift-data:ListSchemas",
                "redshift-data:ListTables",
                "redshift-serverless:GetCredentials"
            ],
            "Resource": "*"
        }
    ]
}

The role's trust policy must be configured to allow Polytomic (AWS Account ID 568237466542) to assume the role. An external identifier is displayed when the connection is created, which may be used to further limit access to the role.

As an example, your trust policy will look something like the following:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::568237466542:root"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "a1efa791-4530-43a0-962d-74e2ccf18309"
                }
            }
        }
    ]
}

The value for sts:ExternalId is your Polytomic Organization's ID. Polytomic displays it when you create the connection. It's the same for every IAM role connection in your Organization.

Using Redshift Serverless as a Bulk Sync source

Redshift Serverless connections can be used as the source for a bulk sync (i.e. an ELT workload into a data warehouse or cloud storage bucket). If you have an S3 bucket available, Polytomic can use UNLOAD to extract data from Redshift, which may be more efficient.

To configure Polytomic to use UNLOAD, check Read data using unload in the connection configuration and enter the bucket name and region.

The IAM Policy must have permission to read, write, and list objects in the bucket.

Redshift must also be able to assume the role in order to perform the unload operation. An example trust policy should look something like the following when using UNLOAD:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::568237466542:root"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "a1efa791-4530-43a0-962d-74e2ccf18309"
                }
            }
        },
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Syncing to Redshift Serverless

If you wish to write to Redshift, there are three requirements:

1. A Redshift role with write permission to the table, schemas, or database that you want to load data into.
2. An S3 bucket in the same region as your Redshift server. Polytomic will use this bucket as a staging area for intermediate data. Files are removed once jobs complete. The IAM role used to access Redshift must also have read and write permission.
3. Redshift must be able to assume the IAM role used to connect.

Create a Redshift role

You can create a role for Polytomic with the following query.

CREATE ROLE polytomic;

GRANT TEMP ON DATABASE my_database TO polytomic;
GRANT CREATE ON DATABASE my_database TO polytomic;

GRANT TEMP ON DATABASE my_database TO ROLE polytomic;
GRANT CREATE ON DATABASE my_database TO ROLE polytomic;

Assign the Redshift role to your IAM role

Tag the IAM role in your AWS console with the Redshift role from the previous step using the RedshiftDbRoles tag. In the image below, the role created in Redshift with write permissions happens to be called rss_admin; you may pick another name. See AWS Documentation for more information on binding Redshift and IAM roles.

S3 bucket

Polytomic will use an S3 bucket to stage data that will be loaded into Redshift; if you are also using Redshift as a source with UNLOAD, the same bucket and permissions will suffice.

The IAM role must have permission to access the S3 bucket.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:GetObjectAttributes",
                "s3:ListBucket",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::unload-example",
                "arn:aws:s3:::unload-example/*"
            ]
        }
    ]
}

Role assumption

Redshift must also have permission to assume the IAM role in order to read the staged data from the bucket. An example of a full trust policy follows.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::568237466542:root"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "sts:ExternalId": "a1efa791-4530-43a0-962d-74e2ccf18309"
                }
            }
        },
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}