CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-redshift
rhonda
rhonda

Reputation: 99

Redshift Scheduler unable to create schedule

My AWS has 2 different Users: admin, s3_readonly

I am the main admin and have 1 cluster in Redshift(cluster1). Now I am trying to schedule a query that just calls those procedures every hour (CALL <procedure_name>)

For this task, I have followed the official documentation from AWS (Scheduling a query on the Amazon Redshift console - Amazon Redshift) and to be exact this document steps (Scheduling SQL queries on your Amazon Redshift data warehouse | AWS Big Data Blog).

So I created new IAM role RedshiftScheduler, which has Redshift Customizable option and have attached AmazonRedshiftDataFullAccess to it. Then I edited the Trust relationship and added:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Sid": "S2",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::<ACCOUNT_ID>:user/admin"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Sid": "S1",
      "Effect": "Allow",
      "Principal": {
        "Service": "events.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

I then went back to my AWS user (admin) and attached a new policy granted with Assume Role permissions:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "S3",
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<ACCOUNT_ID>:role/RedshiftScheduler"
        }
    ]
}

Now, I logged in to the Redshift cluster via AWS service. Used Temporary credentials to connect to cluster1 and user as dbuser. However, when I try to schedule the query it throws an error

To view the schedule history of this schedule, add sts:AssumeRole for IAM role arn:aws:iam::<ACCOUNT_ID>:role/RedshiftScheduler to your IAM role. You also need to add your IAM user ARN to the role’s trust policy.

Upvotes: 5

Views: 4918

Answers (1)

Bek
Bek

Reputation: 21

You need to add your IAM user ARN to the role’s trust policy like this

{
    "Effect": "Allow",
    "Principal": {
        "AWS": "arn:aws:iam::<account #>:user/<admin username"
    },
    "Action": "sts:AssumeRole",
    "Condition": {}
}

after

{
    "Sid": "S1",
    "Effect": "Allow",
    "Principal": {
        "Service": "events.amazonaws.com"
    },
    "Action": "sts:AssumeRole"
}

Upvotes: 2

Related Questions