How do I resolve access denied errors in Redshift Spectrum with Amazon S3 buckets in the same account as Amazon Redshift?

Short description

The AWS Identity and Access Management (IAM) role that's attached to the Redshift cluster must have permissions for AWS Glue and Amazon S3. If the IAM role doesn't have the correct permissions, then you might receive an error message.

When you create an external schema, you might receive the following error message:

"SQL Error [XX000]: ERROR: User: arn:aws:sts::111111111111:assumed-role/KCARole/RedshiftIamRoleSession is not authorized to perform: glue:CreateDatabase on resource: arn:aws:glue:eu-west-1:111111111111:catalog because no identity-based policy allows the glue:CreateDatabase action"

When you query a Redshift Spectrum table, you might receive the following error message:

"SQL Error [XX000]: ERROR: Spectrum Scan Error Detail: ----------------------------------------------- error: Spectrum Scan Error code: 15007 context: Forbidden: HTTP response error code: 403 Message: AccessDenied Access Denied"

When you query a Redshift Spectrum table and the S3 bucket uses an AWS Key Management Services (AWS KMS) encryption key, you might receive the following error message:

"SQL Error [XX000]: ERROR: Spectrum Scan Error Detail: ----------------------------------------------- error: Spectrum Scan Error code: 15007 context: Forbidden: HTTP response error code: 403 Message: AccessDenied The ciphertext refers to a customer master key that does not exist, does not exist in this region, or you are not allowed to access"

To resolve these errors, you must attach an IAM policy with the required permissions to the IAM role that Amazon Redshift uses. If you used an AWS KMS key to encrypt the S3 bucket, then you must also add permissions to use the key.

Resolution

Create an IAM policy. Then, attach the following policies to the IAM role that's attached to the Redshift cluster.

Attach the AWSGlueConsoleFullAccess AWS managed policy.

Use the following example policy that allows read access to the S3 bucket where you store data:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Allows Redshift to Read S3 bucket specified",
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::bucket name/*",
        "arn:aws:s3:::bucket name"
      ]
    }
  ]
}

Note: Replace bucket name with the name of your S3 bucket.

For an AWS KMS key encrypted bucket, attach the following IAM policy that provides the minimum permissions to allow Redshift Spectrum to decrypt the encrypted data in Amazon S3:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Allow Redshift to use the KMS key",
      "Effect": "Allow",
      "Action": [
        "kms:Decrypt",
        "kms:GenerateDataKey"
      ],
      "Resource": [
        "arn:aws:kms:region:AWS account ID:key/KMS key ID"
      ]
    }
  ]
}

Note: Replace region with the AWS Region that your S3 bucket is located in. Also, replace AWS account ID with your account ID and KMS key ID with the KMS encryption key.

Related information

How can I create Amazon Redshift Spectrum cross-account access to AWS Glue and Amazon S3?

IAM policies for Amazon Redshift Spectrum