golang-migrate doesn't work with RDS IAM authentication

[rahanar]

Describe the Bug I am trying to use golang-migrate with IAM auth (we are switching away from static master password), but unfortunately it can't connect to RDS instances.

I keep getting error: pq: PAM authentication failed for user "postgres". I have verified that everything is setup correctly for IAM auth and I am able to connect to the same instances using psql.

Steps to Reproduce

1. Setup an RDS instance and enable IAM auth by following instructions here: https://aws.amazon.com/premiumsupport/knowledge-center/rds-postgresql-connect-using-iam/
2. Fetch a new password from AWS (included in the instructions above)
3. Run migrate -path migrations -database "postgresql://${URL_to_cluster}/${DB_NAME}?user=postgres&password=$PGPASSWORD&sslrootcert=./rds-ca-2019-root.pem&sslmode=verify-full" up

Expected Behavior golang-migrate should be able to connect to IAM enabled RDS instance and run migrations.

Migrate Version v4.14.1

Loaded Source Drivers Source drivers: github-ee, github, gitlab, go-bindata, godoc-vfs, gcs, file, s3, bitbucket

Loaded Database Drivers Database drivers: firebirdsql, mongodb+srv, cassandra, firebird, postgresql, redshift, spanner, clickhouse, crdb-postgres, mongodb, mysql, sqlserver, cockroach, cockroachdb, neo4j, postgres, stub`

Go Version go version go1.16.2 darwin/amd64

Stacktrace N/A

Additional context I am not sure if I am doing it wrong, but based on the docs that command looks correct. Thank you!

[dhui]

Your connection string looks incorrect. e.g. user & password should be specified before the host, not in the query params See the docs Also, you may want to use ${PGPASSWORD} instead of $PGPASSWORD. https://tldp.org/LDP/abs/html/parameter-substitution.html

[rahanar]

@dhui sorry for the late reply.

I already tried that and tried it again now. It doesn't work. The reason is the structure of the token when IAM auth is used is of the form DB_HOST_NAME:PORT/LONG_HASH. You can see an example of here in the following doc under Generating an IAM authentication token

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.AWSCLI.html

When golang-migrate tries to parse it, it tries to connect to the DB instead of using it as a token.

[kosyfrances]

Percent encoding the IAM token before passing it into the postgres URL format like this postgresql://user:secret@DBhostblabla worked for me. According to Postgres docs, Percent-encoding may be used to include symbols with special meaning in any of the URI parts.

[gabrielqs]

This worked for me:

urlencode_grouped_case () {
    string=$1; format=; set --
    while
        literal=${string%%[!-._~0-9A-Za-z]*}
        case "$literal" in
            ?*)
            format=$format%s
            set -- "$@" "$literal"
            string=${string#$literal};;
        esac
        case "$string" in
          "") false;;
        esac
    do
        tail=${string#?}
        head=${string%$tail}
        format=$format%%%02x
        set -- "$@" "'$head"
        string=$tail
    done
    printf "$format\\n" "$@"
}
export RDS_TOKEN=$(aws rds generate-db-auth-token --hostname $RDS_HOST --port $RDS_PORT --region $AWS_REGION --username $RDS_USERNAME)
export RDS_TOKEN=$(urlencode_grouped_case $RDS_TOKEN)
export DATABASE_URL="postgresql://${RDS_USERNAME}:${RDS_TOKEN}@${RDS_HOST}:${RDS_PORT}/${RDS_DBNAME}"
migrate -path ./migrations -database '${DATABASE_URL}' up  

[felipecrestani]

This worked in Jenkins Pipeline

import java.net.URLEncoder
String urlEncode(String password){
  return URLEncoder.encode(password, "UTF-8")
}

String encodedPassword = urlEncode(password)
String databaseURL = "postgres://${datasourceUrl}?sslmode=require&user=${user}&password=${encodedPassword}"

sh(
  label: 'migrate:migrate',
  script: """
    migrate -database "${databaseURL}" -path migrations up
  """
)