Transfer files from s3 bucket to amazon RDS database

Question

I am trying to load data from the s3 bucket to amazon RDS database. I know this is not the programming question. But I really appreciate help. I have used the code below:

aws rds restore-db-instance-from-s3 ^
--allocated-storage 250 ^ 
--db-instance-identifier myidentifier ^
--db-instance-class db.m4.large ^
--engine mysql ^
--master-user-name masterawsuser ^
--master-user-password masteruserpassword ^
--s3-bucket-name mybucket ^
--s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename ^
--s3-prefix bucketprefix ^
--source-engine mysql ^
--source-engine-version 5.6.27

But I am getting the below error, though I have given correct ARN number:

“An error occurred (InvalidParameterValue) when calling the   RestoreDBInstanceFrom S3 operation: IAM role ARN value is invalid or does not include the   required permissions for: S3_SNAPSHOT_INGESTION”

Any comments on this?

Thanks

Rudderpost

Are you saying the role you have specified actually has the required permissions for S3_SNAPSHOT_INGESTION? – Invisible

@MarkB, yes!! It has all the permissions for S3_SNAPSHOT_INGESTION. – Rudderpost

While the official page for restore-db-instance-from-s3 doesn't provide a lot of information Import RDS from S3, you can follow Importing Data into an Amazon RDS MySQL DB Instance that lists specific IAM policies for importing from S3. – Anapest

Answer 1

I'm late to the game, but this is the #1 hit on Google and I spent about an hour figuring this problem out.

That error message is a bit misleading. It has nothing to do with the role or policies that you have attached to RDS. In my case, the account I was logged in as did not use a role with the proper permissions. In AWS IAM, I added the policies AmazonS3FullAccess and AmazonRDSFullAccess to my user account (well, actually added them to my assumed role because I'm using a Federated Corporate Access).

Once I did that, the error message disappeared and I was able to restore from S3

Cafeteria

Thanks, that helped a lot and saved some time. – Anesthetize

In my case, it was an issue with the role that I gave to RDS. That role did not have the proper Trust Relationship. I needed to make sure "rds.amazonaws.com" was in the JSON {"Version": "2012-10-17","Statement": [{"Effect": "Allow","Principal": {"Service": "rds.amazonaws.com"},"Action": "sts:AssumeRole"}]} – Plonk

I had a trust relationship to EC2 but not to RDS, so I had to set up an array in there instead: {"Version": "2012-10-17","Statement": [{ "Effect": "Allow","Principal": {"Service": ["ec2.amazonaws.com","rds.amazonaws.com"]},"Action": "sts:AssumeRole"}]} – Schach

Answer 2

Make sure you have the proper trust relationships for your role. Service export.rds.amazonaws.com is not enough, I had to add the rds.amazonaws.com service too.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "export.rds.amazonaws.com",
                    "rds.amazonaws.com"
                ]
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Thanks to Joseph Shih.

Aught