Connecting to MySQL 8.0 requires non-default settings

[CaptTofu]

Problems:

• MySQL 8.0 doesn't work with TLS by default if not new plugin transmits password clear text
  Either use TLS
  Request public key of the server (not enable by default, MIM attack)
  Try and connect, if get cert error message, handle, reconnect (hackish, pain to maintain)
  caching SHA2 plugin
  if make connection over TLS cache on server

Options:

• Enable TLS by default
  ** how to roll out without breaking?
  ** compile time option?
  ** dev release?

[dveeden]

MySQL 8.0 ships with:

• SSL/TLS enabled by default
• caching_sha2_plugin authentication

DBD::mysql by default:

• Has SSL/TLS disabled

This results in the connection failing as the caching_sha2_plugin needs either SSL/TLS or RSA for the initial connection. Once the server has the entry in the cache secure connections are not required.

What the user has to do is:

1. Enable SSL/TLS (mysql_ssl=1)
2. Specify the RSA public key of the server (mysql_server_pubkey=/path/to/pubkey.pem)
3. Enable RSA public key requests (mysql_get_server_pubkey=1)

Option 3 is not secure as this allow a MitM attack. (attacker specifies its own pubkey)
Option 2 can't be enabled by default as we don't know where the pubkey is etc.
Option 1 can be the default, but this is a change in behavior other users might not expect.

Other options:
4. Catch the connection error and re-try with SSL/TLS enabled (hackish)
5. Make TLS default a compile time option
6. Make TLS the default when compiling against Oracle MySQL 8.0

One thing to avoid is the situation where the defaults depends on too many variables (compile time option, server version, client version, MySQL vs. MariaDB).

[vicigel]

it seems that using mysql_get_server_pubkey=1 to connect to mysql 8.0 does not work, the error is:
failed: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection
while using mysql_server_pubkey=/path/to/pubkey.pem can do it, is it a bug, or my usage has some problem?
I try it in two ways, one is using in dsn string and the other is in my.cnf, both do not work
DBI->connect("DBI:mysql:database=$database;$host;$port;mysql_get_server_pubkey=1", $user, $password) or die $DBI::errstr;

DBI->connect("DBI:mysql:database=$database;$host;$port;mysql_read_default_file=/etc/my.cnf", $user, $password) or die $DBI::errstr;
cat /etc/my.cnf
[client]
mysql_get_server_pubkey=1
can you give any help? Thanks

[dveeden]

I'm not sure why they mysql_get_server_pubkey thing isn't working for you. Are you sure DBD::mysql was build against a recent version of MySQL 8.0?
However I would strongly recommend to use TLS instead of the RSA keypair.

[vicigel]

I'm not sure why they mysql_get_server_pubkey thing isn't working for you. Are you sure DBD::mysql was build against a recent version of MySQL 8.0?
However I would strongly recommend to use TLS instead of the RSA keypair.

Yes, I compile source code, following instructions on official website. almost these steps

yum -y install perl-Test-Deep perl-Time-HiRes perl-DBD-MySQL
export PATH=/usr/local/mysql80/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/mysql80/lib:$LD_LIBRARY_PATH
rpm -e perl-DBD-MySQL
tar xvf DBD-mysql-4.050.tar.gz
cd DBD-mysql-4.050
perl Makefile.pl
make
make test
make install

MySQL version is 8.0.17

ldd /usr/local/lib64/perl5/auto/DBD/mysql/mysql.so
linux-vdso.so.1 => (0x00007fff0d74a000)
libmysqlclient.so.21 => /usr/local/mysql80/lib/libmysqlclient.so.21 (0x00007f45d74a6000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f45d727c000)
libm.so.6 => /lib64/libm.so.6 (0x00007f45d6ff8000)
librt.so.1 => /lib64/librt.so.1 (0x00007f45d6df0000)
libssl.so.1.0.0 => /usr/local/mysql80/lib/libssl.so.1.0.0 (0x00007f45d6b7f000)
libcrypto.so.1.0.0 => /usr/local/mysql80/lib/libcrypto.so.1.0.0 (0x00007f45d6744000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f45d6540000)
libc.so.6 => /lib64/libc.so.6 (0x00007f45d61ab000)
libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f45d5ea5000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f45d5c8f000)
/lib64/ld-linux-x86-64.so.2 (0x00000039b2c00000)

[vladsf]

The same here - mysql_get_server_pubkey=1 does not help, but mysql_ssl=1 (added to DSN line) does and connection establishes.

DBI::VERSION=1.627
DBD::mysql::VERSION4.050
mysql_serverversion=80018

[dveeden]

The mysql_get_server_pubkey=1 depends on which client library is compiled against and which client library is installed. It should work fine with MySQL 8.0 client libraries. It may work with new versions of MySQL 5.7.

However using SSL/TLS is more reliable as this only requires a client libarary and DBD::mysql that are SSL enabled, which should be true for more than 99.999% of the installations.
Using SSL/TLS is also more secure and imho easier to manage.

[vladsf]

$ ldd perl5/lib/perl5/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so
	linux-vdso.so.1 =>  (0x00007ffed4b91000)
	libmysqlclient.so.21 => /usr/lib64/mysql/libmysqlclient.so.21 (0x00007f3a798e7000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f3a796cb000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f3a793c9000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f3a791c1000)
	libssl.so.10 => /lib64/libssl.so.10 (0x00007f3a78f4f000)
	libcrypto.so.10 => /lib64/libcrypto.so.10 (0x00007f3a78aec000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f3a788e8000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f3a7851a000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f3a78213000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f3a77ffd000)
	/lib64/ld-linux-x86-64.so.2 (0x00007f3a7a3df000)
	libgssapi_krb5.so.2 => /lib64/libgssapi_krb5.so.2 (0x00007f3a77db0000)
	libkrb5.so.3 => /lib64/libkrb5.so.3 (0x00007f3a77ac7000)
	libcom_err.so.2 => /lib64/libcom_err.so.2 (0x00007f3a778c3000)
	libk5crypto.so.3 => /lib64/libk5crypto.so.3 (0x00007f3a77690000)
	libz.so.1 => /lib64/libz.so.1 (0x00007f3a7747a000)
	libkrb5support.so.0 => /lib64/libkrb5support.so.0 (0x00007f3a7726a000)
	libkeyutils.so.1 => /lib64/libkeyutils.so.1 (0x00007f3a77066000)
	libresolv.so.2 => /lib64/libresolv.so.2 (0x00007f3a76e4d000)
	libselinux.so.1 => /lib64/libselinux.so.1 (0x00007f3a76c26000)
	libpcre.so.1 => /lib64/libpcre.so.1 (0x00007f3a769c4000)
$ rpm -qf /usr/lib64/mysql/libmysqlclient.so.21
mysql-community-libs-8.0.18-1.el7.x86_64

[dveeden]

Could you check if 58e017b fixes this for you? If not please create a separate issue.

[vladsf]

Thank you, mysql_get_server_pubkey=1 does work now using this patch.
Sorry for delay.