Search code examples
amazon-web-servicesmariadbamazon-auroradatagrip

How to Access a AWS RDS with a CNAME hostname rather than Configuration Endpoint

I have a simple question which I am not finding in Stackoverflow even though this process of generating a db auth token has been asked many times but would like clarification on this if anyone can help.

This is all being tested currently and the process is like so:

  1. I created my user with the following SQL script:
CREATE USER 'readonly'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT SELECT ON *.* TO 'readonly'@'%';
ALTER USER 'readonly'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;
  1. From here, I then generated the db auth token using the CNAME record of the hostname which holds my Writer configuration endpoint.
aws rds generate-db-auth-token --hostname rds-dev-mysql56.lock.internal --port 3306 --region us-east-1 --username readonly

  1. Once I grab the token to sign in through my Datagrip IDE, I receive this error:

    [28000][1045] Could not connect to address=(host=rds-dev-mysql56.lock.internal)(port=3306)(type=master) : (conn=107783568) Access denied for user 'readonly'@'IP' (using password: YES) Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding' [28000][1045] (conn=107783568) Access denied for user 'readonly'@'IP' (using password: YES) Current charset is UTF-8. If password has been set using other charset, consider using option 'passwordCharacterEncoding'

  2. I then followed this where the AWS RDS MySQL passwordCharacterEncoding should be added with utf8 like in this post: MariaDB - Cannot connect to MariaDB instance from Dbeaver using correct password. However, with this change I get a new error:

    [28000][1045] Could not connect to address=(host=rds-dev-mysql56.lock.internal)(port=3306)(type=master) : (conn=107785323) Access denied for user 'readonly'@'IP' (using password: YES) [28000][1045] (conn=107785323) Access denied for user 'readonly'@'IP' (using password: YES)

  3. It's only when I directly grab the Writer instance Configuration endpoint that this works where I am able to sign in.

So my question is, Do CNAME records work with generating the db auth token? I have read online that CNAME records don't work well with SSL/TLS encryption since I enabled REQUIRE SSL. Would appreciate any help.

EDIT: Added the error output in my Datagrip IDE and added link following instructions on the passwordCharacterEncoding.

EDIT 2: In the Datagrip IDE, I have SSL enabled and the following information. The token that is created is pasted where Password which does not work with the CNAME record: enter image description here

However, as for how I get it to work with the Writer instance Configuration Endpoint, I enter the following to generate the token: aws rds generate-db-auth-token --hostname rds-dev-mysql56.cluster-23423423fd.us-east-1.rds.amazonaws.com --port 3306 --region us-east-1 --username readonly

This allows me to grab the token and successfully enter the database. I hope this is enough information.

Solution

  • I don't think there is any easy way to figure this out but I guess CNAME records don't work with RDS IAM Authentication which is annoying. The only way for this is to work is to do the following:

    1. Install the AWS Toolkit for IntelliJ or embed the Writer Instance configuration endpoint (which will be a pain so it will be better to utilize the AWS Toolkit instead to assume the correct role where the RDS cluster resides.) You can find more info here: https://docs.aws.amazon.com/toolkit-for-jetbrains/latest/userguide/rds-connection.html
    2. When using the AWS IntelliJ toolkit, I click on the RDS Cluster I want to connect via assuming IAM Role and presto, works much faster this way. The only thing I need to do is change the User: enter image description here

    I just wanted a simple way to connect using CNAME but this will do the trick instead.