CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-auroraselect-into-outfile
Evan
Evan

Reputation: 284

Export data from Amazon Aurora to S3 using outfile?

I am trying to export data from a table in my Aurora database to a bucket I have created in my S3, using the "SELECT INTO OUTFILE S3" command that is promoted by Amazon here.

Select * from testauroradb.'table1' into outfile S3 's3://data-dump-bucket/Data';

When I try to run the above line I receive the following error:

Error Code: 1045. Access denied for user 'username'@'%' (using password: YES)

According to some forms this is due to the user not having permissions and that they need to be granted by the root user. However this is the root user and according to some documentation provided by Amazon, the root user has the ability to perform the "SELECT INTO S3" command. I have also checked and can verify that the user does have the ability to run the "SELECT INTO S3" command. (I know it is not good practice to use the root user but this is only a test database).

I also created an IAM role and policy to have access to the S3 and have linked it to the Aurora Database. Policy for access to S3:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:ListBucket",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
                "arn:aws:s3:::data-dump-bucket/Data"
            ]
        }
    ]
}

I attached the policy to an IAM role. Then I added the Role ARN to the parameter aws_default_s3_role in the parameter group that is attached to the Aurora Cluster.

Following some forums only, some people had success changing the outbound rules for the security groups to "TYPE:SSH, Port:22, Destination:0.0.0.0/0". But this didn't work for me either. If anyone can tell me what to do or what I have done wrong I would appreciate it.

Upvotes: 3

Views: 3480

Answers (2)

user11630754
user11630754

Reputation: 1

Run this command on you database

GRANT LOAD FROM S3 ON . TO 'user'@'domain-or-ip-address'

Upvotes: 0

user3897126
user3897126

Reputation: 11

I was running into the same issue. I added AmazonS3FullAccess policy to my IAM role and it worked.

Upvotes: 1

Related Questions