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: test@db.test.com
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?
UPDATE #1
Supposedly Bug #77595 has been fixed in php7.2.3 so I fired up a new VM with ubuntu18.04 which has php7.2.3 as the default package, still no luck. The only error string that seemed useful from var_dump was
"PDO::__construct(): SSL operation failed with code 1. OpenSSL Error
messages: error:0906D06C:PEM routines:PEM_read_bio:no start line"
mysql login from command line still works with the same certificates it is just php doesn't like them. I scp'd them from the mysql server so the chance of formatting error is unlikely.
Best Answer
I don't have a clear answer for this problem. My assumption is that something is going on with yaSSL. I ended up using Percona instead which implements openSSL.