Cross account IAM permission set-up for running a Redshift COPY Command from Amazon S3 bucket in one AWS account to Amazon Redshift in another AWS account.

In order to copy data from an S3 bucket in one AWS account to a Redshift cluster in another AWS account, you need to setup cross-account access permissions. Below is the step-by-step guidance on how to set it up.

Step 1: Login to Source Account ( Where S3 data is present) Create an IAM Policy in the source account as below.

Create Policy as ‘S3ReadAccessPolicy’ , you can use any other name as per your company policy : This policy has read access to the S3 bucket.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "DescriptionS3Access",
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": [
                "arn:aws:s3:::<S3 bucketname in Source Account>",
                "arn:aws:s3:::<S3 bucketname in Source Account>/*"
            ]
        }
    ]
}

Step 2: Create an IAM Role as below. In place of account Id put the Account number Where Redshift cluster is created.

Step 3: In Add Permission Page select the policy you created above (S3ReadAccessPolicy)

Step 4: Give a name to the Role - say S3ReadRole, create the role

Step 5: Now Login to account where you have Redshift cluster is created. Create an IAM role in the account where the Redshift cluster is created that allows assuming a role in the source account containing the S3 bucket.

Select AWS Service as below → Redshift - Customizable→ Next

No Permission needed to be selected here

Step 6: Create the Role: Say RedshiftAccessRole

Step 7: Create Role:

Step 8 Add an inline policy as below

Step 9 Use below policy and

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "CrossAccountPolicy",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<Source account num>:role/S3ReadRole"
        }
    ]
}

Step 10 Specify the policy as shown below

Step 11 Create inline policy

Step 12 Now go to the Redshift Console→ Properties → Associate the Role you created above

Step 13 Associate IAM Role

Step 14 Go to Redshift Query EditorV2 and run below COPY command to test if it working.

In COPY command specifying the IAM role ARN created in step 6 . This will copy the data from S3 to the Redshift cluster in the other account.

COPY dev.public.crossaccount_table FROM 's3://****-**/**.csv' 
IAM_ROLE '<arn:aws:iam::Role we associated to the redshift cluster above?' 
FORMAT AS CSV DELIMITER ',' QUOTE '"' IGNOREHEADER 1 REGION AS 'us-east-1';