Can't connect to remote mysql database using SSL and SHA256 password authentication

[Kevin Wong]

I can successfully connect over a secure connection from the web server's cli but get an error message when testing connection through php script.

### mysql status from the web server cli ###

mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper

Connection id: 9
Current database:
Current user: [e-mail]
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: 0.0.0.0 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Uptime: 3 days 4 hours 53 min 35 sec

Threads: 1 Questions: 17 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.000

### SSL variables from web server cli ###

+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+

### Grants from web server cli ###

+-------------------------------------------------------------+
| Grants for test@0.0.0.0 |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'0.0.0.0' |
| GRANT SELECT ON `testDB`.* TO 'test'@'0.0.0.0' |
+-------------------------------------------------------------+

### Character set using utf8mb4 from web server cli ###

+--------------------------+------------------------+
| Variable_name | Value |
+--------------------------+------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_520_ci |
| collation_database | utf8mb4_unicode_520_ci |
| collation_server | utf8mb4_unicode_520_ci |
+--------------------------+------------------------+

### User permissions on remote database ###

+------------------+-------------+-----------------------+----------+
| user | host | plugin | ssl_type |
+------------------+-------------+-----------------------+----------+
| test | 0.0.0.0 | sha256_password | X509 |
+------------------+-------------+-----------------------+----------+

### Certificate permissions on web server ###

apache@web:~$ ls -al /etc/mysql-client
total 20
drwxr-xr-x 2 root root 4096 Mar 24 02:30 .
drwxr-xr-x 99 root root 4096 Mar 30 20:04 ..
-r-xr-xr-x 1 root root 1107 Mar 24 02:30 ca.pem
-r-xr-xr-x 1 root root 1107 Mar 24 02:30 client-cert.pem
-r-xr-xr-x 1 root root 1679 Mar 24 02:30 client-key.pem

### PHP Test Script on web server ###

$dsn = 'mysql:host=db.test.com;port=3306;dbname=testDB;charset=utf8mb4';
$dbuser = 'test';
$dbpass = 'sha256-hash';

$dboptions = array (
PDO::MYSQL_ATTR_SSL_KEY =>'/etc/mysql-client/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT =>'/etc/mysql-client/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/etc/mysql-client/ca.pem',
PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
);

try {
$conn = new PDO($dsn,$dbuser,$dbpass,$dboptions);
if($conn){
echo "Connection established";
}
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}

### Successful network connection from web server to database server ###

Connection to 0.0.0.0 3306 port [tcp/mysql] succeeded!

### Error Message from web server ###

apache@web:~$ php -f test.php
Connection failed: SQLSTATE[HY000] [2002]

### The few questions I have are ###

Should the database password used in the php script be set to the plain-text password string or the sha256 hash? I have tried both and neither work.

Since the certificates are in /etc with global permissions and the php script has explicit file paths, is creating a database user account on the web server necessary? For example, my "test" user on the web server is only really used to test the mysql connection from command line. I don't see how apache is dependent on the test user.

Any php.ini settings I should take a look at? The only one I have tested was changing "open_basedir" to include the /etc/mysql-client directory but that didn't do anything either.

Does anybody know if Bug #77595 has been fixed?

Has anyone actually gotten sha256_password authentication over SSL using PHP to actually work?