• About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
  • About Us
    • New York
  • Work
  • Capabilities
  • Careers
  • Technology
  • Blog
  • Contact Us
October 26, 2017

Cross-Account Redshift UNLOAD/COPY

Posted by Christopher Davis

At PMG, the dev team maintains several large applications that provide our account teams with the tools they need to be awesome. We host those tools on individual AWS accounts, but sometimes they need to talk with each other across accounts.

Most recently we had to implement a redshift UNLOAD from one AWS account to an S3 bucket in another. This is a guide on how to do that.

We’ll use two terms here:

  1. The S3 Account is the AWS account with the target S3 bucket to which redshift will unload.
  2. The Redshift Account contains the redshift cluster that will do the UNLOAD or COPY operation.

How This Works

The S3 Account has a bucket and bucket policy that allows the Redshift Account to access the bucket.

The Redshift Account has an IAM role that redshift assumes and grants permissions to access the bucket on the S3 Account.

S3 Bucket Policies

A bucket policy allows an AWS account to grant third-party accounts or other principals access to the bucket.

Think of a principal as a form of a user. In a bucket policy, a principal can say things like, “grant access to any IAM entity on Account X.” In our case, the S3 Account will allow the Redshift Account to access the target bucket.

The above allows any IAM entity on the Redshift Account to access the target bucket (named CHANGEME in this example). That’s a bit too permissive for most cases, so let’s lock it down to a single IAM role.

Now the only IAM entity on the Redshift Account that can access the bucket is the specific role defined here.

The AWS CLI can be used to set bucket policies or the UI can.

Redshift Roles & IAM Permissions

The above bucket policy only allows the cross-account access to take place; the Redshift Account still needs permissions from its own IAM system to do so. To do that we’ll create an IAM role in the Redshift Account that Redshift can assume and that grants appropriate permissions to act on the S3 bucket.

AWS has plenty of documentation on how to create a role for Redshift, so we’ll skip that here. One interesting thing I’d like to point out is that roles can be restricted to certain database users. That’s super powerful if an automated system is talking to redshift and no others should be allowed to assume a given role.

The role needs permissions to act on the target S3 bucket.

The above could be in a managed or inline policy.

Once the roles and permissions are set up, you can associate the role with the cluster so it can be used.

Finally UNLOAD

With all that done, we’re finally ready to actually unload some data!

The above unloads a SELECT 1 statement to the target bucket. The CREDENTIALS clause says to use the IAM role created above.

Recap

  1. Create a bucket in the S3 Account
  2. Add a bucket policy to that bucket that allows the Redshift Account access
  3. Create an IAM role in the Redshift Account that redshift can assume
  4. Grant permissions to access the S3 Bucket to the newly created role
  5. Associate the role with the Redshift cluster
  6. Run some UNLOAD (or COPY) statements

Since the author is in love with terraform, here is some example terraform configuration for all of the above.

Interested in working with us? See our open engineering roles here.

awsredshifts3
Previous
Next

Latest White Papers

  • Shifting Plans for 2020 & Beyond
  • Game On: How Brands Can Log Into A Diverse Multi-Billion Dollar Industry
  • What CCPA Means For Brands
  • How Google is Improving Consumer Data Privacy
  • Ways to Prepare for the Cookieless Future
  • See all White Papers

Featured Posts

  • Ad Age Names PMG #1 Best Place to Work in 2021
  • Hindsight 2020 & Looking Ahead to 2021
  • Preparing for Streaming’s Growth & The Future of TV Buying
  • MediaPost Names PMG Independent Agency of the Year
  • PMG Client Portfolio Trends During Amazon Prime Day 2020

Categories

  • Consumer Insights
  • Content
  • Creative Design
  • Data Analytics
  • Development
  • Digital TV & Video
  • Ecommerce
  • Industry News
  • Local
  • Mobile
  • Paid Search
  • PMG Culture
  • Programmatic & Display
  • SEO
  • Social Media
  • Structured Data
Fort Worth

2845 West 7th Street
Fort Worth, TX 76107

Dallas

3102 Oak Lawn Avenue
Suite 650
Dallas, TX 75219

Austin

823 Congress Avenue
Suite 800
Austin, TX 78701

London

33 Broadwick Street
London
W1F 0DQ

New York

120 East 23rd Street
New York, NY 10010

Get in touch

(817) 420 9970
info@pmg.com

Subscribe to the PMG Newsletter
© 2021 PMG Worldwide, LLC, All Rights Reserved
  • Contact
  • Privacy Policy
 Tweet
 Share
 Tweet
 Share
 Tweet
 Share
 LinkedIn
We and our partners use cookies to personalize content, analyze traffic, and deliver ads. By using our website, you agree to the use of cookies as described in our Cookie Policy.