Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

packet 254 not implemented -- still struggling with #111

Open
marcstein opened this issue Sep 3, 2023 · 28 comments
Open

packet 254 not implemented -- still struggling with #111

marcstein opened this issue Sep 3, 2023 · 28 comments

Comments

@marcstein
Copy link

I know that this is not a new issue, but I'm trying to connect to a MySQL 8 server, which has been set to use legacy (v5.7) authentication. This is working in other languages that have an issue with the new authentication process (e.g. Nim), but I can't get it to work in Crystal. I've tried it on both OSX and Ubuntu. No difference.

I've tried the published solutions like CREATE/ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword'; and the user is definitely using the native_password auth.

Short of switching languages or databases, can anyone suggest something to try?

Thanks in advance for any suggestions!

[Exception.txt] (https://github.com/crystal-lang/crystal-mysql/files/12506665/Exception.txt)
@willy610
Copy link

Into MySQL 9 now. Everyone must encourage more security.
Workaround as for ver 8 is not possible anymore as I understand.
I will try too contribute by implementing the 'sha2_password'
There are work in progress as I understand.

Please give me links etc to what is achieved so far. (Especially Crystal source..)
(Someone locked into LUA. Perhaps also Rust will give some feedback. And Ruby and Js)

@ysbaddaden
Copy link
Contributor

The rust crate implements the protocol (not a wrapper) and has support for the caching_sha2_password auth. It would likely be helpful to understand how it works, in addition to the mysql-client C library maybe.

@bcardiff
Copy link
Member

The protocol documentation can be found at https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html

@willy610
Copy link

Thanks.
Find sources and also notes in this threads of course.
My approach is to implement caching_sha2_password only. >= mysql 9
Will remove older versions behavior in the source. In order to avoid attacks from smarter hackers than me.
Approach, but than we got 2 libraries?

Is golang worth looking at?

@bcardiff
Copy link
Member

I don't think that dropping the current authentication method is a good call. First it would be disruptive when upgrading an application, and second, I am not sure there is a risk in the client keep supporting old authentication scheme. Is the server that should be configured to not allow it. Am I wrong?

If needed we could make it harder to use old authentication, but I would not suggest dropping the support for it.

It seems that a couple of years ago I started to do some refactors to support sha2_password in master...bcardiff:crystal-mysql:sha2_password but I didn't finish it. I don't recall any specifics so feel free to ignore it. But the key seems to be extracting the list of authentication plugins available.

Please share if you are blocked, contributions are appreciated! 🙇

@willy610
Copy link

Thank you for the link.
Got your points on dropping old behavior.
I will do more rethinking on how make maintainable source.
Perhaps by enabling cutting methods, cutting case, avoiding complex conditions mixing compile time and run time too much etc
Than we have verifications on older version of mysql server. Work to do!

@willy610
Copy link

Just for the record:
One of the variants of 'caching_sha2_password' is now working.
That is the 'PasswordFastAuthSuccess' .
( And the old one 'mysql_native_password' works)

Same image works on

  • Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew)
    no implement of 'mysql_native_password'
    and on
  • Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew)
    implements 'mysql_native_password' only

Still a lot of work to do!!
By the way - allow me to say crystal and golang are a very good environment, maridb client is well enginered, Rust will not even compile the source. Don't mention the mysql original client source (C/C++). But I love C otherwise!

I don't now if it's matter just now but I'm working a git crystal/mysql source copy and not a cloned one.

@bcardiff
Copy link
Member

👏

I'm not sure how you are going to send a PR if you are not working from a fork. But let's not make that a stopper.

Feel free to send your progress somehow in case you rather have feedback at this stage.

Thanks

@willy610
Copy link

willy610 commented Dec 2, 2024

The capability 'perform_full_authentication' defined by mysql server seems to be hard to implement.
And perhaps also unnecessary?

Looking into the mysqlserver 'sql/aut/sha_2password.cc' I found
/*
We either failed to authenticate or did not find entry in the cache.
In either case, move to full authentication and ask the password
*/
if (vio->write_packet(vio, (uchar *)&perform_full_authentication, 1)) return CR_AUTH_HANDSHAKE;

This is the only placed where that capability is used.
So...
Use case:

  1. The client sends wrong user and/or password.
    How to provide new values in a 'perform_full_authentication' round trip?
    Suggestion is: raise 'denied'

  2. The user and/or password has been valid sofar but is actually changed by someone.
    How to provide new values in a 'perform_full_authentication' round trip?
    Suggestion is: raise 'denied'

So my suggestion at the moment is to not implement 'perform_full_authentication'.
(instead raise 'denied')
It will never be used!! And it will imply a mighty set of new crystal source.

I can't see any difference - as a Use Case - whether the DB.open holds fixed or parametric values of user/password.
Either it works or one get a 'perform_full_authentication'.
Don't try to perform a meaningless? 'perform_full_authentication' but pick up proper values into source or parameters in the DB.open

What to do?

Besides that the auth 'caching_sha2_password' and 'mysql_native_password' works as mentioned above
so now I'm working on commands.
There are some issues with messages sequencing.
And styling, documentation, test/verifications and more

@bcardiff
Copy link
Member

bcardiff commented Dec 3, 2024

Adding support to just caching_sha2_password seems fine.

If you are not familiar with Docker and GitHub actions tweaking those to add CI for the additions can happen later, by someone else. No worries.

If you have instructions on how you setup a mysql using the newer authentication and that works for you, I can take care of the CI setup probably. But feel free to keep working on those for sure.

@willy610
Copy link

willy610 commented Dec 3, 2024

Thank you for your patience.

I am 'struggling' to set up a proper folder with my changes so that that folder can be easily referenced as 'dependendcy: - mysql: - path: 'myworkingmysqllib' in all my old crystal mysql project shard.yml files.
Any hints/links on that topic?

Later on we can decide how to continue. At least I will match a cloned version
but it might end up in a .zip file ? or a PR or some kind of CI. OK?

@bcardiff
Copy link
Member

bcardiff commented Dec 4, 2024

In your apps shard.yml or shard.override.yml you should have

dependencies:
  mysql:
    path: /path/to/your/crystal-mysql

After that you need to do shards install, that should leave a symlink in your apps ./lib/mysql pointing to your working directory. You can also this step manually if you prefer.

With that you should be able to compile your apps with your crystal-mysql working copy.

Feel free to attach a .zip or send a link to it here in this issue if doing git/github is a blocker.

@willy610
Copy link

willy610 commented Dec 5, 2024

In order to earn time I will attach a zip file here.
I have built and run it on (as above)

  • Mac Mini M4 Pro and mysql Ver 9.0.1 for macos15.1 on arm64 (Homebrew)
    no implement of 'mysql_native_password'
    and on
  • Mac Mini M2 Pro and mysql Ver 8.0.3 for macos14.2 on on arm64 (Homebrew)
    implements 'mysql_native_password' only
  1. I have done nothing on README.md, version.cr and no spec
  2. There is an issue with crystal spec 'should connect with credentials'.
    Got syntax error from mysqlserver
  3. Error messages from crystal spec are truncated
  4. There might be more issues around any user/password commands
  5. I introduced 'draining' of inmessages in order to really consume the TCP bytestream. Necessary?
  6. I have an active Github Desktop showing changes on my modified clone but I'm really unsure how to continue. Fork to my account? I kind of looking for contribution credits, in the long term, to show my patience family!

crystal-mysql.zip

@willy610
Copy link

willy610 commented Dec 5, 2024

Hera is a good link too on ALTER USER ...

https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

@willy610
Copy link

willy610 commented Dec 8, 2024

Zipped source above build and executed on

  • mysql Ver 8.0.28 for macos10.15 on x86_64 (Homebrew)
  • on user' test' the capability classic 'mysql_native_password' was invoked
  • Crystal 1.8.2 (2023-05-09), LLVM: 15.0.7

@bcardiff
Copy link
Member

FYI I tested the code locally and it works. I need to do some cleanups and probably specs/CI because currently a test user is created with old auth method.

Still on my radar. Thanks for the patch!

@bcardiff
Copy link
Member

@willy610 in your tests, what kind of connection are you using with MySQL? Unix Socket, plain unsecured socket, or TLS?

@willy610
Copy link

willy610 commented Dec 17, 2024 via email

@willy610
Copy link

@bcardiff Just setting up 'plain unsecured socket' - that is no local host - between a mysql Ver 8.0.3 and a Ver 9.0.1
Of course there were error in connection phase.
I will immediately solve this. Sorry for my stubborn stick to the 'issues' track.

@bcardiff
Copy link
Member

I was checking your contributions and they do work in some scenarios but not all. That's fine, it's still a big step forward.

With your contributions we are able to connect to mysql 8.0 using caching_sha2_password. Most of the specs pass. But some specs creates users and perform a FLUSH PRIVILEGES. This invalidates the password cache as explained in https://dev.mysql.com/blog-archive/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

The code path that would authenticate in that situation is not implemented in your previous contributions. So only cached passwords are supported. That could be fine as a step forward!

crystal-mysql only supports plain unsecured socket connection. A TCP socket is always used. Unfortunately that is the kind of connection that seems discouraged by MySQL going forward.

Time for some maintenance in this shard to unblock things I guess 😅 .

@willy610
Copy link

I didn't see this coming with none localhost...

When launching a mysql server without 'localhost' two connection types are accepted

  • Plain access on default port imply the client must implement trusted authentication according to 'cachingSha2PasswordPerformFullAuthentication' capability. Misleading name
  • TLS connection. I think this require some refactoring in using OpenSSL::SSL::Socket::Client ?

Two execution paths but probably partly using shared logic/architecture/knowledge.

Will elaborate the paths and try to implement

@willy610
Copy link

willy610 commented Dec 21, 2024 via email

@willy610
Copy link

Will look into secure connections to remote mysql servers. And unsecure.
I will focus on the one preferred by mysql.
https://dev.mysql.com/doc/refman/8.4/en/encrypted-connection-protocols-ciphers.html

Secure connections have impact on resolving caching_sha2_password with full_authentication

So my work order will be:

  1. Secure connections and unsecure
  2. Resolving caching_sha2_password with full_authentication

@willy610
Copy link

I need some hints on how to continue with cypher in the caching_sha2_password authentication.

Of course I want to use a crystal native implementation if it's there.
Like in cipher = OpenSSL::Cipher.new("some_ident")

Looking into mariadb client (sha256_pw.c) I find they are using EVP_PKEY_encrypt from openssl.

Then in https://docs.openssl.org/3.3/man3/EVP_PKEY_encrypt/ I found that the cypher is a OAEP (for RSA keys)

Also golang (mysql.go) use - implement - what they say RSA-OAEP in there rsa.go

Rust talks about PKCS#1 OAEP and refers to https://datatracker.ietf.org/doc/html/rfc8017#section-7.1
in the source https://docs.rs/rsa/latest/src/rsa/oaep.rs.html#40-49

Asking openssl openssl ciphers about names gives a list but noting on 'OAEP' or 'EVP_PKEY'

So if a cipher according to rfc8017 is present in Crystal OpenSSL::Cipher("???") I want to find it with it's proper name.
Else it's implementing time for me at least for encrypt (for use at the client side only)

@ysbaddaden
Copy link
Contributor

The auth_sha256_client function from sha256_pw.c in mariadb's source code is pretty explicit and lucky for us: it even uses plain OpenSSL.

You need the server public key, either provided with the connection or will need to request it. Then it is used to initialize the EVP_PKEY_CTX. OpenSSL will then use the information from the pubkey to set the context up (hash digest, ...).

You can go with raw LibC calls to OpenSSL for starters. There likely isn't Crystal bindings for EVP_PKEY_CTX yet. We can use the experience here to add them to stdlib later. But let's get this working first.

@willy610
Copy link

willy610 commented Feb 3, 2025

Status report.

  1. My usage of embedding c openssl does not produce correct result. The function is isolated so anyone
    more experienced with integration crystal and C might look into it. Param passing etc might be wrong.
    It's also a distinct entry for native implementation.
  2. I have analyzed all messages send and received towards the mysql server and synthesized
    it to an FSM (kind of Mealy Machine) and have made an ad hoc implementation. Works well.
    Will be documented separately. I think the FSM pattern is very powerful and easy to maintain and extend as mysql develops.
  3. Have some bug when retrieving resulsets on mysql 8.3.0
  4. TLS not in focus at the moment but I will/want look int that later on.

I will provide a zip on my development 'lib' still with some traces etc later this week. Can be used for openssl debug

@bcardiff
Copy link
Member

bcardiff commented Feb 3, 2025

Thanks! I am working on TLS support actually :-)

@willy610
Copy link

willy610 commented Feb 8, 2025

Attached you find my latest contribution.

The embedding of c openssl still does not work.

You also find a separate file FSMConnection.html. That FSM is implemented in the connection.cr.
The machine is aware if tls is used and will avoid the roundtrip with RSAOAEP which is legal. So I'm really keen on the design of your tls and hope my assumptions are valid and useful.

For the record: Anyone with general interest in Finite State Machine could download the attachment and look inte the FSMConnection.html

mysql-20250208.zip

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@ysbaddaden @bcardiff @marcstein @willy610