Mysql

mysql php ssl連接證書驗證失敗

  • March 14, 2018

我已按照以下步驟在伺服器 A上添加 mysql ssl 支持:

# Generate a CA key and certificate with SHA1 digest
openssl genrsa 2048 > ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem

# Create server key and certficate with SHA1 digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -sha1 -req -in server-req.pem -days 730  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
openssl rsa -in server-key.pem -out server-key.pem

# Create client key and certificate with SHA digest, sign it and convert
# the RSA key from PKCS #8 (OpenSSL 1.0 and newer) to the old PKCS #1 format
openssl req -sha1 -newkey rsa:2048 -days 730 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -sha1 -req -in client-req.pem -days 730 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
openssl rsa -in client-key.pem -out client-key.pem

伺服器A nano /etc/mysql/my.cnf

bind-address            = 0.0.0.0

[client]
ssl-ca=/etc/mysql/ca-cert.pem

[mysqld]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

並測試 SSL 支持

mysql -u root -p
mysql> show variables like "%ssl%";
This is resoult from my ubuntu 14.04 test machine

mysql> show variables like "%ssl%";
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_crl       |                            |
| ssl_crlpath   |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+
9 rows in set (0.00 sec)

伺服器B nano /etc/mysql/my.cnf

[client]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

伺服器 B: 我正在測試的 php 腳本:

$con=mysqli_init();
if (!$con){
   die("mysqli_init failed");
}

mysqli_ssl_set($con, "/var/www/certs/client-key.pem","/var/www/certs/client-cert.pem","/var/www/certs/ca-cert.pem",NULL, "AES256-SHA");

if (!mysqli_real_connect(
   $con,"127.0.0.1","user", 'password',"db", 3306)){
   die("Connect Error: " . mysqli_connect_error());
}
mysqli_close($con);

我得到的錯誤:

PHP Warning:  mysqli_real_connect(): SSL operation failed with code 1. OpenSSL Error messages:
error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed in ...

我在伺服器 B 上執行 php 腳本,而 mysql db 在伺服器 A 上。

當我嘗試通過控制台從伺服器 B 連接到 A 時,它會連接。程式碼:

mysql -h SERVER_A_IP -P 3306 -uuser -ppassword 

這是因為預設情況下,OpenSSL 會嘗試驗證您的證書的證書鏈。您的證書是自簽名的,也就是說,沒有其他實體對其進行驗證,這就是 OpenSSL 中證書驗證失敗的原因。

為了禁用證書驗證,您需要在呼叫中添加MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT標誌。mysqli_real_connect()

您的測試有效的原因mysql是您在該測試中沒有使用 SSL 進行連接。您需要單獨啟用 SSL 以mysql進行測試。

引用自:https://serverfault.com/questions/790453