Access denied error connecting through proxysql to database that I can connect to otherwise

[Paul Pollack]

I'm smoke testing a new proxysql cluster for a database and having trouble establishing a connection, which I've not observed before. I was going to make a GH issue but since I haven't reproduced this yet, wanted to respect that this may be a more appropriate forum. First some information about my environment:

Ubuntu Xenial 16.04
Proxysql 1.4.9-3-gd9fd599
MySQL 5.6 on Amazon Aurora 1.17.7

I can connect to the database itself via the command-line from both the proxysql instance and a remote client, e.g. `mysql -uuser -p -h example.com -P 3306`, but when attempting to connect through proxysql, e.g. `mysql -uuser -p -h myproxysql.com -P 6022` the client receives the following error (hostname anonymized):
```
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'user'@'1.2.3.4' (using password: YES)
```
and in the proxysql logs I see a slew of these errors:
```
2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char2018-10-25 20:55:41 MySQL_Protocol.cpp:138:decode_char(): [ERROR] Invalid char
```

I've confirmed that these errors are written directly after an attempt is made to connect to the cluster. I tried to see on GH where decode_char() is called, but the only invocation I could find was in another function called unhex_pass() which I also couldn't see called anywhere, other than a patch that doesn't appear to be merged yet https://github.com/sysown/proxysql/search?q=unhex_pass&unscoped_q=unhex_pass. I could of course be doing a bad job of searching thoroughly. I'm also kind of curious why proxysql would declare a character invalid that is valid in MySQL?

I appreciate any help I can get!

Thanks!

Paul

[René Cannaò]

Which mysql client version are you using? 

--
Sent from my mobile device.
Email: rene....@gmail.com
Time zone: GMT+10

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paul Pollack]

Sorry, forgot to cc list.

On Thu, Oct 25, 2018 at 7:53 PM Paul Pollack <paul.p...@klaviyo.com> wrote:

On the remote client: mysql  Ver 14.14 Distrib 5.5.61, for debian-linux-gnu (x86_64) using readline 6.3

On the proxysql instance: mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

One additional note - the actual app uses Django 1.6 which handles database connections using the Python package mysqlclient, we're on version 1.3.12 (https://github.com/PyMySQL/mysqlclient-python)

[René Cannaò]

Can you please collect some traffic with tcpdump with running this?

mysql -uuser -p -h myproxysql.com -P 6022

If you don't want to share public IPs, please feel free to write me privately.

[René Cannaò]

I think I managed to reproduce it.

Does your password starts with '*' ?

[Paul Pollack]

Yes, it did.

[René Cannaò]

Ok, this is the root of the problem.

If you write a password in mysql_users.password starting with * , proxysql considers it as a hashed password.

Therefore in mysql_users.password you shouldn't write clear text password starting with * , but either clear text password that do not start with * , or hashed password starting with * .

The workaround for you is to use the hashed password in mysql_users.password.

From my side I can perform two further improvements to guess if the password is a hah or not:

- consider the length of the password

- check the chars in the password

Thanks

[Paul Pollack]

Interesting, thanks René. For our purposes we simply replaced the user with a new one that had a password proxysql could work with, but glad to know the root cause.