Clickhouse: MySQL wire protocol: authentication issue

[filimonov]

➜ cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
➜ clickhouse-server --version
ClickHouse server version 19.15.1.1390.
➜ mysql --version
mysql  Ver 14.14 Distrib 5.5.57-38.9, for Linux (x86_64) using readline 5.1
➜ mysql -h 127.0.0.1 -P 9001
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0
➜ mysql -h 127.0.0.1 -P 9001 --user default
ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: /usr/lib64/mysql/plugin/sha256_password.so: cannot open shared object file: No such file or directory

Same on docker (ubuntu).

[alexey-milovidov]

@yurriy

➜ mysql -h 127.0.0.1 -P 9001 --user default
ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: /usr/lib64/mysql/plugin/sha256_password.so: cannot open shared object file: No such file or directory

Do I understand correctly that ClickHouse server responds that it would like to use sha256_password authentication but mysql client cannot use it?

[alexey-milovidov]

And the workaround is to add sha1 password for ClickHouse user?

[yurriy]

Do I understand correctly that ClickHouse server responds that it would like to use sha256_password authentication but mysql client cannot use it?

Yes.
It should work if password is specified using double sha1.
Tag in config How to generate

[alexey-milovidov]

Ok. And if I understand correctly, sha256 is new authentication method that is only supported in newer mysql clients?

[alexey-milovidov]

@filimonov You can think on how we can make it as obvious to the user as possible.

[yurriy]

Ok. And if I understand correctly, sha256 is new authentication method that is only supported in newer mysql clients?

Yes. In documentation, sha256 plugin is defined starting from version 5.6. Our tests use 5.7.

[filimonov]

Do I understand correctly that ClickHouse server responds that it would like to use sha256_password authentication but mysql client cannot use it?

Yes.
It should work if password is specified using double sha1.
Tag in config How to generate

Actually i was using paswordless default user. Why does it try sha256?

[yurriy]

Do I understand correctly that ClickHouse server responds that it would like to use sha256_password authentication but mysql client cannot use it?

Yes.
It should work if password is specified using double sha1.
Tag in config How to generate

Actually i was using paswordless default user. Why does it try sha256?

Empty password isn't handled in a different way, since the plugin does it efficiently itself (without encryption).

[filimonov]

Actually i was using paswordless default user. Why does it try sha256?

Empty password isn't handled in a different way, since the plugin does it efficiently itself (without encryption).

I mean if sha1 is more 'portable', why don't we use that in such cases?

[yurriy]

It is possible, but it seems confusing as the authentication will work/not work depending only on whether the password is empty/non-empty.

[filimonov]

It is possible, but it seems confusing as the authentication will work/not work depending only on whether the password is empty/non-empty.

I would rather exprect the for all plain (unencrypted) passwords, like <password>qwerty123</password> (also including empty password) use sha1 in mysql.

[yurriy]

It is possible, but it seems confusing as the authentication will work/not work depending only on whether the password is empty/non-empty.

I would rather exprect the for all plain (unencrypted) passwords, like <password>qwerty123</password> (also including empty password) use sha1 in mysql.

@alexey-milovidov Is it okay to use SHA1 plugin for plaintext passwords? It makes authentication more weak, since a client is expected to know SHA1 of a password, instead of a password itself.

[alexey-milovidov]

@yurriy Yes, we can activate SHA1 authentication for plaintext passwords.
Motivation: if the user uses plaintext passwords for ClickHouse, it's already unsafe within native ClickHouse protocol (unless SSL is used), so it can be as safe in MySQL protocol.