Connecting to RDS MySQL DB instance without a password, using IAM authentication - 2
Tags: rds, ec2, iam
Published: 2023-08-15 16:55:14

Please refer to the 1st part of this article
[Connecting to RDS MySQL DB instance without a password, using IAM authentication - 1](https://dev.to/aws-builders/connecting-to-rds-mysql-db-instance-without-a-password-using-iam-authentication-1-2b80)


Please visit my **[GitHub Repository for RDS articles](https://github.com/awsmine/RDS_projects)** on various topics being updated on constant basis.

_**Let’s get started!**_

- 2nd part - from Objectives **6.** to **13.**

# Objectives:


**6.** Create a DB user ***`rev`*** account that uses an AWS authentication token

**7.** Create an IAM role that allows Amazon RDS access.***`R-iam-rds-role`*** with AmazonRDSReadOnlyAccess policy

**8.** Create an IAM policy ***`P-iam-rds-policy`*** that maps the DB user to the IAM role

**9.** Attach the IAM role to the EC2 instance

**10.** Download the SSL root certificate file or certificate bundle file
download the root certificate that works for all Regions:

**11.** Generate an AWS authentication token to identify the IAM role

**12.** Connect to the RDS MySQL database using IAM role credentials and the authentication token and SSL certificates.

**13.** Status of the SSL connection.


# Pre-requisites:
- AWS user account with admin access, not a root account.

# Resources Used:

- [How do I allow users to authenticate to an Amazon RDS for MySQL DB instance using their IAM credentials?](https://repost.aws/knowledge-center/users-connect-rds-iam)

- [IAM database authentication for MariaDB, MySQL, and PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.html)

- [Connecting to your DB instance using IAM authentication](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.html)

- [Connecting to your DB instance using IAM authentication from the command line: AWS CLI and mysql client](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.html)

- [IAM Authentication with Amazon Aurora MySQL](https://www.youtube.com/watch?v=y2QsuwZGZ54)

# Steps for implementation to this project:

## 6. Create a DB user ***`rev`*** account that uses an AWS authentication token

- SSH into EC2 instance using putty
- install awscliv2
```
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
sudo ./aws/install

[ec2-user@ip-172-31-39-20 ~]$ /usr/local/bin/aws --version
aws-cli/2.13.9 Python/3.11.4 Linux/5.10.184-175.749.amzn2.x86_64 exe/x86_64.amzn.2 prompt/off

```

- install mysql
```
sudo su
yum install mysql
```
- connect to RDS MySQL Instance
```
mysql -h database-1.cgizjtuyxkda.us-east-1.rds.amazonaws.com -P 3306 -u admin -p
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyjewp6jnm99qo6qn3xk.png)

- Create DB user ***`rev`***
- With MariaDB and MySQL, authentication is handled by AWSAuthenticationPlugin—an AWS-provided plugin that works seamlessly with IAM to authenticate your users. 
- The IDENTIFIED WITH clause allows MariaDB and MySQL to use the AWSAuthenticationPlugin to authenticate the database account (rev). 
- The AS 'RDS' clause refers to the authentication method. 

```
CREATE USER rev IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
``` 

- By default, the database user is created with no privileges. This appears as GRANT USAGE when you run SHOW GRANTS FOR {dbusername}. 
- To require a user account to connect using SSL, run this command:

```
ALTER USER {dbusername} REQUIRE SSL;

ALTER USER rev REQUIRE SSL;
```
- check to see the DB user ***`rev`***has been created

```
select host, user, plugin, authentication_string from mysql.user where user='rev';
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/55oc9g6o6grx79xh7p2u.png)



## 7. Create an IAM role that allows Amazon RDS access - ***`R-iam-rds-role`*** with AmazonRDSReadOnlyAccess policy


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xeffszwtufgsprqayijl.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hteccjvdsj56gnjynvoh.png)




## 8. Create an IAM policy ***`P-iam-rds-policy`*** that maps the DB user to the IAM role

- take note of DB ResourceID
```
db-ZHJ3WUBV6WF5XAU5XYLQU3ME5M
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6d0m5p6961uff5lmickh.png)

- Create an IAM policy ***`P-iam-rds-policy`***

```
{
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:us-east-1:273978150254:dbuser:db-ZHJ3WUBV6WF5XAU5XYLQU3ME5M/rev"
         ]
      }
   ]
}
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ht8chl0cj7w4r28muq2q.png)

- attach ***`P-iam-rds-policy`*** to ***`R-iam-rds-role`***


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mlqtv415ahe3poh90er5.png)




## 9. Attach the IAM role to the EC2 instance


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5zr5xprnd4ad6z0337tk.png)

```
[ec2-user@ip-172-31-39-20 ~]$ aws rds describe-db-instances --region us-east-1 --query "DBInstances[*].[DBInstanceIdentifier,DbiResourceId]"
[
    [
        "database-1",
        "db-ZHJ3WUBV6WF5XAU5XYLQU3ME5M"
    ]
]
[ec2-user@ip-172-31-39-20 ~]$

```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/omv2fhibd5v89og9za8i.png)




## 10. Download the SSL root certificate file or certificate bundle file
- download the root certificate that works for all Regions
- SSL root certificate file
```
$ wget https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem
```

- Note: If your application doesn't accept certificate chains, then download the certificate bundle
- certificate bundle file - I am using this approach

```
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6w53uynnpu5vhclxsreb.png)


## 11. Generate an AWS authentication token to identify the IAM role
- Generate an AWS authentication token
```
aws rds generate-db-auth-token \
 --hostname database-1.cgizjtuyxkda.us-east-1.rds.amazonaws.com \
 --port 3306 \
 --region us-east-1 \
 --username rev
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i8y95n72whfscf0z5f7c.png)

- As you can see from above, the token consists of several hundred characters and numbers
- It can be cumbersome to pass this as a password on the command line
- so we will export this as an environment variable and use this to connect
- export the DB endpoint and the authentication token as an environment variables

```
RDSHOST="database-1.cgizjtuyxkda.us-east-1.rds.amazonaws.com"

TOKEN="$(aws rds generate-db-auth-token --hostname $RDSHOST --port 3306 --region us-east-1 --username rev)"
```


## 12. Connect to the RDS MySQL database using IAM role credentials and the authentication token and SSL certificates.

- Depending on the certificate that you are using (RootCA or Bundle), run one of the following commands:

- RootCA command:
```
mysql --host=$RDSHOST --port=3306 --ssl-ca=/home/ec2-user/rds-ca-2019-root.pem --enable-cleartext-plugin --user=rev --password=$TOKEN
```
- Bundle command:

```
mysql --host=$RDSHOST --port=3306 --ssl-ca=/home/ec2-user/rds-combined-ca-bundle.pem --enable-cleartext-plugin --user=rev --password=$TOKEN
```
**NOTE:** If you're using a MariaDB client, the ***`--enable-cleartext-plugin`*** option isn't required.

- Instead use
```
mysql --host=$RDSHOST --port=3306 --ssl-ca=/home/ec2-user/rds-combined-ca-bundle.pem --user=rev --password=$TOKEN
```
- successfully connected to the RDS MySQL Instance 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/foascx4jqlh182jnh78p.png)

## 13. Status of the SSL connection

- When you connect using ***`AWSAuthenticationPlugin`***, the connection is secured using SSL.

- To verify this, type the following at the mysql> command prompt.

```
show status like 'Ssl%';
```
- Ssl_cipher_list

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/539x572kkm267q9cadwv.png)

- Ssl_version

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56r0thekrukt954xwoal.png)

- Ssl_version

```
show status like 'Ssl_version';
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3lq2oqwkz46h6dn3kk8s.png)

- Ssl_cipher_list

```
show status like 'Ssl_cipher_list';
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ikz2q993zsr3f98eeebv.png)


# Cleanup

- delete RDS MySQL Database

- delete EC2 instance

- delete Security Groups



# What we have done so far

We have successfully connected to an Amazon Relational Database Service (Amazon RDS) database (DB) instance that's running MySQL, using AWS Identity and Access Management (IAM) credentials instead of the native authentication methods.