I created a cloudformation stack with redshift cluster and a masteruser: testuser
"RedshiftCluster" : {
"IamRoles" : [
{
"Fn::GetAtt": [
"IAMInstanceRole",
"Arn"
]
}
]
... other configurations
It uses the below IAM role (IAMInstanceRole) which is in in-sync status and the redshift cluster is up and running:
"IAMInstanceRole": {
"Properties": {
"RoleName": "test-iam-role",
"AssumeRolePolicyDocument": {
"Statement": [
{
"Action": [
"sts:AssumeRole"
],
"Effect": "Allow",
"Principal": {
"Service": [
"ec2.amazonaws.com",
"redshift.amazonaws.com",
"s3.amazonaws.com"
]
}
}
]
},
"Path": "/",
"Policies": [
{
"PolicyName": "root",
"PolicyDocument": {
"Version" : "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "*",
"Resource": "*"
}
]
}
}
]
}
I'm trying to load csv file from s3 to redshift using copy command and iam_role as credential. The iam_role has the arn of IAMInstanceRole (declared above). Whenever I execute the below command:
copy test_table from 's3://test-bucket/test.csv' CREDENTIALS 'aws_iam_role=arn:aws:iam::<account-id>:role/test-iam-role' MAXERROR 100000 removequotes TRIMBLANKS emptyasnull blanksasnull delimiter '|';
I get the error:
ERROR: User arn:aws:redshift:us-west-2:189675173661:dbuser:automated-data-sanity-redshiftcluster-fbp9fgls6lri/sanityuser is not authorized to assume IAM Role arn:aws:iam::189675173661:role/sanity-test-iam-instance-role
DETAIL:
-----------------------------------------------
error: User arn:aws:redshift:us-west-2:<account-id>:dbuser:test-redshiftcluster-fbp9fgls6lri/testuser is not authorized to assume IAM Role arn:aws:iam::<account-id>:role/test-iam-role
code: 8001
context: IAM Role=arn:aws:iam::<account-id>:role/test-iam-role
query: 1139
location: xen_aws_credentials_mgr.cpp:236
process: padbmaster [pid=29280]
-----------------------------------------------
Please suggest some resolution.
I ran into the same problem but after a good 1 hour of troubleshooting, I realised I had failed to add the Redshift role to the cluster while I was creating it. If you select the cluster from Redshift, choose the drop-down on 'Actions' and select 'Manage IAM roles' from there you will be able to attach the Redshift role you may have created for this cluster.
That solved the problem for me, anyways. Hope this helps.
I resolved this issue !! By default, IAM roles that are available to an Amazon Redshift cluster are available to all users on that cluster. You can choose to restrict IAM roles to specific Amazon Redshift database users on specific clusters or to specific regions.
To permit only specific database users to use an IAM role, take the following steps.
To identify specific database users with access to an IAM role
Identify the Amazon Resource Name (ARN) for the database users in your Amazon Redshift cluster. The ARN for a database user is in the format: arn:aws:redshift:region:account-id:dbuser:cluster-name/user-name.
Open the IAM Console at url="https://console.aws.amazon.com/.
In the navigation pane, choose Roles.
Choose the IAM role that you want to restrict to specific Amazon Redshift database users.
Choose the Trust Relationships tab, and then choose Edit Trust Relationship. A new IAM role that allows Amazon Redshift to access other AWS services on your behalf has a trust relationship as follows:
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": "sts:AssumeRole"
}
]
}
For example, the following trust relationship specifies that only database users user1 and user2 on cluster my-cluster in region us-west-2 have permission to use this IAM role.
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": "redshift.amazonaws.com"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": [
"arn:aws:redshift:us-west-2:123456789012:dbuser:my-cluster/user1",
"arn:aws:redshift:us-west-2:123456789012:dbuser:my-cluster/user2"
]
}
}
}]
}
7.Choose Update Trust Policy.
Here's a template that works fine:
{
"AWSTemplateFormatVersion": "2010-09-09",
"Resources": {
"RedshiftRole": {
"Type": "AWS::IAM::Role",
"Properties": {
"RoleName": "Redshift-Role",
"AssumeRolePolicyDocument": {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"Service": [
"redshift.amazonaws.com"
]
},
"Action": [
"sts:AssumeRole"
]
}
]
},
"Path": "/",
"Policies": [
{
"PolicyName": "root",
"PolicyDocument": {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": "s3:*",
"Resource": "*"
}
]
}
}
]
}
},
"RedshiftSG": {
"Type": "AWS::EC2::SecurityGroup",
"Properties": {
"GroupName": "Redshift Security Group",
"GroupDescription": "Enable access to redshift",
"VpcId": "vpc-11223344",
"SecurityGroupIngress": [
{
"IpProtocol": "tcp",
"FromPort": 5439,
"ToPort": 5439,
"CidrIp": "0.0.0.0/0"
}
],
"Tags": [
{
"Key": "Name",
"Value": "Redshift Security Group"
}
]
}
},
"RedshiftCluster": {
"Type": "AWS::Redshift::Cluster",
"Properties": {
"ClusterType": "single-node",
"NodeType": "dc2.large",
"MasterUsername": "master",
"MasterUserPassword": "YourPassword",
"IamRoles": [
{
"Fn::GetAtt": [
"RedshiftRole",
"Arn"
]
}
],
"VpcSecurityGroupIds": [
{
"Ref": "RedshiftSG"
}
],
"PubliclyAccessible": true,
"Port": 5439,
"DBName": "foo"
}
}
}
}
Be sure to insert your own VpcId in the security group.
The Role can be assumed by Redshift and grants access to s3:* (which you should reduce in scope).