I want to use Amazon Redshift Spectrum to access AWS Glue and Amazon Simple Storage Service (Amazon S3) in a different AWS account that's within the same AWS Region. How do I do that? 

To access AWS resources that are in a different account with Amazon Redshift Spectrum, you must attach an AWS Identity and Access Management (IAM) role to your Amazon Redshift cluster. Your cluster can then assume that role to access Amazon S3, Athena, and AWS Glue on your behalf. This is called chaining roles.

Note: The resources in both accounts must be in the same Region.

In the account that is using Amazon Redshift:

1.    Open the IAM console.

2.    Choose Roles from the navigation pane, and then choose Create role.

3.    For Select type of trusted entity, choose AWS service.

4.    Choose Redshift.

5.    For Select your use case, choose Redshift - Customizable.

6.    Choose Next: Permissions, Next: Tags, and then Next: Review. You don't need to add policies or tags.

7.    Enter a Role name, and then choose Create role. In the following examples, we'll refer to this as role_1.

8.    Associate role_1 with the Amazon Redshift cluster.

In the account that is using AWS Glue and Amazon S3:

1.    Open the IAM console.

2.    Choose Policies, and then choose Create policy.

3.    Choose the JSON tab, and then enter an IAM policy similar to the following. Replace your_bucket with the name of the S3 bucket that you want to access with Amazon Redshift Spectrum.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetBucketLocation",
        "s3:GetObject",
        "s3:ListMultipartUploadParts",
        "s3:ListBucket",
        "s3:ListBucketMultipartUploads"
      ],
      "Resource": [
        "arn:aws:s3:::your_bucket",
        "arn:aws:s3:::your_bucket/*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "glue:CreateDatabase",
        "glue:DeleteDatabase",
        "glue:GetDatabase",
        "glue:GetDatabases",
        "glue:UpdateDatabase",
        "glue:CreateTable",
        "glue:DeleteTable",
        "glue:BatchDeleteTable",
        "glue:UpdateTable",
        "glue:GetTable",
        "glue:GetTables",
        "glue:BatchCreatePartition",
        "glue:CreatePartition",
        "glue:DeletePartition",
        "glue:BatchDeletePartition",
        "glue:UpdatePartition",
        "glue:GetPartition",
        "glue:GetPartitions",
        "glue:BatchGetPartition"
      ],
      "Resource": "*"
    }
  ]
}

4.    Choose Review policy.

5.    Enter a Name for the policy, and then choose Create policy.

6.    From the navigation pane, choose Roles and then choose Create role.

7.    For Select type of trusted entity, choose Another AWS account.

8.    For Account ID, enter the ID of the account that is using Amazon Redshift. For more information, see Finding Your AWS Account ID.

9.    Choose Next: Permissions.

10.    In the list of policies, select the check box next to the name of the policy that you created in step 5.

11.    Choose Next: Tags and then choose Next: Review. You don't need to add tags.

12.    Enter a Role name, and then choose Create role. In the following examples, we'll refer to this as role_2.

13.    From the navigation pane, choose Roles.

14.    Select the name of the role that you created in step 12, and then choose the Trust Relationships tab.

15.    Choose Edit Trust Relationship.

16.    Delete the existing policy and then replace it with a policy similar to the following. Replace 111122223333 with the ID of the account that is using Amazon Redshift. Replace role_1 with the name of the first role that you created.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::111122223333:role/role_1"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

17.    Choose Update Trust Policy.

In the account that is using Amazon Redshift:

1.    Open the IAM console.

2.    Choose Roles from the navigation pane, and then choose the name of the first role (role_1) that you created earlier.

3.    Choose Add inline policy.

4.    Choose the JSON tab. Delete the existing policy and then enter an IAM policy similar to the following. Replace 444455556666 with the ID of the account that is using AWS Glue and Amazon S3. Replace role_2 with the name of the second role that you created.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Stmt1487639602000",
      "Effect": "Allow",
      "Action": [
        "sts:AssumeRole"
      ],
      "Resource": "arn:aws:iam::444455556666:role/role_2"
    }
  ]
}

5.    Choose Review policy.

6.    Enter a name for the policy, and then choose Create policy.

7.    Connect to the Amazon Redshift cluster.

8.    Create an external schema using the roles that you created in both accounts, as shown in the following example. Replace the following values:
your_db: the name of your database in AWS Glue.
111122223333: the ID of the Amazon Redshift account.
role_1: the name of the role that you created in the Amazon Redshift account.
444455556666: the ID of the AWS Glue and Amazon S3 account.
role_2: the name of the role that you created in the AWS Glue and Amazon S3 account.

create external schema spectrum_schema
from data catalog
database 'your_db'
iam_role 'arn:aws:iam::111122223333:role/role_1,arn:aws:iam::444455556666:role/role_2'

You can now query AWS Glue tables in account 444455556666 using Amazon Redshift Spectrum from your Amazon Redshift cluster in account 111122223333, as long as all resources are in the same Region. You don’t need to recreate your external tables because Amazon Redshift Spectrum can access your existing AWS Glue tables.

The query in the following example returns the number of rows in an AWS Glue table that was created in the external schema. Replace spectrum_schema with the name of your schema. Replace glue_table with the name of your AWS Glue table.

select count(*) from spectrum_schema.glue_table;

Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2019-02-18