ERROR 1045 (28000): Access denied for user 'db_user'@'ip' (using password: YES) while connecting to a RDS DB instance using IAM DB Authentication

Question

Following is a quick summary of the question. Read the full description section for the underlying details.

Condensed description:

Assume you have an IAM user already existing and the user is already able to access other AWS services, such as S3, CloudFront, ECS, EC2...

Let's say we need to provide the user with read-only access over the RDS cluster and set up IAM DB Authentication as well.

We perform all the steps mentioned as per the official guide, in OUR local system and it works perfectly and we are able to generate correct auth token for db_user.

However, here is where it gets interesting.. when the user tries to generate the token for the db_user account, from their local machine.. the user will be denied access.

---

Full description:

Setup:

My RDS cluster instance runs the Aurora MySQL engine. Engine version: 5.6.10a

I've been following the AWS knowledge center guide on How do I allow users to connect to Amazon RDS with IAM credentials?

The guide doesn't explicitly mention but while generating the authentication token, AWS CLI uses IAM credentials stored locally, to sign the request.

I'd like to highlight that in the below-mentioned snippet, admin is the profile name stored by AWS CLI for my admin IAM user while the db_user is the IAM user (with rds-db:connect privileges).

TOKEN="$(aws --profile admin rds generate-db-auth-token -h.. .. .. -u db_user)

Using the above snippet I'm able to authenticate with the generated token and connect to the cluster.

If --profile attribute is not mentioned, it reads the default profile saved in the credentials file.

Issue:

Instead of using --profile admin I'm looking to use an already existing non-admin IAM profile for generating an authentication token.

For instance, assume IAM user named developer, with RDS read-only privileges and the credentials stored locally under the profile rds_read_only

TOKEN="$(aws --profile rds_read_only rds generate-db-auth-token -h.. .. .. -u db_user)

If I use the above token, I get the following error:

ERROR 1045 (28000): Access denied for user 'db_user'@'ip' (using password: YES)

After hours of troubleshooting, I was able to conclude that my rds_read_only profile is unable to generate valid authentication tokens probably because IAM user developer is missing some required policies.

I tried attaching all policies available under RDS and RDS Data API (individually as well as in combinations) to IAM user developer, without any luck. If I attach the AdministrativeAccess policy to IAM user developer, only then it is able to generate the token successfully.

Question:

What are the mandatory policies required for non-admin IAM users to generate an authentication token successfully?

Answer 1

i saw your question in AWS Blog.

1. You need to create an IAM policy to define access to your AWS RDS instances. Check this docs

   { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "rds-db:connect" ], "Resource": [ "arn:aws:rds-db:us-east-2:1234567890:dbuser:/" ] } ] }

2. Create User inside RDS DB instance with the instructions to use an IAM Plugin. Check this docs

3. Create the token check this docs

4. I found this nice plugin that builds a JDBC jar to allow IAM authentication.

Answer 2

This answers the specific question of @Ronnie regarding the Token generation.

Ronnie i am back. I used the following policy in my AWS Account: Sandbox I am an AWS Federated user with AssumeRole priveleges so Admin

You have to be very careful because as you said the article doesn't make the distinction from:

AWS IAM USER using a generated token to access the DB and

AWS IAM User/Role with the right Admin policies that generates a VALID Token

I will give an Example of how to identify the correct generated Tokens. For some reason AWS generates a value but it doesn't tell you whether is a useful token or not :-\

Token without admin special access= WILL NOT WORK

sandboxdb.asdasdffw.ap-southeast-2.rds.amazonaws.com:3306/?Action=connect&DBUser=human_user&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIA5XZIHS3GYVMRPRZF%2F20200424%2Fap-southeast-2%2Frds-db%2Faws4_request&X-Amz-Date=20200424T035250Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=3efd467d548ea05a8bdf097c132b03661680908f723861e45323723c870ef646

Token with Access= Will Work! Look carefully and it contains X-Amz-Security-Token=

sandboxdb.ras21th1z8.ap-southeast-2.rds.amazonaws.com:3306/?Action=connect&DBUser=human_user&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIA5XZIHS3GSAQI6XHO%2F20200424%2Fap-southeast-2%2Frds-db%2Faws4_request&X-Amz-Date=20200424T040756Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEEQaDmFwLXNvdXRoZWFzdC0yIkgwRgIhAOzVIondlMxYkJG5nWNeQlxS0M6B1pphgD1ewFwx2VfKAiEAkcp2jNHHmNMgwqUholnW545MwjzoEjS1uh4BHI4R4GAqvgMIbRABGgw5NDQ0NDAzMTc2NDUiDEvFkyEy833kd%2By4nyqbAybqK5dcP0nTlqZ19I2OVZxzwzz%2BUv9RVdVLMPHE5b%2FqXQGVG1CRtw90r9Lt4QkzTBeIVzdtIkXbpwFtqFh24Djb%2BiZHfvElj%2Fhz29ExzStU0fPYMewEB1u%2F2Osi72Fw6KbZ6TDy5EjuWcrrS08PZQ9CHc%2Fc8iDAIKs28vJ70KKcmow0SInVZGHGpD2JAgIL7jvnadVlcAW7lN2OAnxS72Kb4neqNuHcWzfPLfbXaOP1OaOs7vCR7zDlTTxX2aHoVflC69K9K67BqzdnDnnju%2F4XWQWU3r%2ByXylExwOsiG3y4Qq6wv002l%2BpQmF5%2BMXdTrFR5ewpfrcHf8TZLI5eq8HLA2gG1%2B255L%2Bqt%2BD80T%2FCzEdKSJPjppdYSq9FdeCMRSsqp5PpXP%2BDbQZwmhxiE2RmrbOKwNsFPJqUUnemQHXYLB8lily56nnswT2PYmQOGHqnZWRrv%2FTlGOAGlThuiR%2BLhQLBC08nBEGbBqK%2FjU4JwFMY4JfhgUHr8BA9CuGwAu0qIAFzG71M3HzCNX6o56k1gYJB%2F3%2FJaKlp7TCIxIn1BTrqASqywcfKrWhIaNX3t%2BV%2FZoYYO%2FtGVBZLyr3sSmByA%2Fwq538LiPHA0wDE3utOg%2FwNP%2BQGTcXhk1F%2BI0HOHztAQ2afnKW8r1oRbXxYAzb2j2b8MNEwrsaBju2gHFRgZHkM8YI%2FP5cvYr%2F8FQXWcE9eqjdme0hOo3rPETzxZfRwNQTHEntBbVVD1ec0d7DblfSEDZhLk%2By1%2BFMAYf7NeBIfU6GNsAN2hTdSkPPuto2fQKzRybRAwxQz5P3cO5CClUNIxu4J3bM1MUUTux%2BtMjqRvjGxDhB4yLIJmIPOOYLDSOXl3aWO2y4v89wu5A%3D%3D&X-Amz-Signature=1c6fcc472bb2af09055117075ca21d4a5f715910443115116c9230905721e79d

AWS IAM policy For DB User to Connect to AWS RDS DB Instance

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": "rds-db:connect",
            "Resource": "*"
        }
    ]
}

To avoid messing with local configs i used the following testing process:

1. Token generation from an AWS EC2 instance running in the same VPC as the DB. Generation is successful
2. Token generation from local machine by using a Docker container with AWS CLI Token generation is successful.

Of course my user was created in the MySQL DB with the following command

mysql -h $HOSTNAME -u$ADMIN_USER -p$ADMIN_PASS <<EOF
CREATE USER db_human_user IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT SELECT ON $SPECIFIC_GIVEN_DB.* TO 'db_human_user';
EOF