Cross Account Redshift UNLOAD/COPY

all_account_policy.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowS3",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::{REDSHIFT_ACCOUNT_ID}:root"
            },
            "Action": [
                "s3:PutObject",
                "s3:ListBucket",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::CHANGEME/*",
                "arn:aws:s3:::CHANGEME"
            ]
        }
    ]
}

bucket_policy.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowS3",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::{REDSHIFT_ACCOUNT_ID}:role/{ROLE_NAME}"
            },
            "Action": [
                "s3:PutObject",
                "s3:ListBucket",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::CHANGEME/*",
                "arn:aws:s3:::CHANGEME"
            ]
        }
    ]
}

permissions.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowS3",
            "Effect": "Allow",
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::CHANGEME/*",
                "arn:aws:s3:::CHANGEME"
            ]
        }
    ]
}

trust_relationship.json

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

unload.sql

UNLOAD ('SELECT 1')
TO 's3://CHANGEME/prefix-'
CREDENTIALS 'aws_iam_role=arn:aws:iam::{REDSHIFT_ACCOUNT_ID}:role/{ROLE_NAME}';