Add support for CLI option --enable-cleartext-plugin

[rantav]

MySQL 5.5.27 introduces the option --enable-cleartext-plugin to Enable cleartext authentication plugin This option is required, for example, in order to use AWS Aurora MySQL IAM authorized user login.

It would be great of mysql could also support this option as well. For my team it's unfortunately a showstopper to using mysql.

Thank you for a great tool!

[meeuw]

Hmm it seems possible to use mysql_cleartext_password for auth_plugin_map when connecting using PyMySQL (pymysql.connect). I'll see if I can create a PR for this in a few days.

[meeuw]

I have some trouble configuring a mysql which only accept clear text passwords (for testing purposes). It seems the clear text plugin is for LDAP and PAM authentication which is only available for MySQL enterprise edition ...

[rantav]

I came across this option when configuring MySQL Aurora (AWS flavor of MySQL) for IAM authentication support, which in high level supports similar use cases as does LDAP so what you're saying makes a lot of sense.

What's the best way to test this then?

One way might be to launch at Aurora server with IAM auth support. This might cost a few $$ for as long as it's alive. Another way I suppose is mucking around with mysql versions that support that if you gain access to any of these.

If you prefer so, then I don't mind giving it a shot and sending a PR, would you be able to send a quick pointer where to begin? (alternatively, send me a branch/PR/patch to apply and test).

[meeuw]

Could you have a look at this? https://github.com/meeuw/mycli/commit/42c343bf3378d4f04c377a2ef2c5b48ab09babf9

(I've only added auth_plugin_map={b'mysql_cleartext_password': object})

[rantav]

Hi @meeuw thanks for this. However, I'm a little confused... What's the connection b/w mysql_cleartext_password and the cli argument --enable-cleartext-plugin?

Regardless, and that's an implementation detail, how could this change affect the CLI arguments (e.g. if you want to add support for a new arg --enable-cleartext-plugin then shouldn't you add a @click.option in main.py?)

[rantav]

Oh I get what you were trying to do, simply test if it could work before adding the require CLI param. Unfortunately it doesn't. I read a little bit about pymysql and I did see in one example the use of auth_plugin_map={b'mysql_cleartext_password' but it seem to require a real handler, not sure if it'd work with just an empty object

[meeuw]

Oh yes, I'm sorry, I was a bit too brief...

I've found an error in my auth_plugin_map, you should try mysql_clear_password instead of mysql_cleartext_password. https://github.com/meeuw/mycli/commit/15e75b91a7a016a7be77b796418d06bb84851f79

I cannot get into this code path of PyMySQL but you could try to add some debugging prints in pymysql/connections.py to check if the clear password is picked up right. This is where it's handled: https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L1197

If you're using mycli in a virtualenv you can use:

git clone https://github.com/PyMySQL/PyMySQL.git
cd PyMySQL
pip install -e .

To make local changes to PyMySQL.

[meeuw]

Hmm I've did some hacking of PyMySQL for myself, to get into this code path and I think it requires another change; use None instead of object. Please check this commit: https://github.com/meeuw/mycli/commit/42433fd7b6ceb99f5bcec6f17113f3930d7e332e

[rantav]

No sorry, it isn't working still. I didn't get to debug pymysql, but I did try the changes in meeuw/mycli@42433fd as a blackbox and they don't seem to work in the sense that mysql server (well, aurora actually) isn't happy with the authentication (whereas when using mysql cli it is happy). Basically it responds as if the auth token ("password") is either not provided or incorrect.

[meeuw]

A quick update; I've installed mariadb and successfully installed pam authentication only to find out this already works with PyMySQL using the dialog plugin...

I'm particularly interested in what's returned for auth_packet.read_string() in pymysql/connections.py. If I force plugin_name = b"mysql_clear_password" I can login using a plain text password.

[lucacorti]

I was also struggling with this (aws iam auth to rds mysql), did some testing, and found out what is going on.

pymysql supports cleartext auth, but only when ssl is enabled. Apparently you need to explicitly specify an ssl option like --ssl-verify-server-cert or --ssl-ca. If you don't SSL is not enabled and auth fails with a generic auth failure error which is not helpful. If you do, everything works.

I think SSL should be enabled by default.

[danstewart]

For anyone else trying to use this with RDS IAM authentication, the command should look something like this:

mycli -h127.0.0.1 -u$user --password="$token" -P$local_port --ssl-verify-server-cert

[prashanth-devadas]

If anybody is still looking for the right cli command to connect using the --enable-cleartext-plugin the below command worked for me

mysql -h "${host}" -u ${user} --password=${pwd} --enable-cleartext-plugin

[amne]

for IAM auth on RDS through SSH jump server see https://github.com/dbcli/mycli/issues/1176

I patched the code to create the tunnel to SSH jumphost in a different way so that it can be wrapped by ssl lib and connection works now.

Here's an example:

export DBHOST=myrdsdb.cluster-whatever.us-east-1.rds.amazonaws.com
export AWS_REGION=us-east-1
export TOKEN=$(aws --profile myprofile rds generate-db-auth-token --hostname $DBHOST --username iam_usr --port=3306 --region $AWS_REGION)
mycli --ssh-user amne --ssh-host myjump.com --ssh-key-filename ~/.ssh/id_rsa mysql://iam_usr@$DBHOST -p $TOKEN --ssl

--ssl at the end is needed to pass the token in cleartext as mentioned in comments above and make sure if you're using STS to have a valid credential active before creating token.