Cannot authenticate using AWS IAM RDS Auth

[AcerLaze]

Hello, I want to set up a jenkins pipeline to automate DB deployment.

I have set up all the required settings on the aws & managed to retrieved the token using this guide from aws.
If I understand it correctly I just need to put the token in the env $SQITCH_PASSWORD and then run the command deploy
sqitch deploy db:mysql://{$dbuser}@{$dbHost}:3306/${dbName}

But when I tried to do that I get access denied.

Am I missing something or are there something wrong with how I tried to authenticate to the database ?

For more information :

• I'm using sqitch from docker image
• DB Version : Aurora MySQL 3.04.1 (compatible with MySQL 8.0.28)

[theory]

I have no idea, but can ask around. Meanwhile could you post your pipeline file so others can review how things are set up?

[AcerLaze]

Sure, here's my pipeline file content.

import com.sec.devops.AwsUtil

def gitUrl = env.GIT_URL

pipeline {
  agent {
    node {
      label params.AGENT_LABEL
    } 
  }
  stages{
    stage('Cleanup') {
      steps {
        cleanWs()
      }
    }
    stage('Checkout') {
      steps{
        script {
          def repoUrl = gitUrl + params.ORG_NAME +'/'+ params.REPO_NAME
          git url: repoUrl, branch: params.BRANCH, credentialsId: env.GIT_CREDENTIALS_ID
        }
      }
    }
    stage('Setup Sqitch') {
      steps{
        script {
          sh "docker pull sqitch/sqitch"
          sh "curl -L https://git.io/JJKCn -o sqitch && chmod +x sqitch"
        }
      }
    }
    stage('Assume Role') {
      steps {
        script {
          def roleArn = "arn:aws:iam::${params.AWS_ACCOUNT_ID}:role/${params.IAM_ROLE}"
          def service = env.SERVICE_NAME
          def sessionName = env.AWS_SESSION_NAME
          def stageEnv = params.STAGE_ENV
          def awsUtil = new AwsUtil(this)
          awsUtil.assume_role(stageEnv, service, roleArn, sessionName)
        }
      }
    }
    stage('Deploy Changes') {
      steps {
        script {
          def dbHostname = params.DB_HOSTNAME
          def dbPort = params.DB_PORT
          def dbName = params.DB_NAME
          def region = params.REGION
          def dbUser = params.DB_USER
          env.SQITCH_PASSWORD = sh(
            script: "aws rds generate-db-auth-token --hostname ${dbHostname} --port ${dbPort} --region ${region} --username ${dbUser}",
            returnStdout: true
          )
          sh "./sqitch deploy db:mysql://${params.DB_HOSTNAME}:${params.DB_PORT}/${dbName}"
        }
      }
    }
  }
}

I don't have the access to view the AwsUtil import so I can't share it, but basically it only used to simplify assume role process on aws.
The assume role part works perfectly, because i can generate the token & set it on SQITCH_PASSWORD

[theory]

In a shell you should wrap values in quotation marks. I don't recall if that's necessary in Jenkins pipelines, though, but the question mark and ampersand would definitely cause issues, because they have explicit meanings as operators in a shell context.

[AcerLaze]

I've checked the SQITCH_PASSWORD by printing the env before running the sqitch deploy and the value is correct.
Also already tried setting the value manually wrapped in quotation marks and still get access denied.

[AcerLaze]

So far from my testing, access denied only occurred when using token generated via IAM, but I can connect using db user & password.
I think I'm gonna give up trying to create sqitch jenkins pipeline with iam authentication.
Instead create an user on the db and use that to deploy changes.

[theory]

It looks like there's documentation for using a token with the mysql CLI, but not with SDKs like DBD::mysql, which Sqitch uses to connect to the registry. It may be that there's an incantation that would work by adding query parameters for the SSL CA (mysql_ssl_ca_file) and enabling cleartext auth, but I don't know what they are, and they might require modifications to App::Sqitch::Engine::mysql to work. And I don't see a likely candidate for the cleartext auth plugin in the DBD::mysql docs.

[girzel]

I believe this is an escaping issue. I've had to run the IAM tokens through sed 's/:/\\:/g' before they were usable as values for PGPASSWORD (and I assume SQITCH_PASSWORD as well).