title: user is not authorized to assume IAM Role
body: <p>I have a bash script which pulls information from a CloudFormation stack and places it into a Python script. The Python script is intended to connect to a Redshift database, create a table, then import data from a file downloaded from an S3 bucket. </p>
  
  <p>The first part works:</p>
  
  <pre><code>endpoint=$(aws cloudformation describe-stacks --stack-name=BallotOnlineRS --region=us-west-2 \
              --query 'Stacks[0].Outputs[?OutputKey==`RedshiftClusterEndpointAddress`].OutputValue' \
              --output text)
  
  role=$(aws cloudformation describe-stack-resources --stack-name=BallotOnlineRS --region=us-west-2 \
              --logical-resource-id RawDataBucketAccessRole |grep 'PhysicalResourceId' |cut -d: -f2 |sed 's/^ //; s/,//; s/"//g')
  
  sed -i "s/ENDPOINT/${endpoint}/; s/ROLE/${role}/" /tmp/ballotonline_data.py
  </code></pre>
  
  <p>This results in the the necessary strings going where they need to:</p>
  
  <pre><code>HOST = 'ballotonliners-redshiftcluster-xxxxxxxxxxxx.cgsdneukfjtv.us-west-2.redshift.amazonaws.com'
  
  ARN_CREDENTIALS = 'arn:aws:iam::xxxxxxxxxxx:role/BallotOnlineRS-RawDataBucketAccessRole-xxxxxxxxxxxx
  </code></pre>
  
  <p>However, when I execute the Python script, I get the following error from <code>sqlalchemy</code>:</p>
  
  <pre><code>sqlalchemy.exc.InternalError: (psycopg2.errors.InternalError_) User arn:aws:redshift:us-west-2:xxxxxxxxxxx:dbuser:ballotonliners-redshiftcluster-1qb2hxkta10t9/admin is not authorized to assume IAM Role arn:aws:iam::xxxxxxxxxxx:role/BallotOnlineRS-RawDataBucketAccessRole-1U56NHFY528VW
  DETAIL:
    -----------------------------------------------
    error:  User arn:aws:redshift:us-west-2:xxxxxxxxxxx:dbuser:ballotonliners-redshiftcluster-xxxxxxxxxxxx/admin is not authorized to assume IAM Role arn:aws:iam::xxxxxxxxxxx:role/BallotOnlineRS-RawDataBucketAccessRole-xxxxxxxxxxxx
    code:      8001
    context:   IAM Role=arn:aws:iam::xxxxxxxxxxx:role/BallotOnlineRS-RawDataBucketAccessRole-xxxxxxxxxxxx
    query:     76
    location:  xen_aws_credentials_mgr.cpp:272
    process:   padbmaster [pid=13823]
    -----------------------------------------------
  </code></pre>
  
  <p>The <code>admin</code> user mentioned in the error is created when the Redshift stack is created and if I access the database through the Redshift UI by logging into the Query Editor, I get no error using the same account. The difference in that situation is that I'm logging in with the username:password rather than, I'm guessing, assuming a role. Specifically, the one in the error.</p>
  
  <p>The Redshift stack is built using a template <a href="https://s3.amazonaws.com/thorntech-public-documents/cf-templates/redshift.yml" rel="nofollow noreferrer">provided by Thorntech</a>. This also is successful when executed.</p>
  
  <p>Any thoughts on what the issue is?</p>
  
  <p><strong>EDIT:</strong>
  I found what should be an identical issue on the <a href="https://forums.aws.amazon.com/thread.jspa?messageID=759810" rel="nofollow noreferrer">AWS Developer forum</a> that indicates that rather than using the physical resource ID, I should be using the logical ID. In this case, instead of <code>BallotOnlineRS-RawDataBucketAccessRole-xxxxxxxxxxxx</code> I should just use <code>RawDataBucketAccessRole</code>. However, that results in the same error with difference being that the logical ID replaces the physical ID.</p>
  
  <p><strong>EDIT2:</strong>
  In response to John Rotenstein's question regarding associating the role, here is the section that defines the cluster and its relevant components:</p>
  
  <pre><code>Resources:
    RedshiftCluster: 
      Type: AWS::Redshift::Cluster
      Properties: 
        ClusterSubnetGroupName: !Ref RedshiftClusterSubnetGroup
        ClusterType: !If [ SingleNode, single-node, multi-node ]  
        NumberOfNodes: !If [ SingleNode, !Ref 'AWS::NoValue', !Ref RedshiftNodeCount ] #'
        DBName: !Sub ${DatabaseName}
        IamRoles:
          - !GetAtt RawDataBucketAccessRole.Arn
        MasterUserPassword: !Ref MasterUserPassword
        MasterUsername: !Ref MasterUsername
        PubliclyAccessible: true
        NodeType: dc2.large
        Port: 5439
        VpcSecurityGroupIds: 
          - !Sub ${RedshiftSecurityGroup}
        PreferredMaintenanceWindow: Sun:09:15-Sun:09:45
    DataBucket:
      Type: AWS::S3::Bucket
      Properties:
        BucketName: !Sub ${DataBucketName}
    RawDataBucketAccessRole:
      Type: AWS::IAM::Role
      Properties:
        AssumeRolePolicyDocument:
          Version: 2012-10-17
          Statement: 
            - 
              Effect: Allow
              Principal:
                Service:
                  - redshift.amazonaws.com
              Action:
                - sts:AssumeRole
    RawDataBucketRolePolicy:
      Type: AWS::IAM::Policy
      Properties:
        PolicyName: RawDataBucketRolePolicy
        PolicyDocument: 
          Version: 2012-10-17
          Statement:
            - 
              Effect: Allow
              Action: s3:ListAllMyBuckets
              Resource: arn:aws:s3:::*
            - 
              Effect: Allow
              Action: 
                - 's3:Get*'
                - 's3:List*'
              Resource: '*'
            - 
              Effect: Allow
              Action: cloudwatch:*
              Resource: "*"
        Roles:
          - !Ref RawDataBucketAccessRole  
  </code></pre>
  
  <p>Does it matter that the cluster is defined prior to the role? I suspect it doesn't since the role is listed under the stack's Resources tab in the CF UI.</p>
  
  <p><strong>EDIT3:</strong>
  I found another, <a href="https://discourse.snowplowanalytics.com/t/data-loading-error-to-redshift/1559" rel="nofollow noreferrer">similar issue</a> but for a product. The OP states that he found the solution. Being that he was using the wrong role to authenticate with. When I looked into that avenue for my issue, it does not appear to apply since I only have the one role.</p>