thelearner
thelearner

Reputation: 1130

Unable to access RDS Database via IAM Authentication

I'm unable to connect to my RDS database with an IAM user.

Database username: master
IAM User: api-user

I have assigned the user programmatic access and added following policies to the user:

enter image description here

The custom rds-permission is defined as: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html

    {
   "Version": "2012-10-17",
   "Statement": [
      {
         "Effect": "Allow",
         "Action": [
             "rds-db:connect"
         ],
         "Resource": [
             "arn:aws:rds-db:REGION:ACCOUNT-ID:USER:dbi-resource-id/DB_ACCOUNT_NAME"
         ]
      }
   ]
}

The strange thing is, even though I have defined my custom permission exactly like it is required in the documentation, it is not recognized:

enter image description here

enter image description here

When I'm trying to connect using an authentication token (via golang), I'm getting following error:

error: ERROR: Error 1045: Access denied for user 'master'@'x.x.x.189' (using password: YES)

My policies don't seem to work!

Even though it is irrelevant, here is how I connect via my IAM user:

//Yes Env vars are available
//creating new credentials from environment variables
//AWS_ACCESS_KEY_ID  and  AWS_SECRET_ACCESS_KEY
awsCreds := credentials.NewEnvCredentials()

//creating authentication token for the database connection
authToken, err := rdsutils.BuildAuthToken(dbEndpoint, awsRegion, dbUser, awsCreds)
if err != nil {
    Logger.LogFatal("Unable to build Authentication Token")
    log.Fatal("Unable to build Authentication Token")  //todo remove
}

//setting up TLS
mysql.RegisterTLSConfig("custom", &tls.Config{
    InsecureSkipVerify: true,
})

// Creating the MySQL DNS string for the DB connection
// user:password@protocol(endpoint)/dbname?<params>
dnsStr := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s allowCleartextPasswords=true&tls=custom",
    dbUser, authToken, dbEndpoint,dbPort, dbName,
)
rootCertPool := x509.NewCertPool() //NewCertPool returns a new, empty CertPool.

pem, err := ioutil.ReadFile("rds-ca-bundle.pem") //reading the provided pem
if err != nil {
    log.Fatal("! Could not read certificates")
}
fmt.Println("Loading certificate seems to work")
//AppendCertsFromPEM attempts to parse a series of PEM encoded certificates.
//pushing in the pem
if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
    log.Fatal("Failed to append PEM.")
}
fmt.Println("Appending certificate seems to work too")

//setting up TLS
//we dont need a client ca?
mysql.RegisterTLSConfig("custom", &tls.Config{
    RootCAs: rootCertPool,
    InsecureSkipVerify: true,
})
database, err = sql.Open("mysql", dnsStr)

Upvotes: 4

Views: 9737

Answers (3)

Matteo
Matteo

Reputation: 2634

The answer provided by Ele is partially wrong this is the correct code:

arn := "arn:aws:iam::00000000000:role/your-TaskRole"
awsCreds := stscreds.NewCredentials(session.New(&aws.Config{Region: aws.String(Region)}), arn)

So the arn is not the one of the iam db user, but is the arn of the role assigned to your resource (EC2, ECS, Fargate)

Remember that also you MUST use TLS to connect to a database via IAM authentication

Upvotes: 1

Sharath
Sharath

Reputation: 1

I was stuck with the same issue. Got it resolved after (a huge lot of lookup online) I added port number for building Auth Token.

Earlier it was like below:

authToken, err := rdsutils.BuildAuthToken(host, region, username, sess.Config.Credentials)

Which I had to change to:

authToken, err := rdsutils.BuildAuthToken(fmt.Sprintf("%s:%d", host, 3306), region, username, sess.Config.Credentials)

This is for Golang application. But same might apply for other language SDKs. I got this answer from https://luktom.net/en/e1544-aws-lambda-and-mysql-iam-authentication-in-go

Someone from AWS need to update their official docs on this

Upvotes: 0

Ele
Ele

Reputation: 33726

According to the sample from AWS Github repository for Go programming language, you need to create the credentials as follow:

IAM ARN

Reference: IAM authentication go example

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    "github.com/go-sql-driver/mysql"

    "github.com/aws/aws-sdk-go/aws"
    "github.com/aws/aws-sdk-go/aws/credentials/stscreds"
    "github.com/aws/aws-sdk-go/aws/session"
    "github.com/aws/aws-sdk-go/service/rds/rdsutils"
)

awsCreds := stscreds.NewCredentials(session.New(&aws.Config{Region: &awsRegion}), iamArn) 

// iamArn: arn:aws:rds-db:region:account-id:dbuser:dbi-resource-id/data‌​base-user-name

authToken, err := rdsutils.BuildAuthToken(dbEndpoint, awsRegion, dbUser, awsCreds)

I almost sure you're missing that part of the IAM ARN.


Preparing a Database User

Reference: Preparing a Database User

You need to connect to your database and create a user using the AWS authentication plugin.

The following SQL statement creates a database user named lambda. Instead of specifying a password, the AWSAuthenticationPlugin is used for identifying the user. Replace with the name of the database you want to grant the user access to.

CREATE USER 'master' IDENTIFIED WITH AWSAuthenticationPlugin as 'RDS';
GRANT ALL PRIVILEGES ON <DB_NAME>.* TO 'master'@'%';
FLUSH PRIVILEGES;

Bonus recommendation

Reference: Limitations for IAM Database Authentication

I don't know what's the purpose of IAM authentication in your project, but I consider important the following from AWS documentation:

Limitations for IAM Database Authentication

With IAM database authentication, you are limited to a maximum of 20 new connections per second. If you are using a db.t2.micro instance class, the limit is 10 connections per second.

The Amazon RDS for MySQL and Aurora MySQL database engines do not impose any limits on authentication attempts per second. However, when you use IAM database authentication, your application must generate an authentication token. Your application then uses that token to connect to the DB instance or cluster. If you exceed the maximum new-connection-per-second limit, then the extra overhead of IAM database authentication can cause connection throttling. The extra overhead can even cause existing connections to drop.

We recommend the following:

  • Use IAM database authentication as a mechanism for temporary, personal access to databases.

  • Don't use IAM database authentication if your application requires more than 20 new connections per second.

  • Use IAM database authentication only for workloads that can be easily retried.

Note

For information about the maximum total connections for MySQL, see see Maximum MySQL connections. For information about the maximum total connections for Aurora MySQL, see Maximum Connections to an Aurora MySQL DB Instance.

Upvotes: 3

Related Questions