SSL/TLS Database MySQL MariaDB Connection
I wish there were an option for installation or through the config file to allow secure database connections. As I was searching around, I was able to find a solution.
This edit will alter some core files, so it is NOT upgrade safe.
I will explain how the secure database connection is done and then how it is reflected through a connection with SuiteCRM.
THE DB KEYCHAIN
I found this great tutorial on how to allow secure DB connections and create the Keychain for TLS.
https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/
The main difference we are taking is in STEP 3 where it notes:
Note: Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. To avoid any issues I am setting them as follows:
CA common Name : MariaDB admin
Server common Name: MariaDB server
Client common Name: MariaDB client
Simply use a “-” instead of a space. The reason is that the “MariaDB-server” must be a valid hostname of the DB. You may also substitute the Hostname with a valid TLD. I used “MariaDB-server” as the CN and altered the /etc/hosts file on the web server to point to the IP of the DB when attempting to connect “MariaDB-server”.
I have also uploaded a text file of the tutorial in case the page changes or is taken down for whatever reason.
So to reiterate, use the following names
Note: Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate. To avoid any issues I am setting them as follows:
CA common Name : MariaDB-admin
Server common Name: MariaDB-server
Client common Name: MariaDB-client
Once you have the main files:
ca-cert.pem
client-cert.pem
client-key.pem
Then you can migrate them over to your web server. Ensure your DB has the keychain properly installed as per the tutorial.
My files are organized as follows, your scenario might differ.
/var/www/vhosts/mycrmdomain.tld/httpdocs/
/var/www/vhosts/mycrmdomain.tld/suitecrm/
/var/www/vhosts/mycrmdomain.tld/suitecrmlog/
/var/www/vhosts/mycrmdomain.tld/ca-cert.pem
/var/www/vhosts/mycrmdomain.tld/client-cert.pem
/var/www/vhosts/mycrmdomain.tld/client-key.pem
So basically I place the files above the root web directory.
NEXT
THE CONFIG FILE
Next we simply alter the config_override file and add some parameters.
add the following to the end of the config_override.php file
/***SSL CONFIG - KEY FILE PATH***/
$sugar_config['dbconfigoption']['ssl'] = true;
$sugar_config['dbconfigoption']['ca_cert'] = '../ca-cert.pem';
$sugar_config['dbconfigoption']['client_cert'] = '../client-cert.pem';
$sugar_config['dbconfigoption']['client_key'] = '../client-key.pem';
/***SSL CONFIG - KEY FILE PATH***/
NEXT
THE MysqliManager.php file
Work your way to around line 262
Alter the connection section.
BEFORE
public function connect(array $configOptions = null, $dieOnError = false)
{
global $sugar_config;
if (is_null($configOptions))
$configOptions = $sugar_config['dbconfig'];
if(!isset($this->database)) {
//mysqli connector has a separate parameter for port.. We need to separate it out from the host name
$dbhost=$configOptions['db_host_name'];
$dbport=isset($configOptions['db_port']) ? ($configOptions['db_port'] == '' ? null : $configOptions['db_port']) : null;
$pos=strpos($configOptions['db_host_name'],':');
if ($pos !== false) {
$dbhost=substr($configOptions['db_host_name'],0,$pos);
$dbport=substr($configOptions['db_host_name'],$pos+1);
}
$this->database = @mysqli_connect($dbhost,$configOptions['db_user_name'],$configOptions['db_password'],isset($configOptions['db_name'])?$configOptions['db_name']:'',$dbport);
if(empty($this->database)) {
$GLOBALS['log']->fatal("Could not connect to DB server ".$dbhost." as ".$configOptions['db_user_name'].". port " .$dbport . ": " . mysqli_connect_error());
if($dieOnError) {
if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
} else {
sugar_die("Could not connect to the database. Please refer to suitecrm.log for details.");
}
} else {
return false;
}
}
}
etc...
AFTER
public function connect(array $configOptions = null, $dieOnError = false)
{
global $sugar_config;
if (is_null($configOptions))
$configOptions = $sugar_config['dbconfig'];
if(!isset($this->database)) {
$configDbOptions = $this->getOptions();
if ($configDbOptions['ssl'] === TRUE) {
//mysqli connector has a separate parameter for port.. We need to separate it out from the host name
$dbhost=$configOptions['db_host_name'];
$dbport=isset($configOptions['db_port']) ? ($configOptions['db_port'] == '' ? null : $configOptions['db_port']) : null;
$pos=strpos($configOptions['db_host_name'],':');
if ($pos !== false) {
$dbhost=substr($configOptions['db_host_name'],0,$pos);
$dbport=substr($configOptions['db_host_name'],$pos+1);
}
$this->database = mysqli_init();
mysqli_options($this->database, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$this->database->ssl_set(NULL,NULL,$configDbOptions['ca_cert'],NULL,NULL);
$this->database->real_connect($dbhost,$configOptions['db_user_name'],$configOptions['db_password'],isset($configOptions['db_name'])?$configOptions['db_name']:'',$dbport, NULL, MYSQLI_CLIENT_SSL);
}
else{
//mysqli connector has a separate parameter for port.. We need to separate it out from the host name
$dbhost=$configOptions['db_host_name'];
$dbport=isset($configOptions['db_port']) ? ($configOptions['db_port'] == '' ? null : $configOptions['db_port']) : null;
$pos=strpos($configOptions['db_host_name'],':');
if ($pos !== false) {
$dbhost=substr($configOptions['db_host_name'],0,$pos);
$dbport=substr($configOptions['db_host_name'],$pos+1);
}
$this->database = @mysqli_connect($dbhost,$configOptions['db_user_name'],$configOptions['db_password'],isset($configOptions['db_name'])?$configOptions['db_name']:'',$dbport);
}
if(empty($this->database)) {
$GLOBALS['log']->fatal("Could not connect to DB server ".$dbhost." as ".$configOptions['db_user_name'].". port " .$dbport . ": " . mysqli_connect_error());
if($dieOnError) {
if(isset($GLOBALS['app_strings']['ERR_NO_DB'])) {
sugar_die($GLOBALS['app_strings']['ERR_NO_DB']);
} else {
sugar_die("Could not connect to the database. Please refer to suitecrm.log for details.");
}
} else {
return false;
}
}
}
etc...
END
That should be enough changes to make the Database SSL/TLS connection work through PHP.
You can check your keychain beforehand to ensure they are working using MySQL CLI connection.
I hope this helps anyone looking to secure the DB connection.
Thanks.