create and switch user failed on tiproxy

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. deploy cluster with tiproxy and connect to the tiproxy
2. execute the following sql

select user();
create user 'u1'@'%' identified with mysql_native_password by "aaa";
system mysql -u u1 -h 172.16.4.131 -P6000 -p"aaa";
select user();

this case is come from jdbc test

    public void testBug19354014() throws Exception {
        if (versionMeetsMinimum(5, 5, 7)) {
            Connection con = null;
            createUser("'bug19354014user'@'%'", "identified WITH mysql_native_password");
            this.stmt.executeUpdate("grant all on *.* to 'bug19354014user'@'%'");
            this.stmt.executeUpdate(versionMeetsMinimum(5, 7, 6) ? "ALTER USER 'bug19354014user'@'%' IDENTIFIED BY 'pwd'"
                    : "set password for 'bug19354014user'@'%' = PASSWORD('pwd')");
            this.stmt.executeUpdate("flush privileges");

            try {
                Properties props = new Properties();
                props.setProperty("useCompression", "true");

                con = getConnectionWithProps(props);
                ((MySQLConnection) con).changeUser("bug19354014user", "pwd");
            } finally {
                this.stmt.executeUpdate("flush privileges");

                if (con != null) {
                    con.close();
                }
            }
        }
    }

2. What did you expect to see? (Required)

like connect to the tidb directly

-- mysql -u root -h tidb-server -P 4100 
mysql> select user();
+-------------------+
| user()            |
+-------------------+
| root@10.9.190.123 |
+-------------------+
1 row in set (0.12 sec)

mysql> create user 'u1'@'%' identified with mysql_native_password by "aaa";
Query OK, 0 rows affected (0.06 sec)

mysql> system mysql -u u1 -h 172.16.5.52 -P4100 -p"aaa";
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> select user();
+-----------------+
| user()          |
+-----------------+
| u1@10.9.190.123 |
+-----------------+
1 row in set (0.04 sec)

3. What did you see instead (Required)

-- mysql -u root -h tiproxy-server -P 6000
TiDB root@172.16.4.131:(none)> select user();
+-------------------+
| user()            |
+-------------------+
| root@10.9.190.123 |
+-------------------+

1 row in set
Time: 0.055s
TiDB root@172.16.4.131:(none)> create user 'u1'@'%' identified with mysql_native_password by "aaa";
Query OK, 0 rows affected
Time: 0.064s
TiDB root@172.16.4.131:(none)> system mysql -u u1 -h 172.16.4.131 -P6000 -p"aaa";
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'u1'@'10.9.190.123' (using password: YES)

Time: 0.221s
TiDB root@172.16.4.131:(none)> select user();
+-------------------+
| user()            |
+-------------------+
| root@10.9.190.123 |
+-------------------+

4. What is your version? (Required)

376829c

[xhebox]

TiDB root@localhost:(none)> system mysql -u u1 -h 127.0.0.1 -P6000 -p"aaa";
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'u1'@'127.0.0.1' (using password: YES)

Time: 0.023s
TiDB root@localhost:(none)> system mysql -u u1 -h 127.0.0.1 -P4000 -p"aaa";
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'u1'@'127.0.0.1' (using password: YES)

Time: 0.021s

It is same if you connect 4000 port. And I can not reproduce this if I connect to a mysql server. I doubt it is tidb bug? @aytrack

[aytrack]

maybe you need create a user with host localhost or 127.0.0.1 for the localhost tidb server. You should better using a different host for the client.
I check this again on the different host.

• on the remote host from both tidb-server and tiproxy
  mysql client case can works will on both tidb-server connect and tiproxy connect
  jdbc cases always failed

• on the same host of tirpoxy server
  mysql client connect to the tiproxy sever case will failed
  jdbc cases will failed

[xhebox]

I can confirm that on the same host, tiproxy works. Maybe there is something wrong with your setup. Remote cases should work as the local one, as long as proxy-protocol is enabled.

JDBC fails because tidb/tiproxy can not handle ChangeUser command well. TiDB cloud should have the same problem.

[xhebox]

Fixed by pingcap/tidb#38830, #132.

[djshow832]

The reason: JDBC uses the TiProxy salt to generate the auth data in COM_CHANGE_USER, instead of using the TiDB salt.

The details:

• NativeAuthenticationProvider.seed is only updated in the initial handshake: https://github.com/mysql/mysql-connector-j/blob/8.0.17/src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeAuthenticationProvider.java#L140 but not updated in the switch auth request: https://github.com/mysql/mysql-connector-j/blob/8.0.17/src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeAuthenticationProvider.java#L581
• COM_CHANGE_USER uses NativeAuthenticationProvider.seed to generate the auth data: https://github.com/mysql/mysql-connector-j/blob/8.0.17/src/main/protocol-impl/java/com/mysql/cj/protocol/a/NativeAuthenticationProvider.java#L534