Support for caching_sha2_password plugin

[Pio82]

Running MySQL 8.0 on Linux Ubuntu 18.04 unable to login with RMariaDB package on RStudio. User password plugin uses caching_sha2_password on MySQL 8.0. Is this something RMariaDB will support? I'd rather not have to change the plugin to the less secure mysql_native_password system.

[krlmlr]

Thanks for raising this.

I don't have MySQL 8.0 to test this, but I guess I can compose one using the instructions on the Docker site.

Could you please share the code and the output?

[J-Benitez]

This is an issue which I am also experiencing as I connect to a MySQL v8.0. More information about the issue can be found at https://mariadb.com/kb/en/library/authentication-plugin-sha-256/. MySQL 8 changed the default authentication from mysql_native_password to caching_sha2_password. The standard con<-dbConnect(RMariaDB:MariaDB(),dbname="schema",host="xxx.xxx.xxx.xxx", port=3306,user="username",password = "password") throws the

Error in connection_create(host, username, password, dbname, as.integer(port), :

Failed to connect: Plugin caching_sha2_password could not be loaded: The specified module could not be found.

[Rhydderch]

Did anyone found a solution for this ?
When I use the other method of authentication, which isn't supported anymore by MySQL v8.0, I get this error : Failed to connect: Plugin sha256_password could not be loaded

[Rhydderch]

Apparently both (sha526 and caching_sha2) are not longer supported in MySQL 8 and in mariaDB.
https://mariadb.com/kb/en/authentication-plugin-sha-256/

Apparently this is the new plugin to connect:
https://mariadb.com/kb/en/authentication-plugin-ed25519/
Is it implemented in the package ?

EDIT:
I can connect fine with:
user_data_db<-dbConnect(RMariaDB::MariaDB(), user='3c_admin', password=localuserpassword, dbname='3c_dashboard', host='localhost')

However, the plugin errors appear as soon as I try to use the config file this way
user_data_db<-dbConnect(RMariaDB::MariaDB(),group=rmariadb.db)

[krlmlr]

Plugins are part of a server's configuration. I don't understand how this is relevant for the client library?

[arencambre]

@Rhydderch can you verify your "not longer supported" statement? https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html says that "caching_sha2_password is the default authentication plugin" for MySQL 8.0.

[krlmlr]

On Ubuntu 18.04 I see:

$ apt-file search caching_sha2_password
# no output
$ apt-file search client_ed25519
libmariadbclient18: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/client_ed25519.so

So the newer client_ed25519 plugin seems to be available in the libmariadbclient18 package, but caching_sha2_password can't be found anywhere. The development package for the client library doesn't contain any trace of plugins, as shown by apt-file show libmariadbclient-dev. This makes me think that we need somehow to include caching_sha2_password.so in the libmariadbclient18 package.

I'm having a hard time tracking down the source of this package. I see:

$ apt show libmariadbclient18
Package: libmariadbclient18
Version: 1:10.4.17+maria~bionic
Priority: optional
Section: libs
Source: mariadb-10.4
Maintainer: MariaDB Developers <[email protected]>
Installed-Size: 13.3 kB
Provides: libmariadbclient18
Depends: libmariadb3 (= 1:10.4.17+maria~bionic)
Replaces: libmariadbclient18
Homepage: http://mariadb.org/
Download-Size: 3'224 B
APT-Sources: http://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.4/ubuntu bionic/main amd64 Packages
Description: Virtual package to satisfy external libmariadbclient18 depends
 MariaDB is a fast, stable and true multi-user, multi-threaded SQL database
 server. SQL (Structured Query Language) is the most popular database query
 language in the world. The main goals of MariaDB are speed, robustness and
 ease of use.
 .
 This package provides compatibility symlinks for binaries that expect to find
 libmariadbclient.so.18 will automatically use libmariadb.so.3 instead.

N: There are 4 additional records. Please use the '-a' switch to see them.

Many blog posts and bug reports mentioning caching_sha2_password really recommend downgrading to insecure auth. I wish we could do better here, I'm not sure how. Has this been discussed in the MariaDB issue tracker?

[jeroen]

Would it be possible to test this again with the latest CRAN version of RMariaDB and see what error you get this time?

[grooverdan]

I suspect this is purely packaging and not RMariaDB's fault.

https://mariadb.com/kb/en/authentication-plugin-sha-256/ shows the continued support of caching_sha2_password in the client libraries that RMariaDB uses.

@krlmlr is on the right track with the libmariadbclient18 which was based on MariaDB-10.0 and didn't have caching_sha2_password.so.

This package is renamed to libmariadb3 in later Debian/Ubuntu versions but is essentially the same.
The debian bug 962597 is the root cause of this issue where other users of connection libraries cannot access caching_sha2_password due to it not being packages.

This was fixed in Debian bullseye but not buster. Likewise fixed in Ubuntu groovy but not focal.

Even MariaDB didn't fix this in their Debian packaging until 10.5 (I've just created MDEV-24728 - and will get this into an upstream release).

Every packaged version based on MariaDB-10.1 onwards does have caching_sha2_password source code in its original.

I recommend following this up with a Ubuntu focal bug (bionic has older connector C version), and (another) Debian buster bug. Along with every distro MariaDB-10.1+/MariaDB-connector-c-3.0.8+ based package that fails to install caching_sha2_password.so.

Include links to issues back here to save duplication of bug reporting effort.

[krlmlr]

Thanks @grooverdan for your detailed analysis.

I don't have the resources to take this upstream to Debian Buster and Ubuntu Focal and to other distros. Leaving this open for now, please post links to relevant upstream issues here.

[grooverdan]

Upstream MariaDB packages MDEV-24728 (change submitted) (sha256_password.so and caching_sha2_password.so)
https://salsa.debian.org/grooverdan/mariadb-10.3/-/pipelines/224496 - I will attempt to shortcut duplicating a debian bug if this builds right (only caching_sha2_password.so per Debian bug 962597).

And to clarify an earlier misunderstanding, these are client shared libraries that are automaticly loaded as part of the connection to complete the authentication protocol with the server.

[grooverdan]

Ubuntu focal 20.04 bug submitted: https://bugs.launchpad.net/ubuntu/+source/mariadb-10.3/+bug/1913676

[jeroen]

On Windows we solved this by disabling this plugin, because it doesn't seem to be actually required? See https://github.com/r-windows/rtools-packages/blob/master/mingw-w64-libmariadbclient/PKGBUILD#L63

On Linux we depend on the ubuntu packagers, indeed.

[grooverdan]

On Windows we solved this by disabling this plugin, because it doesn't seem to be actually required? See https://github.com/r-windows/rtools-packages/blob/master/mingw-w64-libmariadbclient/PKGBUILD#L63

You solved lack of support by ensuring it doesn't exist? I'm sure I'm missing something. If there where tests that ran against a MySQL-8.0 server that had a default created user, or a MariaDB instance with an ed25519 authenticated user, then this would show up pretty quickly. These plugins are only required at runtime. Sorry I'm not familiar with R tools to know if this is build time or runtime.

A slow flow of upvotes on this RMySQL Stack Overflow question indicated people are still hitting it. The original question was a Windows user.

Also Debian Buster on way to getting fixed.

[jeroen]

The problem was that the driver would try to load a shared library for the caching_sha2_password plugin, which did not exist, and therefore crash. It turned out that most users don't need the caching_sha2_password plugin in the first place. Explicitly disabling this plugin seemed to solve the problem . We fixed this 2 months ago on Windows, so the issues you quote predate that.

Sorry I'm not familiar with R tools to know if this is build time or runtime.

Everything in R is statically linked, so everything is build time: https://github.com/r-windows/rtools-packages/blob/master/mingw-w64-libmariadbclient/PKGBUILD#L57-L67

[arencambre]

The original question was a Windows user.

That is an important point. I am also a Windows user. I see a lot of emphasis on Linux-related solutions in the comments. Will this cause a fix for Windows users, too?

[jeroen]

We already fixed it for Windows, are you still experiencing this error?

[krlmlr]

I thought certain server configurations require that the client has the "caching SHA2" plugin available?

[jeroen]

So far, nobody seems to have actually needed that plugin. Given that it the plugin was broken, and it would cause a crash even in cases where the plugin isn't actually needed, the best course of action was to disable it.

If the plugin were to be fixed and can be statically linked in future versions of libmariadblcient, we could try to enable it.

[arencambre]

We already fixed it for Windows, are you still experiencing this error?

@jeroen Apologies. I may have mixed up this with a similar issue. I think I may have participated in confirming resolution on Windows.

[grooverdan]

I thought certain server configurations require that the client has the "caching SHA2" plugin available?

Yes. Specific user definitions. Like the default mysql-8.0 configuration.

So far, nobody seems to have actually needed that plugin. Given that it the plugin was broken, and it would cause a crash even in cases where the plugin isn't actually needed, the best course of action was to disable it.

Odd. Do you have a link to this issue? I haven't been able to find it.

https://github.com/r-dbi/RMariaDB/runs/1773985592?check_suite_focus=true#step:12:13 tests against the mariadb server which includes the connector/C and the caching_sha2_password.dll. Is there a current test which would trigger the crash?

If the plugin were to be fixed
and can be statically linked in future versions of libmariadblcient, we could try to enable it.

Building with with -DCLIENT_PLUGIN_CACHING_SHA2_PASSWORD=STATIC , -DCLIENT_PLUGIN_CLIENT_ED25519=STATIC was fixed after 3.1.11 in the latest 3.1 branch.

[grooverdan]

In what has been an extraordinary amount of time, https://bugs.launchpad.net/ubuntu/+source/mariadb-10.3/+bug/1913676 finally got resolved and the libmariadb3 (10.3.30-0ubuntu0.20.04.1) package that includes caching_sha2_password.so

So for the Linux side I think this is fixed in all possible scenarios where it can be fixed (Ubuntu except 18.04 which isn't fixable, all Debian, all other distros, MariaDB upstream packages).

Windows, currently no upstream open caching_sha2_password bugs.
Notes:

• mingw related isn't officially support for mariadb-connector-C
• the caching_sha2_password ignores the OPENSSL and forces WINCRYPT aka bcrypt.h. Are these the schannel/mingw compatibility problems referenced in the build? I must admin I'm not familary at all with windows libraries.

So to resolve the windows it seems one of the mingw or crypt libraries needs to be addressed. So if there's a demand like @arencambre indicated, work out which way to resolve it. Create a JIRA CONCPP bug on the MariaDB side if needed. Removing the code that forces WINCRYPT in mariadb and see if that works on OpenSSL under windows would be my first suggestion. Same applies to ed25519.

[vaintroub]

BCrypt is not a problem, it exists since Windows Vista, which is EOL since many years. I think the comment https://github.com/r-windows/rtools-packages/blob/master/mingw-w64-libmariadbclient/PKGBUILD#L43 is wrong, and a decent at-least-Vista(i.e 15 years after Vista) compile toolchain should deal OK with it.

[jeroen]

CRAN still targets windows vista at this point.

[vaintroub]

Yes , and BCrypt targets that too. It does not target Windows XP.

[jeroen]

Well I had tried it and it failed: https://github.com/r-windows/rtools-packages/runs/2885880624

You can send a PR to rtools-packages if you can make it work.

[vaintroub]

I can make it work, without anything special. I do not know how rtools-packages build, but here is my attempt
https://gist.github.com/vaintroub/f98eb8d5573c7b66a10439da3a00e5e8

And yes, this is the latest-greatest mingw64 (the one for which I found a description how to download), and there is a couple of gcc warnings, but everything builds.

[krlmlr]

@vaintroub: Would you like to send a PR to https://github.com/r-windows/rtools-packages? The README explains the building process.

AFAIR we use a fixed mingw64 version here, does it still work there?

[vaintroub]

@krlmlr . I do not even know which version of mingw you're using. I just posted a note, that I can build with mingw64 without any patches and workarounds. I do not know R build process, I just know how to build mariadb with cmake and a C compiler.

[krlmlr]

@vaintroub: Thanks. The R toolchain uses gcc 8.3.0, https://github.com/r-windows/rtools-packages/runs/2885880624#step:4:363. The toolchain is documented here: https://cran.r-project.org/bin/windows/Rtools/.

One difference between https://gist.github.com/vaintroub/f98eb8d5573c7b66a10439da3a00e5e8 and https://github.com/r-windows/rtools-packages/runs/2885880624 seems to be that the former builds a dynamic plugin library, and the latter uses static linking. Can you still build mariadb with statically linked caching_sha2_password?

[vaintroub]

Can you still build mariadb with statically linked caching_sha2_password?

Sure, by passing -DCLIENT_PLUGIN_CACHING_SHA2_PASSWORD=STATIC to cmake.

[krlmlr]

This might be fixed on Windows in version 1.2.0.9001. Can you please try installing from source or from r-universe (as soon as it's available there, check at https://r-dbi.r-universe.dev/ui#builds)? Thanks!

[jeroen]

I have also pushed this change in RMySQL to CRAN.

[krlmlr]

RMariaDB update sent to CRAN. Please open a new issue if problems persist.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.