Please refer to the 1st part of this article
Connecting to RDS MySQL DB instance without a password, using IAM authentication - 1
Please visit my GitHub Repository for RDS articles 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:
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
- 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
revhas been created
select host, user, plugin, authentication_string from mysql.user where user='rev';
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
- take note of DB ResourceID
db-ZHJ3WUBV6WF5XAU5XYLQU3ME5M
- 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"
]
}
]
}
- attach
P-iam-rds-policytoR-iam-rds-role
9. Attach the IAM role to the EC2 instance
[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 ~]$
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
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
- 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
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
- Ssl_version
- Ssl_version
show status like 'Ssl_version';
- Ssl_cipher_list
show status like 'Ssl_cipher_list';
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.
Top comments (0)