Articles in this section

Configuring Your Redshift or Redshift Serverless Destination

Follow these steps to configure your Redshift or Redshift Serverless destination.

# In this article

# Prerequisites

Satisfy the following prerequisites before configuring Redshift or Redshift Serverless:

  • If your Redshift destination requires IP allowlisting, have Iterable's static IP (in this case, 35.193.179.61 for US configurations, 104.155.71.71 for EU configurations) available for the following tasks.

  • Redshift authentication uses role-based access, by default–to grant access, you must prepopulate the trust policy with the data-syncing service account identifier, similar to the following example JSON object:

    {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "sts:AssumeRoleWithWebIdentity"
      ],
      "Principal": {
        "Federated": "accounts.google.com"
      },
      "Condition": {
        "StringEquals": {
          "accounts.google.com:sub": "<some_service_account_identifier>"
        }
      }
    }
  ]
}

# Step 1: Create a limited user in Redshift or Redshift Serverless

  1. Connect to Redshift using the SQL client.

  2. Create a user to write the data, with a password of your choice.

    CREATE USER <username> PASSWORD '<password>';

    Role-based authorization doesn't require a password. If you want to create the user without one, type:

    CREATE USER <username> PASSWORD DISABLE;

  3. Grant create and temporary privileges for users on the database so the service can create new schemas and temporary tables.

    GRANT CREATE, TEMPORARY ON DATABASE <database> TO <username>;

IMPORTANT

You don't need to create the schema manually in the destination ahead of time–it will be created, by default, during the first sync, using the schema name you supply during Step 4. However, if you want to use a schema that does already exist, you must ensure that the user has the proper permissions on the schema by using:

GRANT ALL ON schema <schema> TO <username>;

Once you've provided the GRANT ALL permission on the schema, you can remove the CREATE permission on the database, but you must keep the TEMPORARY permission.

# Step 2: Allowlist connection

  1. In the Redshift console, click Clusters and note the cluster name.

  2. Select the cluster to connect to Iterable.

  3. In the General information pane, note the Endpoint details. You may need to copy the full details to discover the endpoint and port number.

  4. Click the Properties tab.

  5. Scroll to the Network and security settings section.

  6. In the VPC security group field, select a security group to open.

  7. In the Security Groups window, click Inbound rules.

  8. Click Edit inbound rules and create custom TCP rules for the static IP, as follows:

    • Select Custom TCP in the drop-down menu.
    • Enter your Redshift port number (likely 5439).
    • Enter the static IP (35.193.179.61 for US configurations, 104.155.71.71 for EU configurations).
    • Click Add rule.

TIP

To use IP allowlisting from outside your VPC, the Redshift cluster must be publicly accessible and deployed in a public subnet with a route to an Internet Gateway.

For private Redshift clusters, SSH tunneling is supported. Configuring an SSH tunnel for a Redshift cluster is uncommon, but if you need help doing so, contact Iterable for instructions.

# Step 3: Create a staging bucket

# Create the staging bucket

  1. Navigate to the S3 service page.

  2. Click Create bucket.

  3. Enter a Bucket name and modify settings, as needed, noting the bucket name and AWS region.

TIP

AWS recommends the following bucket settings:

  • Set Object Ownership to "ACLs disabled"
  • Set Block Public Access settings to "Block all public access"
  1. Click Create bucket.

# Create the policy

  1. Navigate to the IAM service page, click the Policies navigation tab, and click Create policy.

  2. Click the JSON tab, and paste the following policy, with the bucket name used in the previous step. The next steps vary, depending on whether you're setting up a Redshift destination or a Redshift Serverless destination.

    For Redshift, supply the correct Redshift values for: REGION_NAME, ACCOUNT_ID, CLUSTER_NAME, USERNAME, and DATABASE_NAME.

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::BUCKET_NAME"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::BUCKET_NAME/*"
        },
        {
            "Effect": "Allow",
            "Action": "redshift:GetClusterCredentials",
            "Resource": [
                "arn:aws:redshift:REGION_NAME:ACCOUNT_ID:dbuser:CLUSTER_NAME/USERNAME",
                "arn:aws:redshift:REGION_NAME:ACCOUNT_ID:dbname:CLUSTER_NAME/DATABASE_NAME"
            ]
        }
    ]
}

    For Redshift Serverless, supply the correct Redshift values for: REGION_NAME, ACCOUNT_ID, WORKGROUP_NAME_OR_ID.

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "redshift-serverless:GetCredentials"
            ],
            "Resource": [
                "arn:aws:redshift-serverless:REGION_NAME:ACCOUNT_ID:workgroup/WORKGROUP_NAME_OR_ID"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::BUCKET_NAME"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::BUCKET_NAME/*"
        }
    ]
}

NOTE

  • For Redshift provisioned, the s3:ListBucket permission applies to BUCKET_NAME (without /*).
  • For Redshift Serverless, the same applies—s3:ListBucket must target BUCKET_NAME while object actions target BUCKET_NAME/*.
  • For user credentials containing special characters, avoid using the following characters, which can break connection string parsing: @, [, ], /, ?, #, ", \, +, space, &, :
  1. Click to the Review step, choose a policy name (such as, transfer-service-policy), add a description, and click Create policy. Note the policy name–you'll need it in an upcoming step.

# Create the role

  1. Navigate to the IAM service page, click the Roles navigation tab, and click Create role.

  2. Select Custom trust policy and paste the provided trust policy (from prerequisites) to enable AssumeRole access to this role, and click Next.

  3. Add the permissions policy created above and click Next.

  4. Enter a Role name (such as, transfer-role) and click Create role.

  5. Search for and click the created role in the Roles list and note the ARN value for later use.

NOTE

AWS recommends role based authentication, however, if you want to use HMAC Access Key ID & Secret Access Key, instead, follow these steps:

  1. Navigate to the IAM service page, click the Users navigation tab, and click Add users.

  2. Enter a User name for the service (such as, transfer-service), and click Next.

  3. Under Select AWS access type, select the Access key - Programmatic access
    option, and click Next: Permissions.

  4. Click the Attach existing policies directly option, search for and select the policy name created in the previous step, and click Next: Tags.

  5. Click Next: Review and click Create user.

  6. In the Success screen, record the Access key ID and the Secret access key.

# Step 4: Create a new destination

Create a new destination for data writing.

  1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you're working on.

  2. Go to Integrations > Data Sync.

  3. Select the name of the destination that you're connecting to, then click Continue.

  4. Provide the following details:

    • Destination name – A name for the destination.
    • Host – The host for your Redshift cluster.
    • Port – The port for the connection.
    • Database – The target database name.
    • Schema – The target schema name.
    • Cluster – The target cluster name.
    • Auth Method – Choose one of the following:
      • IAM Role – Recommended. Uses service account impersonation. Enter the Custom Trust Policy and IAM Role ARN.
      • User/Password – Key-based authentication. Enter the password for the destination service account, the Bucket Access ID, and the Bucket Secret Key.
    • S3 bucket name – The staging bucket for data transfers.
    • S3 bucket region – The region where your staging bucket is located.
    • SSH tunneling – If you require extra security, enable it and provide the necessary details.

  5. Click Create Destination.

# Permissions checklist

Verify that the following permissions are applied in your project:

  • Ensure that a Redshift database user exists and has CREATE and TEMPORARY permissions on the database. If you pre-created the schema, ensure that you also enable GRANT ALL ON SCHEMA <schema> TO <username>.

  • Ensure that the IAM role trust policy allows data syncing services to assume the role. Your IAM policy must include:

    • redshift:GetClusterCredentials on your target cluster (db user and db name resources)
    • S3 ListBucket on arn:aws:s3:::BUCKET_NAME
    • S3 GetObject, PutObject, and DeleteObject on arn:aws:s3:::BUCKET_NAME/*

  • If network allowlisting is enforced, ensure that it permits egress IP/CIDR for the Redshift port (typically 5439).

# FAQ

Q: How is the Redshift connection secured?

A: We use role-based authentication with your AWS IAM Role. The data syncing services assume your role to obtain short-lived credentials.

Q: Why is an S3 bucket required?

A: Redshift's high-throughput path loads data from S3 using COPY. We stage files briefly in your bucket to maximize throughput and reliability. Files are cleaned up after load.

Q: What are the oaud vs sub IDs used for?

A: These identity claims are used in the IAM trust policy when federating from GCP to AWS. sub uniquely identifies our Google principal in federation. oaud is an additional claim used to bind role assumption to your organization.

Q: Why am I getting authentication errors with Redshift?

A: Common causes for authentication errors include:

  • Missing or incorrect permission on redshift:GetClusterCredentials–ensure it targets the correct cluster ARN and region/account.

  • Trust policy mismatch, where the data syncing service's principal isn't permitted to assume your role.

  • Use of a serverless workgroup permission or redshift-serverless:GetCredentials instead of provisioned cluster + redshift:GetClusterCredentials.

  • A propagation delay where IAM changes take a few minutes to apply. In this case, trying again after 5-10 minutes will resolve the error.

Q: Do I need to precreate the schema?

A: No. The schema provided in the destination configuration is created automatically on the first sync. If you pre-create it, grant ALL permissions on the schema to the writer user, and remove the database-level CREATE permission (retain TEMPORARY).

Related articles