Cannot connect to Cloud SQL via SSL from PHP

[小川純平]

Hi, I have the same problem as following question. Does anyone have idea to solve this problem?

http://stackoverflow.com/questions/34478265/ssl-self-signed-certifications-to-connect-with-mysql-with-php/34478690#34478690

I'm using CakePHP and connecting from CentOS 6 (applied all updates via yum) on GCE.

According to the error message, Common Name of Cloud SQL certificate seems invalid.
Any solutions to fix Cloud SQL's certificate, or any workaround?

BTW I can connect using mysql client (5.1.x) from the same GCE instance
$ mysql -u dbname -p -h (Cloud SQL IPv4 address) notel --ssl-ca=/path/to/ca.pem --ssl-cert=/path/to/cert.pem --ssl-key=/path/to/key.pem


Following is full logs:

2016-02-18 19:41:44 Warning: Warning (2): PDO::__construct() [<a href='http://php.net/pdo.construct'>pdo.construct</a>]: Peer certificate CN=`(CGP project name):(Cloud SQL instance name)' did not match expected CN=`(Cloud SQL instance IP)' in [/var/www/notel/data/lib/Cake/Mod
el/Datasource/Database/Mysql.php, line 175]
Trace:
PDO::__construct() - [internal], line ??
Mysql::connect() - CORE/Cake/Model/Datasource/Database/Mysql.php, line 175
DboSource::__construct() - CORE/Cake/Model/Datasource/DboSource.php, line 260
ConnectionManager::getDataSource() - CORE/Cake/Model/ConnectionManager.php, line 105
Model::getDataSource() - CORE/Cake/Model/Model.php, line 3502
Model::schema() - CORE/Cake/Model/Model.php, line 1355
Model::_setAliasData() - CORE/Cake/Model/Model.php, line 1220
Model::set() - CORE/Cake/Model/Model.php, line 1183
Model::save() - CORE/Cake/Model/Model.php, line 1713
DatabaseSession::write() - CORE/Cake/Model/Datasource/Session/DatabaseSession.php, line 117
MySession::write() - CORE/Cake/Model/Datasource/Session/MySession.php, line 31
session_write_close - [internal], line ??
[main] - [internal], line ??

2016-02-18 19:41:44 Warning: Warning (2): PDO::__construct() [<a href='http://php.net/pdo.construct'>pdo.construct</a>]: Cannot connect to MySQL by using SSL in [/var/www/notel/data/lib/Cake/Model/Datasource/Database/Mysql.php, line 175]
Trace:
PDO::__construct() - [internal], line ??
Mysql::connect() - CORE/Cake/Model/Datasource/Database/Mysql.php, line 175
DboSource::__construct() - CORE/Cake/Model/Datasource/DboSource.php, line 260
ConnectionManager::getDataSource() - CORE/Cake/Model/ConnectionManager.php, line 105
Model::getDataSource() - CORE/Cake/Model/Model.php, line 3502
Model::schema() - CORE/Cake/Model/Model.php, line 1355
Model::_setAliasData() - CORE/Cake/Model/Model.php, line 1220
Model::set() - CORE/Cake/Model/Model.php, line 1183
Model::save() - CORE/Cake/Model/Model.php, line 1713
DatabaseSession::write() - CORE/Cake/Model/Datasource/Session/DatabaseSession.php, line 117
MySession::write() - CORE/Cake/Model/Datasource/Session/MySession.php, line 31
session_write_close - [internal], line ??
[main] - [internal], line ??

2016-02-18 19:41:44 Warning: Warning (2): PDO::__construct() [<a href='http://php.net/pdo.construct'>pdo.construct</a>]: [2002]  (trying to connect via tcp://(Cloud SQL instance IP):3306) in [/var/www/notel/data/lib/Cake/Model/Datasource/Database/M
ysql.php, line 175]
Trace:
PDO::__construct() - [internal], line ??
Mysql::connect() - CORE/Cake/Model/Datasource/Database/Mysql.php, line 175
DboSource::__construct() - CORE/Cake/Model/Datasource/DboSource.php, line 260
ConnectionManager::getDataSource() - CORE/Cake/Model/ConnectionManager.php, line 105
Model::getDataSource() - CORE/Cake/Model/Model.php, line 3502
Model::schema() - CORE/Cake/Model/Model.php, line 1355
Model::_setAliasData() - CORE/Cake/Model/Model.php, line 1220
Model::set() - CORE/Cake/Model/Model.php, line 1183
Model::save() - CORE/Cake/Model/Model.php, line 1713
DatabaseSession::write() - CORE/Cake/Model/Datasource/Session/DatabaseSession.php, line 117
MySession::write() - CORE/Cake/Model/Datasource/Session/MySession.php, line 31
session_write_close - [internal], line ??
[main] - [internal], line ??

[小川純平]

Sorry, I forgot to write some info.

1. I'm using Google Cloud SQL 1st generation
2. I'm using PHP 5.6 from Remi repo.

[David Newgas]

As you noticed Cloud SQL does not make the common name match the IP address of the instance. Instead of relying on public CAs and verifying certificate chain, with Cloud SQL a one-off ca certificate is distributed to the client. This is more secure as it avoids trusting the hundreds of trusted CAs.

To make this work requires the client to be configured to use the provided CA certificate and disable peer name verification. With PDO you can do the former with MYSQL_ATTR_SSL_CA. PDO doesn't offer an option to do the latter.

If you are able to switch to the mysqli interface then you can use the MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT flag to mysqli_real_connect to disable peer name verification.

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/0ae3d9f7-5c3a-4dde-800a-1f25ba9502b1%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[小川純平]

Thanks, David.

Hmm... so there is no way to use Google Cloud SQL with CakePHP, which doesn't support MySQLi?

[Takashi Matsuo]

Ogawa-san,

Unfortunately you're correct as long as you want to use the SSL connection and somewhat newer versions of PHP. Maybe you can vote the bug above so that PHP devs will look into it sooner?

On Thu, Feb 18, 2016 at 8:08 PM 小川純平 <og...@growaspeople.org> wrote:

Thanks, David.

Hmm... so there is no way to use Google Cloud SQL with CakePHP, which doesn't support MySQLi?

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/c86db0e9-1801-4355-beb9-5f76b2a2024b%40googlegroups.com.

[小川純平]

Hi Matsuo san

Unfortunately I decided to use Amazon RDS this time.

> Unfortunately you're correct as long as you want to use the SSL connection and somewhat newer versions of PHP

I found SSL verification is introduced on 5.6,
http://php.net/manual/en/migration56.openssl.php#migration56.openssl.peer-verification

I considered to use 5.5 or older, but 5.5 EOL is next July.

CentOS 6 provides PHP 5.3.3, but CakePHP requires 5.3.7+ for SSL connection to MySQL.

RedHat / CentOS 7 provides PHP 5.4 and it looks the best solution to use Google Cloud SQL, but because my task is heavily delaying, it is difficult to choose to rewrite my Ansible configs.

I wonder if it is possible to include Cloud SQL instance IP as CN in the certificate. It looks better rather than adding function to make it less secure to PDO.
Sorry if I misunderstand David's explanation.

[David Newgas]

We do not have a usable CN because, depending on user config, there might not be an IP address at all and the IP address can change. Additionally because we provide a CA that is distributed by the user and is the sole trusted CA of clients verifying CN isn't actually needed for security: a man in the middle attack is not possible because an attacker cannot generate a certificate that would be trusted.

We do see the fact that users such as yourself have difficulties as a problem and we have an internal issue open to address this. Unfortunately poor configurability of client libraries like PDO make this difficult.

David

--

You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-d...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/16b92ac7-450b-4083-ae5f-49bfe4fb8fe0%40googlegroups.com.

[小川純平]

Thanks David.

Then PDO should be changed rather than Cloud SQL.

# Sorry, this post was not sent by some reason.

[Carlos Mendieta Castro]

Today I tested this new constant on PDO configuration on PHP 7.0.18 and is working fine to me.