How do I resolve access denied errors using 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 on the AWS Glue and S3 services. If the IAM role doesn't have the correct permissions, then you might receive any of the following errors:

When creating an external schema:

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 trying to query a Redshift Spectrum table:

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 trying to query a Redshift Spectrum table and the S3 bucket is using a Key Management Services (AWS KMS) encryption key:

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 used by Amazon Redshift. If the S3 bucket is encrypted using a KMS key, you must also attach permissions to use the key.

Resolution

Attach the AWS managed policy AWSGlueConsoleFullAccess to the IAM role that's attached to the Redshift cluster.

Create an IAM policy using the following example and attach the policy to the IAM role that's attached to the Redshift cluster. This allows read access to the S3 bucket where the data is stored:

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

{
  "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>"
      ]
    }
  ]
}

If the S3 bucket that Redshift Spectrum is using is encrypted using an AWS KMS encryption key, then create and attach the following IAM policy. Attach the policy to the IAM role that's attached to the Redshift cluster. This policy provides access so that Redshift Spectrum can decrypt the encrypted data in Amazon S3. The following is an example of the minimum permissions to allow decryption:

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

{
  "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>"
      ]
    }
  ]
}

---

Related information

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

IAM policies for Amazon Redshift Spectrum