DEV Community

Wakeup Flower
Wakeup Flower

Posted on

Redshift cross account access S3

#aws

🔹 What is Redshift UNLOAD?

  • UNLOAD is a Redshift SQL command that exports the results of a query from your Redshift cluster into files stored in Amazon S3.
  • It’s basically the opposite of COPY (which loads data into Redshift from S3).
  • Use case: If you run a query in Redshift and want to save the results in S3 for downstream analytics, backup, or sharing across accounts, you run something like: 
UNLOAD ('SELECT * FROM sales')
TO 's3://my-bucket/unloads/sales_'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
FORMAT AS PARQUET;
Enter fullscreen mode Exit fullscreen mode

That creates files in S3 (sales_0000, sales_0001, …) with your query results.

⚠️ Problem: By default, the objects are owned by the AWS account that Redshift belongs to (the “uploader”), not the bucket owner. That’s why access can break without cross-account setup.

âś… The Solution (Cross-Account Role Setup)

  1. In the S3 bucket account (your account):
  • Create an IAM role (“Bucket Role”) with permissions to read/write the bucket.
  1. In the Redshift account (other account):
  • Create an IAM role (“Cluster Role”) that allows Redshift to assume the “Bucket Role.”
  1. Trust Relationship:
  • Update the Bucket Role so that the Cluster Role is trusted and allowed to use it.
  1. Run UNLOAD with both roles:
  • Now when Redshift unloads, it’s using your permissions, so the files land in the bucket owned by you (bucket account), not by Redshift’s account.

🔹 Modern Alternative: S3 Object Ownership (Bucket Owner Enforced)

AWS introduced Object Ownership in 2020 to solve exactly this problem.

  • With Bucket owner enforced, S3 automatically assigns ownership of all objects in your bucket to the bucket owner, regardless of who uploaded them.

  • This means:

    • No more dealing with ACLs.
    • No more worrying about Redshift (or another account) owning the files.
    • As long as Redshift can write to the bucket, you’ll always be the object owner.

âś… How to enable it

  1. Go to your bucket in the S3 console.
  2. Open Permissions → Object Ownership.
  3. Select Bucket owner enforced.
  • This disables ACLs completely and forces bucket-owner ownership.

Or via CLI:

aws s3api put-bucket-ownership-controls \
  --bucket my-bucket \
  --ownership-controls 'Rules=[{ObjectOwnership=BucketOwnerEnforced}]'
Enter fullscreen mode Exit fullscreen mode

🔹 Summary

  • Redshift UNLOAD = export query results to S3.
  • Old fix = cross-account IAM role trust setup (Cluster Role + Bucket Role).
  • New fix (preferred) = enable S3 Object Ownership (Bucket owner enforced) so the bucket owner always owns the objects — no ACLs, no headaches.

⚠️ Exception

  • If the S3 bucket or Redshift uses server-side encryption with AWS KMS (SSE-KMS), this method doesn’t work by default, because KMS introduces another layer of key policies. That requires extra KMS permissions setup.

Top comments (0)