How do I troubleshoot issues with UNLOAD in Amazon Redshift?
Last updated: 2020-09-25
Short description
When unloading data from your Amazon Redshift cluster to your Amazon S3 bucket, you might encounter the following errors:
DB user is not authorized to assume the AWS Identity and Access Management (IAM) Role error
error: User arn:aws:redshift:us-west-2:<account-id>:dbuser:<cluster-identifier>/<dbuser username> is not authorized to assume IAM Role arn:aws:iam::<account-id>:role/<Role name> 4
403 Access Denied error
[Amazon](500310) Invalid operation: S3ServiceException:Access Denied,Status 403,Error AccessDenied,
Note: Use the UNLOAD command with the SELECT statement when unloading data to your S3 bucket. Unload the text data in either a delimited or fixed-width format (regardless of the data format used while being loaded). You can also specify whether a compressed Gzip file should be filed.
Resolution
DB user is not authorized to assume the AWS IAM Role error
If the database user isn't authorized to assume the IAM role, check the following:
- Verify that the IAM role is associated with your Amazon Redshift cluster.
- Verify that there are no trailing spaces in the IAM role used in the UNLOAD command.
- Verify that the IAM role assigned to the Amazon Redshift cluster is using the correct trust relationship.
403 Access Denied error
If you receive a 403 Access Denied error from your S3 bucket, confirm that the proper permissions are granted for your S3 API operations:
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"s3:Get*",
"s3:List*",
"s3:PutObject"
],
"Resource": [
"arn:aws:s3:::your_bucket",
"arn:aws:s3:::your_bucket/*"
]
}
]
}
If you are using server-side encryption with S3-managed encryption keys, then your S3 bucket encrypts each of its objects with a unique key. As an additional safeguard, the key itself is also encrypted with a master key that is regularly rotated.
To encrypt the unloaded data at rest, verify that the s3:x-amz-server-side-encryption parameter in your S3 bucket policy is set to "true":
"Condition": {
"Null": {
"s3:x-amz-server-side-encryption": "true"
}
Confirm that KMS_KEY_ID is encrypted so that you can UNLOAD the encrypted data to your S3 bucket:
unload ('select * from a') to 's3://mybucket/test/varchar/' iam_role 'arn:aws:iam::xxxxxx:role/RedshiftS3Access' ALLOWOVERWRITE CSV
KMS_KEY_ID '11111111111111111111111111'
encrypted;
If your S3 bucket is encrypted with a master key from AWS Key Management Service (AWS KMS), then add these permissions to your IAM policy:
"Action": [
"kms:Encrypt",
"kms:Decrypt",
"kms:ReEncrypt*",
"kms:GenerateDataKey*",
"kms:DescribeKey"
]
Related information
Did this article help?
Do you need billing or technical support?