[SOLVED] CodeIgniter – Google Cloud MySQL connect with SSL

For one of my projects, the database is hosted in Google Cloud SQL. The connection to this database is available only via SSL. This is the first time I am switching from unsecure connection to secure connection in MySQL.

Below code was written in test.php to make sure that I can connect to the database server via SSL

<?php

$mysqli = mysqli_init();

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


$mysqli -> ssl_set("<<home_dir_user>>/.ssh/client-key.pem", "<<home_dir_user>>/.ssh/client-cert.pem", "<<home_dir_user>>/.ssh/server-ca.pem", NULL, NULL); 

if (!$mysqli -> real_connect("<<google_cloud_public_ip>>","<<dbuser>>","<<dbpassword>>","<<dbname>>")) {
  die("Connect Error: " . mysqli_connect_error());
}

echo "Connected";

?>

The code looks perfectly OK!

But unfortunately, this did not work as expected.

The first error I received was “<<dbuser>>@localip Access denied”. I was 200% sure that the credentials were correct. I had no idea what to do!

Googling did not give any useful result! [That’s the reason for this post too :)]. There were several thoughts made and tried. But the one that worked was to copy the certificate files to a folder within the project folder. For testing purposes, it was kept in the same folder where the connection was established. So, the code was changed to below

<?php

$mysqli = mysqli_init();

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


$mysqli -> ssl_set("client-key.pem", "client-cert.pem", "server-ca.pem", NULL, NULL); 

if (!$mysqli -> real_connect("<<google_cloud_public_ip>>","<<dbuser>>","<<dbpassword>>","<<dbname>>")) {
  die("Connect Error: " . mysqli_connect_error());
}

echo "Connected";

?>

Now, Access denied problem disappeared & I came across another issue.. “Peer certificate CN=[google-project:instance_id] did not match expected CN=[google-cloud-sql-public-ip]

Started googling again.. Client certificates generated in Google Cloud will have instance name. However, we cannot use the instance name to connect to MySQL. Therefore, these certificates become useless. We cannot generate certificates with IP address in Google Cloud. I felt like standing at a dead end!!

The only option that many had implemented is Google Cloud Proxy SQL. I started reading Google help to implement the same. Then came another thought and wanted to check it out before I start implementing proxy. The idea was to add google-cloud-instance-name in /etc/hosts file. The entry was like this

xx.xx.xx.xx google-cloud-project:instance-name #Added for Google Cloud SQL

Now.. tried to connect again.. and Tada.. It worked!

I would like to also update about other options errors that I faced, and how I fixed them…

MYSQLI_OPT_SSL_VERIFY_SERVER_CERT

$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true); 

At some point, I received the error SSL certificate cannot be verified. Therefore, added this option before ssl_set call. Later I removed this & it made no difference

client_flags in mysqli_real_connect – MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT

$mysqli -> real_connect("<<google_cloud_public_ip>>","<<dbuser>>","<<dbpassword>>","<<dbname>>", null, null, MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT); 

Remember, you can use MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT option only when it exists in your PHP configuration. I later, removed this code and it still worked.

Good.. Now it works. But the ultimate goal was to setup SSL connection in CodeIgniter project. The config in database.php was written like below

$db['default'] = array(
	'dsn'	=> '',
        'hostname' => '<<google_cloud_ip>>',
	'username' => '<<dbuser>>',
	'password' => '<<dbpassword>>',
	'database' => '<<dbname>>',
        'dbdriver' => 'mysqli',
	'dbprefix' => '',
	'pconnect' => FALSE,
	'db_debug' => (ENVIRONMENT !== 'production'),
	'cache_on' => FALSE,
	'cachedir' => '',
	'char_set' => 'utf8',
	'dbcollat' => 'utf8_general_ci',
	'swap_pre' => '',
	'encrypt' => array(
		'ssl_key' => '<<folder_within_project_folder>>/client-key.pem',
		'ssl_cert' => '<<folder_within_project_folder>>/client-cert.pem',
		'ssl_ca' => '<<folder_within_project_folder>>/server-ca.pem',
		'ssl_verify' => FALSE,
	),
	'compress' => FALSE,
	'stricton' => FALSE,
	'failover' => array(),
	'save_queries' => TRUE
);

Note, although you put it in “encrypt” array, nothing is encrypted in codeigniter. The option MYSQLI_CLIENT_SSL is being set, which takes care of client encryption.

Share This

Leave a Reply

Your email address will not be published. Required fields are marked *


4 − = three