Support running with no persistent user database?

[OJezu]

I was wondering why isn't it possible for ProxySQL to allow all clients to connect, and close connections which are unable to authorize at MySQL upstream server. As far as I can tell, there is no possibility of operating in such mode.

As far as security goes, those clients still have to provide a proper password working with upstream. Unless ProxySQL is doing some data operations before connection with upstream is reset to current client credentials, it should not be possible to compromise any sensitive data.

Our case:

We are using (or rather planning to use) ProxySQL to provide MySQL connection pooling from our k8s cluster to MySQL server in cloud. Why are we even considering that? As it turns out:

• Azure MySQL has large connection delay (60ms, yay)
• The good people writing PHP never thought it was worthy to implement a proper connection pool

We have an Azure k8s cluster, Azure MySQL server with many databases, and a PHP application that loads different configuration and uses different MySQL users to connect to appropriate databases. Those users, databases and PHP application configurations are added on the fly, as part of an automated process.

Plugging ProxySQL instance in that k8s cluster to proxy connections turns out to be cutting connection establishment time by 90%, to 6ms from aforementioned 60ms, which is a good thing. Changing configuration of multiple running k8s pods is hard, which is a less good thing. So, we would like to save ourselves form as much dynamic configuration as possible, which brings me to the original question if ProxySQL could have a feature running with no static user database.

[renecannao]

The short answer to this is no. The longer answer is that authentication between client and proxysql, and between proxysql and database server, are not related. ProxySQL musts authenticate the client. ProxySQL needs credentials to then connect to backend. These credentials are required to have its own connection pool completely unrelated to application connection pool (or lack of application connection pool, like in your case). If proxysql was able to get credentials from just handling the authentication between client and backend server, that would mean that MySQL protocol was easily to attack with a man-in-the-middle attack.

Also, glad to hear that proxysql can cut connection establishment time by 90%! :tada:

[OJezu]

Well, it's a bummer.

These credentials are required to have its own connection pool completely.

As I understand, each user has its own connection pool, e.g. Proxy user userA will never reuse a connection established for userB. From my rudimentary search of the source I don't see C API function like mysql_change_user or mysql_reset_connection used. Is there an implementation reason for that?

If proxysql was able to get credentials from just handling the authentication between client and backend server, that would mean that MySQL protocol was easily to attack with a man-in-the-middle attack.

Some sort of password-equivalent must be exchanged, so without server authentication and integrity assurance I don't think it prevents MITM attack, but I guess it might not be possible with standard libmysql abstracting away implementation details. I understand if there is no will to pursue such "proxy credentials" functionality, but I would be surprised if it wasn't possible to MITM MySQL protocol when SSL is not used or server certificate is not validated.

[renecannao]

As I understand, each user has its own connection pool, e.g. Proxy user userA will never reuse a connection established for userB.

That is not correct. userA can reuse a connection established for userB .

From my rudimentary search of the source I don't see C API function like mysql_change_user

grep is your friend :smiley: mysql_change_user_start and mysql_change_user_cont are used.

About password hashing, I would recommend reading this: https://dev.mysql.com/doc/internals/en/secure-password-authentication.html#packet-Authentication::Native41

[OJezu]

That's great to hear the connections are shared!

Yeah, it seems ProxySQL would have to intercept challenge (which is probably hidden by libmysql) and relay it to client, which would also make it impossible to delay authorization until first query.