Resolving Permission Errors When Accessing S3 Buckets Using Amazon Redshift Spectrum and AWS Glue

Sometimes while working with Amazon Redshift Spectrum, users might try to access data stored in Amazon Simple Storage Service (Amazon S3) buckets within the same account and encounter some problems. In addition, AWS Glue might be used as the data catalog. This post will provide answers and solutions when you experience permission errors in this situation.

Understanding Permission Errors

In scenarios where the AWS Identity and Access Management (IAM) role that’s attached to the Redshift cluster does not have adequate permissions on the AWS Glue and S3 services, several errors may occur:

  1. While creating an external schema, users may encounter this error:

    SQL Error [XX000]: ERROR:

  2. Trying to query a Redshift Spectrum table can result in this error:

    SQL Error [XX000]: ERROR: Spectrum Scan Error

  3. If the S3 bucket uses a Key Management Services (AWS KMS) encryption key and you try to query a Redshift Spectrum table, this error may occur:

    SQL Error [XX000]: ERROR: Spectrum Scan Error

Resolving These Errors

The solution to these errors is to 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, users must also attach permissions to use the key.

Step 1: Create an IAM policy

The first step towards resolving these errors is to create an IAM policy and then attach it to the IAM role that is attached to the Redshift cluster. This policy will allow read access to the S3 bucket where the data is stored. Here is an example:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "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 bucket.

Step 2: Add Permissions for KMS Encrypted S3

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. Here’s an example of the minimum permissions to allow decryption:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "kms:Decrypt"
      ],
      "Resource": [
        "arn:aws:kms:<region>:<AWS account ID>:key/<KMS key ID>"
      ]
    }
  ]
}

Note: Replace “”, “”, “” with your respective details.

In conclusion, ensuring that the IAM role associated with your Redshift cluster has the right permissions with the AWS Glue, S3, and possibly KMS services, saves you a lot of trouble and prevents unexpected permission errors.

Tags: #AWSRedshift, #AmazonS3, #AWSGlue, #IAM

[Reference Link](!https://repost.aws/knowledge-center/redshift-resolve-access-denied-errors)