search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.93k stars 967 forks source link

Credentials with caching_sha2_password do not work #4505

Closed Uccio closed 4 months ago

Uccio commented 4 months ago

Issue

Users created on SqlProxy using the encrypted password are not allowed access.

Software Version:

How to replicate the problem

Below are all the SQL statements required to have 4 test users: testone, testtwo, testthree, testfour where only testone works because it uses a plaintext password.

on Mysql run this:

CREATE USER 'testone'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; CREATE USER 'testtwo'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; CREATE USER 'testthree'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; CREATE USER 'testfour'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; FLUSH PRIVILEGES; SELECT user, HEX(authentication_string), HEX(SUBSTR(authentication_string, 8, 20)) AS salt, plugin FROM mysql.user WHERE user like 'test%'\G;

on SqlProxy run this (but be careful that the jumps must be taken from mysql): INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testone', '1234',10, 0); INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testtwo', CACHING_SHA2_PASSWORD('1234'),10, 0); INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testthree', CACHING_SHA2_PASSWORD('1234',unhex('XXX')),10, 0); INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testfour', '1234',10, 0); UPDATE mysql_users SET password = CACHING_SHA2_PASSWORD('1234',unhex('XXX')) WHERE username='testfour'; LOAD MYSQL USERS TO RUNTIME; SAVE mysql users TO DISK; SELECT username, HEX(password), HEX(SUBSTR(password, 8, 20)) AS salt FROM mysql_users where username like 'test%'\G; SELECT username, HEX(password), HEX(SUBSTR(password, 8, 20)) AS salt FROM runtime_mysql_users where username like 'test%'\G;

Full MySql Console

mysql> CREATE USER 'testone'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'testtwo'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'testthree'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; Query OK, 0 rows affected (0.02 sec)

mysql> CREATE USER 'testfour'@'%' IDENTIFIED WITH caching_sha2_password BY '1234'; Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.02 sec)

mysql> SELECT user, HEX(authentication_string), HEX(SUBSTR(authentication_string, 8, 20)) AS salt, plugin FROM mysql.user WHERE user like 'test%'\G; 1. row user: testfour HEX(authentication_string): 244124303035240E743F25130209271D5E04754F434209563F055075386D66346131546872414C2F554150536D3775476F306876543275763872746F33642E4A4C4278395631 salt: 0E743F25130209271D5E04754F434209563F0550 plugin: caching_sha2_password 2. row user: testone HEX(authentication_string): 24412430303524495366373163464B523B6F3B4A5B4F63152C74385033677636656F68564379304E314767734B6A6E57624A737A45484341466A666C344B767A486E59795143 salt: 495366373163464B523B6F3B4A5B4F63152C7438 plugin: caching_sha2_password 3. row user: testthree HEX(authentication_string): 24412430303524785F762C125F03251334792A150366402A0504036153546173547673746669656B59366D534944687062594A563733784C796263644637694B36504E764B42 salt: 785F762C125F03251334792A150366402A050403 plugin: caching_sha2_password 4. row user: testtwo HEX(authentication_string): 2441243030352478067B30405508214446290211576D5144012D0B355A7455656D63666B516D4C346E464F385A4467507545444F6578727A4B7774564451643938484D334742 salt: 78067B30405508214446290211576D5144012D0B plugin: caching_sha2_password 4 rows in set (0.00 sec)

Full SqlProxy Console

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testone', '1234',10, 0); Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testtwo', CACHING_SHA2_PASSWORD('1234'),10, 0); Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testthree', CACHING_SHA2_PASSWORD('1234',unhex('785F762C125F03251334792A150366402A050403')),10, 0); Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('testfour', '1234',10, 0); Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> UPDATE mysql_users SET password = CACHING_SHA2_PASSWORD('1234',unhex('0E743F25130209271D5E04754F434209563F0550')) WHERE username='testfour'; Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> LOAD MYSQL USERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec)

ProxySQLAdmin> SAVE mysql users TO DISK; Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> SELECT username, HEX(password), HEX(SUBSTR(password, 8, 20)) AS salt FROM mysql_users where username like 'test%'\G; 1. row username: testone HEX(password): 31323334 salt: 2. row username: testtwo HEX(password): 244124303035243B6830104909557F7E291A387C01062612073758324A7756652F6A442F72385758424A62542E4848376D49465A2F503732397251303273687A6E786D714935 salt: 3B6830104909557F7E291A387C01062612073758 3. row username: testthree HEX(password): 24412430303524785F762C125F03251334792A150366402A0504036153546173547673746669656B59366D534944687062594A563733784C796263644637694B36504E764B42 salt: 785F762C125F03251334792A150366402A050403 4. row username: testfour HEX(password): 244124303035240E743F25130209271D5E04754F434209563F055075386D66346131546872414C2F554150536D3775476F306876543275763872746F33642E4A4C4278395631 salt: 0E743F25130209271D5E04754F434209563F0550 4 rows in set (0.00 sec)

ERROR: No query specified

ProxySQLAdmin> SELECT username, HEX(password), HEX(SUBSTR(password, 8, 20)) AS salt FROM runtime_mysql_users where username like 'test%'\G; 1. row username: testone HEX(password): 31323334 salt: 2. row username: testtwo HEX(password): 244124303035243B6830104909557F7E291A387C01062612073758324A7756652F6A442F72385758424A62542E4848376D49465A2F503732397251303273687A6E786D714935 salt: 3B6830104909557F7E291A387C01062612073758 3. row username: testthree HEX(password): 24412430303524785F762C125F03251334792A150366402A0504036153546173547673746669656B59366D534944687062594A563733784C796263644637694B36504E764B42 salt: 785F762C125F03251334792A150366402A050403 4. row username: testfour HEX(password): 244124303035240E743F25130209271D5E04754F434209563F055075386D66346131546872414C2F554150536D3775476F306876543275763872746F33642E4A4C4278395631 salt: 0E743F25130209271D5E04754F434209563F0550 5. row username: testone HEX(password): 31323334 salt: 6. row username: testtwo HEX(password): 244124303035243B6830104909557F7E291A387C01062612073758324A7756652F6A442F72385758424A62542E4848376D49465A2F503732397251303273687A6E786D714935 salt: 3B6830104909557F7E291A387C01062612073758 7. row username: testthree HEX(password): 24412430303524785F762C125F03251334792A150366402A0504036153546173547673746669656B59366D534944687062594A563733784C796263644637694B36504E764B42 salt: 785F762C125F03251334792A150366402A050403 8. row username: testfour HEX(password): 244124303035240E743F25130209271D5E04754F434209563F055075386D66346131546872414C2F554150536D3775476F306876543275763872746F33642E4A4C4278395631 salt: 0E743F25130209271D5E04754F434209563F0550 8 rows in set (0.01 sec)

Logs

2024-04-12 14:50:04 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.36:47899 2024-04-12 14:50:14 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.1:39964 2024-04-12 14:50:20 MySQL_Session.cpp:6056:handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'testtwo'@'172.20.22.7' (using password: YES) 2024-04-12 14:50:23 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.36:18613 2024-04-12 14:50:24 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.178.1:44064 2024-04-12 14:50:26 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.36:65137 2024-04-12 14:50:30 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.36:50984 2024-04-12 14:50:30 MySQL_Session.cpp:6056:handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'testthree'@'172.20.22.7' (using password: YES) 2024-04-12 14:50:34 MySQL_Thread.cpp:4081:process_all_sessions(): [WARNING] Closing unhealthy client connection 172.26.177.36:7764 2024-04-12 14:50:41 MySQL_Session.cpp:6056:handler_status_CONNECTINGCLIENTSTATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'testfour'@'172.20.22.7' (using password: YES)

Client Console

mysql -u testtwo -p -h server.lan --port=6033 Enter password: ERROR 1045 (28000): ProxySQL Error: Access denied for user 'testtwo'@'172.20.22.7' (using password: YES) mysql -u testthree -p -h server.lan --port=6033 Enter password: ERROR 1045 (28000): ProxySQL Error: Access denied for user 'testthree'@'172.20.22.7' (using password: YES) mysql -u testfour -p -h tor1mysql1-emigui.dev.lan --port=6033 Enter password: ERROR 1045 (28000): ProxySQL Error: Access denied for user 'testfour'@'172.20.22.7' (using password: YES)

Maybe I am doing something wrong, but I have been trying unsuccessfully for a few days now not to have the passwords in the clear in SqlProxy

Thank you!

renecannao commented 4 months ago

Hi @Uccio . Please run the following, and let me know if then you are able to authenticate:

SET mysql-default_authentication_plugin='caching_sha2_password';
LOAD MYSQL VARIABLES TO RUNTIME;
Uccio commented 4 months ago

Hi @renecannao,

the variable was already setted to 'caching_sha2_password, but I still launched the queries...

Unfortunately it doesn't solve it... none of users 2 to 4 can authenticate themselves

This is what I see in the console.

ProxySQLAdmin> SET mysql-default_authentication_plugin='caching_sha2_password'; Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-default%'; +-------------------------------------+-----------------------+ | variable_name | variable_value | +-------------------------------------+-----------------------+ | mysql-default_charset | utf8 | | mysql-default_collation_connection | utf8_general_ci | | mysql-default_max_latency_ms | 1000 | | mysql-default_authentication_plugin | caching_sha2_password | | mysql-default_reconnect | true | | mysql-default_session_track_gtids | OFF | | mysql-default_query_delay | 0 | | mysql-default_query_timeout | 36000000 | | mysql-default_schema | information_schema | +-------------------------------------+-----------------------+ 9 rows in set (0.00 sec)

ProxySQLAdmin> SELECT * FROM global_variables WHERE variable_name LIKE '%'; +----------------------------------------------------------------------+--------------------------------------------+ | variable_name | variable_value | +----------------------------------------------------------------------+--------------------------------------------+ | admin-stats_credentials | stats:stats | | admin-stats_mysql_connections | 60 | | admin-stats_mysql_connection_pool | 60 | | admin-stats_mysql_query_cache | 60 | | admin-stats_mysql_query_digest_to_disk | 0 | | admin-stats_system_cpu | 60 | | admin-stats_system_memory | 60 | | admin-telnet_admin_ifaces | (null) | | admin-telnet_stats_ifaces | (null) | | admin-refresh_interval | 2000 | | admin-read_only | false | | admin-vacuum_stats | true | | admin-cluster_username | | | admin-cluster_password | | | admin-cluster_check_interval_ms | 1000 | | admin-cluster_check_status_frequency | 10 | | admin-cluster_mysql_query_rules_diffs_before_sync | 3 | | admin-cluster_mysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_users_diffs_before_sync | 3 | | admin-cluster_proxysql_servers_diffs_before_sync | 3 | | admin-cluster_mysql_variables_diffs_before_sync | 3 | | admin-cluster_admin_variables_diffs_before_sync | 3 | | admin-cluster_ldap_variables_diffs_before_sync | 3 | | admin-cluster_mysql_query_rules_save_to_disk | true | | admin-cluster_mysql_servers_save_to_disk | true | | admin-cluster_mysql_users_save_to_disk | true | | admin-cluster_proxysql_servers_save_to_disk | true | | admin-cluster_mysql_variables_save_to_disk | true | | admin-cluster_admin_variables_save_to_disk | true | | admin-cluster_ldap_variables_save_to_disk | true | | admin-cluster_mysql_servers_sync_algorithm | 1 | | admin-checksum_mysql_query_rules | true | | admin-checksum_mysql_servers | true | | admin-checksum_mysql_users | true | | admin-checksum_mysql_variables | true | | admin-checksum_admin_variables | true | | admin-checksum_ldap_variables | true | | admin-restapi_enabled | false | | admin-restapi_port | 6070 | | admin-web_enabled | false | | admin-web_port | 6080 | | admin-web_verbosity | 0 | | admin-prometheus_memory_metrics_interval | 61 | | admin-coredump_generation_interval_ms | 30000 | | admin-coredump_generation_threshold | 10 | | admin-ssl_keylog_file | | | admin-admin_credentials | admin:secretPassword2024 | | admin-mysql_ifaces | 127.0.0.1:6032 | | mysql-default_charset | utf8 | | mysql-default_collation_connection | utf8_general_ci | | mysql-shun_on_failures | 5 | | mysql-shun_recovery_time_sec | 10 | | mysql-unshun_algorithm | 0 | | mysql-query_retries_on_failure | 1 | | mysql-client_host_cache_size | 0 | | mysql-client_host_error_counts | 0 | | mysql-connect_retries_delay | 1 | | mysql-connection_delay_multiplex_ms | 0 | | mysql-connection_max_age_ms | 0 | | mysql-connect_timeout_client | 10000 | | mysql-connect_timeout_server_max | 10000 | | mysql-enable_client_deprecate_eof | true | | mysql-enable_server_deprecate_eof | true | | mysql-enable_load_data_local_infile | false | | mysql-eventslog_filename | | | mysql-eventslog_filesize | 104857600 | | mysql-eventslog_default_log | 0 | | mysql-eventslog_format | 1 | | mysql-auditlog_filename | | | mysql-auditlog_filesize | 104857600 | | mysql-handle_unknown_charset | 1 | | mysql-free_connections_pct | 10 | | mysql-connection_warming | false | | mysql-session_idle_ms | 1 | | mysql-have_ssl | true | | mysql-log_mysql_warnings_enabled | false | | mysql-monitor_enabled | true | | mysql-monitor_connect_timeout | 600 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_read_only_max_timeout_count | 3 | | mysql-monitor_replication_lag_group_by_host | false | | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_replication_lag_count | 1 | | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 | | mysql-monitor_groupreplication_max_transactions_behind_count | 3 | | mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 | | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | | mysql-monitor_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_threads_min | 8 | | mysql-monitor_threads_max | 128 | | mysql-monitor_threads_queue_maxsize | 128 | | mysql-monitor_local_dns_cache_ttl | 300000 | | mysql-monitor_local_dns_cache_refresh_interval | 60000 | | mysql-monitor_local_dns_resolver_queue_maxsize | 128 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-max_allowed_packet | 67108864 | | mysql-tcp_keepalive_time | 120 | | mysql-use_tcp_keepalive | true | | mysql-automatic_detect_sqli | false | | mysql-firewall_whitelist_enabled | false | | mysql-firewall_whitelist_errormsg | Firewall blocked this query | | mysql-throttle_connections_per_sec_to_hostgroup | 1000000 | | mysql-max_transaction_idle_time | 14400000 | | mysql-max_transaction_time | 14400000 | | mysql-multiplexing | true | | mysql-log_unhealthy_connections | true | | mysql-enforce_autocommit_on_reads | false | | mysql-autocommit_false_not_reusable | false | | mysql-autocommit_false_is_transaction | false | | mysql-verbose_query_error | false | | mysql-hostgroup_manager_verbose | 1 | | mysql-binlog_reader_connect_retry_msec | 3000 | | mysql-threshold_query_length | 524288 | | mysql-threshold_resultset_size | 4194304 | | mysql-query_digests_max_digest_length | 2048 | | mysql-query_digests_max_query_length | 65000 | | mysql-query_digests_grouping_limit | 3 | | mysql-query_digests_groups_grouping_limit | 10 | | mysql-query_rules_fast_routing_algorithm | 1 | | mysql-wait_timeout | 28800000 | | mysql-throttle_max_bytes_per_second_to_client | 0 | | mysql-throttle_ratio_server_to_client | 0 | | mysql-max_stmts_per_connection | 20 | | mysql-max_stmts_cache | 10000 | | mysql-mirror_max_concurrency | 16 | | mysql-mirror_max_queue_length | 32000 | | mysql-default_max_latency_ms | 1000 | | mysql-query_processor_iterations | 0 | | mysql-query_processor_regex | 1 | | mysql-set_query_lock_on_hostgroup | 1 | | mysql-set_parser_algorithm | 2 | | mysql-reset_connection_algorithm | 2 | | mysql-auto_increment_delay_multiplex | 5 | | mysql-auto_increment_delay_multiplex_timeout_ms | 10000 | | mysql-long_query_time | 1000 | | mysql-query_cache_size_MB | 256 | | mysql-query_cache_soft_ttl_pct | 0 | | mysql-query_cache_handle_warnings | 0 | | mysql-poll_timeout_on_failure | 100 | | mysql-server_capabilities | 569867 | | mysql-keep_multiplexing_variables | tx_isolation,transaction_isolation,version | | mysql-default_authentication_plugin | caching_sha2_password | | mysql-kill_backend_connection_when_disconnect | true | | mysql-client_session_track_gtid | true | | mysql-session_idle_show_processlist | true | | mysql-show_processlist_extended | 0 | | mysql-query_digests | true | | mysql-query_digests_lowercase | false | | mysql-query_digests_replace_null | false | | mysql-query_digests_no_digits | false | | mysql-query_digests_normalize_digest_text | false | | mysql-query_digests_track_hostname | false | | mysql-query_digests_keep_comment | false | | mysql-parse_failure_logs_digest | false | | mysql-servers_stats | true | | mysql-default_reconnect | true | | mysql-ssl_p2s_ca | /var/lib/proxysql/ca.pem | | mysql-ssl_p2s_capath | | | mysql-ssl_p2s_cert | /var/lib/proxysql/client-cert.pem | | mysql-ssl_p2s_key | /var/lib/proxysql/client-key.pem | | mysql-ssl_p2s_cipher | ECDHE-RSA-AES256-SHA | | mysql-ssl_p2s_crl | | | mysql-ssl_p2s_crlpath | | | mysql-init_connect | | | mysql-ldap_user_variable | | | mysql-add_ldap_user_comment | | | mysql-default_session_track_gtids | OFF | | mysql-connpoll_reset_queue_length | 50 | | mysql-min_num_servers_lantency_awareness | 1000 | | mysql-aurora_max_lag_ms_only_read_from_replicas | 2 | | mysql-stats_time_backend_query | false | | mysql-stats_time_query_processor | false | | mysql-query_cache_stores_empty_result | true | | mysql-data_packets_history_size | 0 | | mysql-handle_warnings | 1 | | mysql-evaluate_replication_lag_on_servers_load | 1 | | mysql-threads | 4 | | mysql-max_connections | 2048 | | mysql-default_query_delay | 0 | | mysql-default_query_timeout | 36000000 | | mysql-have_compress | true | | mysql-poll_timeout | 2000 | | mysql-interfaces | 0.0.0.0:6033 | | mysql-default_schema | information_schema | | mysql-stacksize | 1048576 | | mysql-server_version | 8.0.35 | | mysql-connect_timeout_server | 3000 | | mysql-monitor_username | proxysql | | mysql-monitor_password | SuperPassword | | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 60000 | | mysql-monitor_ping_interval | 10000 | | mysql-monitor_read_only_interval | 1500 | | mysql-monitor_read_only_timeout | 500 | | mysql-ping_interval_server_msec | 120000 | | mysql-ping_timeout_server | 500 | | mysql-commands_stats | true | | mysql-sessions_sort | true | | mysql-connect_retries_on_failure | 10 | | mysql-monitor_aws_rds_topology_discovery_interval | 1000 | | admin-version | 2.6.2-41-gb368fc9 | +----------------------------------------------------------------------+--------------------------------------------+ 211 rows in set (0.01 sec)

JavierJF commented 4 months ago

Hi @Uccio,

don't know if the above is testing environment, please be aware there are passwords in the comment.

Uccio commented 4 months ago

@JavierJF

not a prod environment, but I replaced the passwords

Thanks for the courtesy

JavierJF commented 4 months ago

Hi @Uccio,

yw, assumed it was the case, but just to be double safe. Regarding your issue, it appears that the configuration is correct according the data you have shared. I see enough variability in your data for being confident that the cases you propose should be working, could you please share with us:

Thank you, regards, Javier.

Uccio commented 4 months ago

Hello @JavierJF,

thanks for your support, I have tried to connect with both new and old clients:

mysql Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 mysql Ver 14.14 Distrib 5.6.28, for debian-linux-gnu (x86_64) using EditLine wrapper

(I have no configurations in the client)

but the result is always the same, only the user with the plaintext password manages to establish a connection.

Thank you!

JavierJF commented 4 months ago

Hi @Uccio,

yw! So, let's try something, because I'm sensing the issue, and it's something we are going to improve in the documentation, I see that you are using either MySQL 5.6.28 or MariaDB client 10.3.39. I have checked and looks like both versions you are using doesn't support the respective plugins for making caching_sha2_password authentication work. Your MariaDB version supports it, but looks like you might be using a package that isn't bundled with the required plugin. Let me break it down per client:

MySQL

For older MySQL client that you might not know which one is the default authentication plugin or if even the package is shipped with it, you want to try to connect using the following command:

mysql -h$HOST --ssl --default-auth='caching_sha256_password' -u$USERNAME -p$PASSWORD -P6033

The --ssl parameter may not be required, if SSL is enabled by default, but this is also something of newer versions. If something isn't right, like the plugin being missing, mysql client is explicit about the error:

ERROR 2059 (HY000): Authentication plugin 'caching_sha256_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha256_password.so: cannot open shared object file: No such file or directory

The reason for the default-auth parameter is due to a current limitation that is documented here as scenario 1. In essence, old clients will request the old mysql_native_password and a currently unsupported auth switch should take place for the authentication to succeed, this is a limitation that will be addressed in the future.

MariaDB

For MariaDB you want to check if the plugin is present in the system, since the client isn't going to report a nice error, this is because unlike MySQL client, the client performs a "dummy fallback" of the selected auth method if the correct plugin isn't found, to avoid getting you network analyzer of choice, you can simply:

[root@system /]# find /usr -name "caching*"
/usr/lib64/mariadb/plugin/caching_sha2_password.so

You should be able to find if the required plugin is present, if it's, then the following command should allow you to perform the correct authentication:

mysql -h$HOST --ssl --default-auth='caching_sha2_password' -u$USERNAME -p$PASSWORD -P6033

For MariaDB the command is the same as for MySQL, and before version 10.10 the --ssl flag is mandatory, as caching_sha2_password requires a secure channel, but MariaDB client doesn't tries to enable it when detecting that caching_sha2_password is needed. The default-auth flag is required for the same reason it's required in the mysql command.

Solution

If you try with a newer connector for MySQL or a MariaDB connector which package is bundle with the plugin, you should be able to connect with either one of the previous commands. For example, I have successfully login in a Centos 8 system with this version:

[root@system /]# mysql --version
mysql  Ver 15.1 Distrib 10.3.28-MariaDB, for Linux (x86_64) using readline 5.1

Of course, this version is bundled with the required plugin:

[root@system /]# find /usr -name "caching*"
/usr/lib64/mariadb/plugin/caching_sha2_password.so

Hope this information helps, and was the cause of your issue. I will be updating the docs so this complications with older versions are more clear.

Thank you, regards, Javier.

Uccio commented 4 months ago

Hi @JavierJF

thanks for pointing out that the problem lies in the client, I have done several tests which I share with you below.

Unfortunately, however, the situation is a bit complicated because even though with the "right client" the system works, if the "wrong client" goes after the right client it too works (BUG). Let me explain better: If I connect first with updated mysql client and after with a mariadb-wrong one (no caching_sha256_password) also mariadb-wrong client work.

On ubuntu systems the mariadb-client package does not have as a dependency the libmariadb3 package that includes support for caching_sha256_password therefore the client does not work as expected.

A user using a client without caching_sha256_password support can access the DB until proxysql reboots or users reload. Whether it works or not, however, is related to the individual user+client.

All of the following tests were done using an official Ubuntu container to avoid a dirty starting system or strange dependencies in installed packages. Therefore, all tests were run with:

The results are:

Test: 1 Ubuntu: 20.04 packages: mysql-client Client: mysql Ver 8.0.36-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)) Test CMD: mysql -u testtwo -p -h 1.2.3.4 --port=6033 Test Result: PASS Error: -

Test: 2 Ubuntu: 20.04 packages: mariadb-client Client: mysql Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Test CMD: mysql -u testtwo -p -h 1.2.3.4 --port=6033 Test Result: FAIL Error: ERROR 1045 (28000): ProxySQL Error: Access denied for user 'testtwo'@'172.20.139.138' (using password: YES)

Test: 3 Ubuntu: 20.04 packages: mariadb-client libmariadb3 Client: mysql Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Test CMD: mysql -u testtwo -p -h 1.2.3.4 --port=6033 Test Result: FAIL Error: ERROR 2061 (HY000): RSA Encryption not supported - caching_sha2_password plugin was built with GnuTLS support

Test: 4 Ubuntu: 20.04 packages: mariadb-client libmariadb3 Client: mysql Ver 15.1 Distrib 10.3.39-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 Test CMD: mysql -u testtwo -p -h 1.2.3.4 --port=6033 --ssl Test Result: PASS Error: -

Conclusions:

Thank you, Uccio

renecannao commented 4 months ago

if the "wrong client" goes after the right client it too works (BUG). Let me explain better: If I connect first with updated mysql client and after with a mariadb-wrong one (no caching_sha256_password) also mariadb-wrong client work.

Can you elaborate on why this is wrong/bad?

JavierJF commented 4 months ago

Hi @Uccio,

the problem you point out it's not bug , it's known behavior and it's part of the previously mentioned limitation documented here as scenario 1. The behavior you are seeing is that, once ProxySQL has a retained the clear-text password from the first successful authentication, there is no need to perform the currently unsupported auth-switch unsupported authentication by the client. Instead, since ProxySQL already has the clear-text password, is able to use it to validate the client authentication attempt. The limitation only applies if this clear-text passwords cache isn't warmup.

I don't see any reason why this behavior should be classified as bad or a bug, it's just part of the behavior of the current limitation previously described the clear-text password warmup that caching_sha2_password allows. Meaning that the second connections attempts are now possible simply because the clear-text password is already known, but this isn't related to the first connection attempt, that unlike the following ones, will require to perform the full authentication for caching_sha2_passwords.

EDITS: I have edited the marked sections as I didn't explain myself very well. This behavior isn't strictly related to the previously mentioned limitation, since it will only affect initial login attempts, and here we are talking about later login attempts after a successful one.

Thanks, Javier.

Uccio commented 4 months ago

Hello, @JavierJF

thank you very much for your support and speed of reaction, I really appreciate it.

Authentications in my experience are always complicated processes (I know this as a developer) and let's say that what I went to test unintentionally is definitely a borderline case (two different clients but in sequence) which confirms how the implementation works.

As a ubuntu user with a mariadb client, trying out the authentications shows that they work if the passwords are in clear text while if we save them encrypted they stop working. What could be improved at this point would be the documentation on some page like https://proxysql.com/documentation/Packaging/ although the documentation lacks a section on how to install the necessary packages on the various distros (if there is one, I haven't found it)

I thank you again for your help. Uccio

JavierJF commented 4 months ago

Hi @Uccio,

yw! Always glad to assist, thank you for being an active member of the community.

Authentications in my experience are always complicated processes (I know this as a developer) and let's say that what I went to > test unintentionally is definitely a borderline case (two different clients but in sequence) which confirms how the implementation > works.

Indeed they are :smile: and this is one of the tricky ones.

As a ubuntu user with a mariadb client, trying out the authentications shows that they work if the passwords are in clear text while if we save them encrypted they stop working. What could be improved at this point would be the documentation on some page like https://proxysql.com/documentation/Packaging/ although the documentation lacks a section on how to install the necessary packages on the various distros (if there is one, I haven't found it)

Since the documentation didn't mentioned anything related to 'extra flags' maybe being required by clients, I have updated both these doc sections to reflect this fact, and provide some guidance to users using the feature for the first time:

Now there are mentions to the flags, why they are required, and potential extra flags or missing plugins by other clients. I think this is the most relevant part of the documentation to update. I don't think that ProxySQL documentation should elaborate more, or duplicate information from MySQL clients installations, or their particular requirements for supporting extra authentication methods. This is something that belongs to the documentation of those clients themselves.

I think that with this documentation update, we can close this issue. Please feel free to comment otherwise, thank you for your feedback and for your input with this issue.

Thanks, regards. Javier.