DEV Community

Revathi Joshi
Revathi Joshi

Posted on

Connecting to RDS MySQL DB instance without a password, using IAM authentication - 2

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

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

Image description

  • 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';
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • check to see the DB user revhas been created
select host, user, plugin, authentication_string from mysql.user where user='rev';
Enter fullscreen mode Exit fullscreen mode

Image description

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

Image description

Image description

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
Enter fullscreen mode Exit fullscreen mode

Image description

  • 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"
         ]
      }
   ]
}
Enter fullscreen mode Exit fullscreen mode

Image description

  • attach P-iam-rds-policy to R-iam-rds-role

Image description

9. Attach the IAM role to the EC2 instance

Image description

[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 ~]$

Enter fullscreen mode Exit fullscreen mode

Image description

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
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

Image description

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
Enter fullscreen mode Exit fullscreen mode

Image description

  • 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)"
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Bundle command:
mysql --host=$RDSHOST --port=3306 --ssl-ca=/home/ec2-user/rds-combined-ca-bundle.pem --enable-cleartext-plugin --user=rev --password=$TOKEN
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • successfully connected to the RDS MySQL Instance Image description

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%';
Enter fullscreen mode Exit fullscreen mode
  • Ssl_cipher_list

Image description

  • Ssl_version

Image description

  • Ssl_version
show status like 'Ssl_version';
Enter fullscreen mode Exit fullscreen mode

Image description

  • Ssl_cipher_list
show status like 'Ssl_cipher_list';
Enter fullscreen mode Exit fullscreen mode

Image description

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)