Having caching_sha2_password authentication issues with mysql 8.0.30

[abdulchaudhrycoupa]

Everything works, in that I don't see any issues with trilogy when running with mysql_native_password auth but the moment I switch to caching_sha2_password things break down in weird ways.

I am trying to understand where it fails but I am still learning how this works and I am only seeing a packet error.

It seems to perform authentication and create a successful connection to the mysql server when switching the client over to caching_sha2_password but then it fails during packet parsing for some reason.

I can see in show processlist during the handshake that the user moves from unauthorized to the user name but then it fails on the client just as its about to call trilogy_auth_recv or maybe its calling it too often with the wrong packet sequence/payload and the server disconnects. I am still debugging the issue.

I am using a debug version of the mysql server version 8.0.30-debug and I was about to debug the server side to see if it was indeed closing the connection because it didn't like something from the client.

Is this a known issue with caching_sha2_password and trilogy?

[composerinteralia]

Does #21 look related to what you were seeing? I've just released a new version of trilogy that includes that change.

[abdulchaudhrycoupa]

@composerinteralia just pulled the latest, that doesn't seem to help. The error that I am seeing is TRILOGY_UNEXPECTED_PACKET. The issue I am seeing might still be related to auth switch request but it seems to happen when it succeeds, if that makes sense. As in, the user transitions from unauthorized to authorized but the client fails with TRILOGY_UNEXPECTED_PACKET.

Oh and my password is not blank, so I don't think it is related to that PR.

[composerinteralia]

It seems like I'm able to reproduce this (or at least something like it). I pushed up a failing test and do see a TRILOGY_UNEXPECTED_PACKET error. Does this capture the problem you are having?

[abdulchaudhrycoupa]

@composerinteralia that does reproduce the problem exactly.

created PR - #28

This seems to fix the test but do look into this in more detail as you might want to capture the payload that the server is sending, in this case it just seems to be the plugin information which we don't care about that much, for other schemes, such as FIDO it might be more detailed.

From the documentation it seems only mysql_native_password returns ok, the other non challenge-response auth plugin schemes return auth more data.

See https://dev.mysql.com/doc/internals/en/connection-phase-packets.html#packet-Protocol::AuthMoreData

Ideally, this should capture the data payload but for now this seems to pass tests.

[composerinteralia]

Thanks for digging into that! The tests aren't green, but the error has changed to trilogy_auth_recv: 1159 Got timeout reading communication packets, so that's something.

[abdulchaudhrycoupa]

@composerinteralia it seems to fix the test suite for me for the mysql server version that I am using, but I have a very strange version of the mysql server, its 8.0.30-debug as its built from source in debug mode, it might be breaking on other versions. Ah, it seems to work on mac osx only.

[breisig]

Trilogy not supporting caching_sha2_password is a HUGE issue. MySQL 8.1.0 was released and 'mysql_native_password' is deprecated and will be removed in future versions of MySQL. [ https://dev.mysql.com/doc/relnotes/mysql/8.1/en/news-8-1-0.html ]

Even the mysql2 gem supports the newer 'caching_sha2_password' authentication method.

As a hack/workaround, I switched the mysql credentials to use the 'mysql_native_password' method and it worked [from 'caching_sha2_password']

[ianks]

Could be total red herring, but I wonder if the known missing trailing \0 in auth handshake is related?

[andyjeffries]

I believe caching_sha2_password is the default with official docker image based MySQL installations, so Trilogy doesn't work in our staging/production environments either. This is a deal breaker for me (which is a shame because I've had "move to Trilogy" on my todo list for a while now) and I'm sure us too.

[onyxraven]

I believe caching_sha2_password is the default with official docker image based MySQL installations, so Trilogy doesn't work in our staging/production environments either. This is a deal breaker for me (which is a shame because I've had "move to Trilogy" on my todo list for a while now) and I'm sure us too.

FWIW, if you add your own my.cnf into the container (a layer), you can change this default. The root and default users created with the env vars are then created with the native plugin. This is currently working for us in dev/CI.

/etc/mysql/my.cnf

[mysqld]
host-cache-size = 0
skip-name-resolve

default-authentication-plugin = mysql_native_password

But: hopefully we can see caching_sha2_password support soon, since this is a bit of a temporary workaround that, according to mysql upstream, will go away at some point.

[breisig]

Not supporting caching_sha2_password is a show stopper for us.

[gs-deliverists-io]

Found a working solution:

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';
ALTER USER 'yourusername'@'%' IDENTIFIED WITH mysql_native_password BY 'youpassword';

[AAlvarez90]

@composerinteralia This issue is over 1 year old. Is there a way that can be addressed any time soon? I would love to introduce trilogy to my organization but we do use caching_sha2_password and there is no way they will switch back to mysql_native_password.
All in All I wonder if Trilogy will really gain traction. Sounds like the release cycles are slowing down and big issues like this are sitting idle for a while..

[composerinteralia]

Is there a way that can be addressed any time soon?

Somebody would need to implement caching_sha2_password. It's not something we are actively working on at GitHub right now.

[andyjeffries]

Any tips on where the current mechanism is implemented in Trilogy? And where in the MySQL2 client library or docs you found out how to implement it? Any tips? I’m sure like a lot of others in the community this is a huge issue and we’d love to help, given some pointers in the right direction.

[abdulchaudhrycoupa]

@andyjeffries

Oh I created a quick PR a while back to fix this ( see #28 ), the code in question is in client.c. Trilogy does not use the mysql client library, it implements the mysql protocol in C without using the mysql client library. The mysql client protocol is documented here - see MySQL Protocol. The specific details on the caching_sha2_password plugin are here Caching_sha2_password authentication and work in multiple phases.

The fix I have captures the servers auth more data packet - see Protocol::AuthMoreData - which Caching_sha2_password doesn't use per se.

Since this is authentication related, that server packet - AuthMoreData- should be kept and looked at in more detail depending on the authentication provider and connection phase. A plugin such as FIDO may use this packet so this needs to be handled on a per provider basis.

Feel free to use the PR - #28 - and/or create a new PR.

Note that those links above change, the mysql documentation changes the links with each release and so they might not work at a later date.

[andyjeffries]

Sorry maybe I confused things with what I wrote 🤣

I meant that it felt like they were the only large rails group that might be working on it (as I hadn't seen anyone from Shopify or Rails Core post to say that they were aware of it), and that the confirmation was that they weren't.

I'd certainly consider Shopify and Rails Core to be large groups, and wasn't aware it was on their minds (hence I posted an issue in Rails today). For comparison at my startup there's me and one other guy, and we don't have spare capacity to work on it (unless ABSOLUTELY NO ONE else can/is).

So your confirmation that you are working on it, and both Shopify and Rails Core are aware is absolutely perfect.

[eileencodes]

To be honest I didn't comment on this issue that I was working on it until now because I didn't want to get asked constantly about when it was going to be done.

The thing about open source (life?) is no one has spare capacity. It's just moving around of priorities and what feels most important right now. Currently I'm working on something else that's more important so while I have a branch started for this, I'm not working on it this week. Then I'm off for 2 weeks.

It's on the radar, it's being worked on, but this really isn't nearly as simple to implement as described on this issue.

I will post an update when the PR is ready.

[andyjeffries]

I absolutely agree. For what it's worth, now I know it's "in hand" I definitely won't chase you, my concern was this would go out and be the default without it working.

It felt, for want of a better term, under the radar. And even though I don't have any spare time to contribute to much open source (outside of my own open source gem Flexirest), I would hate for our community have major issues if this went out as default and broke left right and centre. As you say it's priorities, and my own not-for-profit martial arts club takes up my spare time.

Anyway, good luck with it - if you want someone to try your work in other environments when you feel it's ready, feel free to tag me and I can help do that.

Enjoy your Christmas break! (and thanks for the comments back here, genuinely appreciated)

[abdulchaudhrycoupa]

thanks for the update @eileencodes - agreed, this is a non trivial fix - closed PR #28 as I'm not convinced its safe for use - enjoy working on the fix - 😆

[eileencodes]

Ok we have a PR ready #165. As noted earlier this is a non-trivial change so I'd appreciate if you only comment on the PR if you:

• Tested this in your app and found a bug.
• Tested this in your app and found it to be working correctly.
• See a security issue.

I'd like avoid "looks good" reviews without actual testing. Thanks for understanding. ❤️

Note that we haven't and won't be adding support for using caching_sha2_password without TLS or over a unix socket.

[abdulchaudhrycoupa]

@eileencodes - I thought I would comment here rather than on the PR as this is likely just something I missed or my weird setup and if so forgive me.

Unix domain sockets works but the moment I switch to TLS proper, I get the error - 1043 Bad handshake - thats it.

I had to recompile trilogy using openssl 3 rather than 1.1 and the code seems to support it and compiles.

diff --git a/Makefile b/Makefile
index 948c719..2339fa6 100644
--- a/Makefile
+++ b/Makefile
@@ -13,8 +13,8 @@ EXAMPLES = example/trilogy_query
 UNAME_S := $(shell uname -s)

 ifeq ($(UNAME_S), Darwin)
-       CFLAGS += "-I$(shell brew --prefix openssl@1.1)/include"
-       LDFLAGS += "-L$(shell brew --prefix openssl@1.1)/lib"
+       CFLAGS += "-I$(shell brew --prefix openssl)/include"
+       LDFLAGS += "-L$(shell brew --prefix openssl)/lib"

I tried changing the example/trilogy_query.c to set the following hardcoded values, the certs are the defaults.

connopt.ssl_capath = "mysql-server/build/data/ca.pem";
connopt.ssl_cert = "mysql-server/build/data/client-cert.pem";
connopt.ssl_key = "mysql-server/build/data/client-key.pem";
connopt.ssl_mode = TRILOGY_SSL_VERIFY_CA;  
connopt.ssl_cipher = "TLS_AES_128_GCM_SHA256";
connopt.flags = TRILOGY_CAPABILITIES_SSL;

I also tried TRILOGY_SSL_PREFERRED_NOVERIFY and TRILOGY_SSL_REQUIRED_NOVERIFY

The version of the mysql server I am using is mysqld 8.3.0-debug which uses openssl 3.

The mysql client works like so

mysql --tls-ciphersuites=TLS_AES_128_GCM_SHA256 --protocol=tcp -h localhost --ssl-mode=VERIFY_CA --ssl-ca='mysql-server/build/data/ca.pem' --ssl-cert='mysql-server/build/data/client-cert.pem' --ssl-key='mysql-server/build/data/client-key.pem' -u <user> -p

Confirmed the mysql client connection type using (its empty if its using unix domain sockets)

SELECT THREAD_ID FROM performance_schema.threads where connection_type = "SSL/TLS";

Its likely I did something wrong in example/trilogy_query.c but does trilogy have a debug mode that logs the TLS handshake byte sequence, basically whatever openssl logs in debug mode?

[andyjeffries]

It compiles fine for me (in Docker using ruby:3.3.0-alpine), but it doesn't work for me.

trilogy_auth_recv: TRILOGY_UNSUPPORTED
Couldn't create 'feature-pr1946' database. Please check your configuration.
rake aborted!
Trilogy::QueryError: trilogy_auth_recv: TRILOGY_UNSUPPORTED (Trilogy::QueryError)
/usr/local/bundle/bundler/gems/trilogy-e5af495b7b23/contrib/ruby/lib/trilogy.rb:18:in `_connect'
/usr/local/bundle/bundler/gems/trilogy-e5af495b7b23/contrib/ruby/lib/trilogy.rb:18:in `initialize'

Trying to connect to a standard/default docker.io/library/mysql:8.0.33 container over TCP within a Kubernetes cluster.

Just to also add thanks for your efforts so far @eileencodes , and I'm happy to keep testing as required (I was fairly vocal about this issue, so want to ensure I'm being fair in testing as often as you need as a thanks for your efforts)

[eileencodes]

My mac, Trilogy CI, and Shopify CI all pass so I'm not sure how to reproduce this. I don't really have much more time I can spend on this so a docker container that reproduces would help a lot @abdulchaudhrycoupa.

@andyjeffries are you using TLS/SSL or a unix socket? We're not going to support this feature without one of those, it's too complex to implement. The error you're seeing is thrown if use_ssl and has_unix_socket are both false.

[eileencodes]

Ok I see you're using the example @abdulchaudhrycoupa. I can reproduce but also get the same thing on main so...I don't think it's related to my PR. Here's the script we're using to test the implementation locally:

require "trilogy"

DEFAULT_HOST = ENV["MYSQL_HOST"] || "127.0.0.1"
DEFAULT_PORT = (port = ENV["MYSQL_PORT"].to_i) && port != 0 ? port : 3306

defaults = { 
  host: DEFAULT_HOST,
  port: DEFAULT_PORT,
  username: "caching_sha2",
  password: "password",
  ssl: true,
  ssl_mode: Trilogy::SSL_PREFERRED_NOVERIFY,
  tls_min_version: Trilogy::TLS_VERSION_12,
}

c = Trilogy.new defaults
p c.query "SELECT (1)"

[andyjeffries]

I'm not using TLS/SSL nor a unix socket. I've spent the afternoon so far trying to configure the pods to use TLS now, and I'll report back when I have it sorted.

As a minor part of this PR though, could that hint be added to the error - didn't feel like it was obviously that when I read the error message - and it feels like this could bite quite a few people going forward.

[eileencodes]

I've updated the error to be more clear, but I did mention this will not work without TCP over TLS or a unix socket in my original comment and on the PR.

[andyjeffries]

I'm sure you did, wasn't blaming you, just saying that I'd forgotten and I'm sure others would forget or not notice too 👍

[abdulchaudhrycoupa]

@eileencodes - ah ok, probably a weird issue with the example. The ruby code works and confirmed the connection used TLS using

SELECT THREAD_ID FROM performance_schema.threads where connection_type = "SSL/TLS";

[abdulchaudhrycoupa]

@eileencodes - ah so, I do get Bad handshake on the mysql server when I change the ruby code to verify the CA

require "trilogy"

DEFAULT_HOST = ENV["MYSQL_HOST"] || "127.0.0.1"
DEFAULT_PORT = (port = ENV["MYSQL_PORT"].to_i) && port != 0 ? port : 3306

defaults = {
  host: DEFAULT_HOST,
  port: DEFAULT_PORT,
  username: "caching_sha2",
  password: "password",
  ssl: true,
  # ssl_mode: Trilogy::SSL_REQUIRED_NOVERIFY,
  ssl_mode: Trilogy::SSL_VERIFY_CA,
  # ssl_mode: Trilogy::SSL_VERIFY_IDENTITY,
  ssl_capath: '/mysql-server/build/data/ca.pem',
  ssl_cert: '/mysql-server/build/data/client-cert.pem',
  ssl_key: '/mysql-server/build/data/client-key.pem',
  tls_min_version: Trilogy::TLS_VERSION_12,
}

The server reports this

2024-03-14T11:38:32.431437-07:00 17 [Note] [MY-010914] [Server] Bad handshake

[eileencodes]

The bad handshake isn't caused by my PR, the handshake happens before auth. You'll see the same on main with a different user (can't use caching_sha2).