Connect to Database via SSL

[jbrinson]

Is it possible to connect to a remote db using ssl?

My current situation:
Server1 hosts OJS instances (serves out the http requests)
Server2 hosts the database (mysql service)

How do I connect from Server1 to Server2 via SSL?

[asmecher]

Hi @jbrinson,

See this post about the ADODB library and SSL connections. OJS uses ADODB’s Connect/PConnect functions, so you may need to adapt this to use a DSN instead.

Regards,
Alec Smecher
Public Knowledge Project Team

[jbrinson]

So I am taking that as a no (without changing code).

Is this a feature that is being planned for the future? I hope I am not the only one with this use case.

[asmecher]

Hi @jbrinson,

I don’t recall hearing a need for this before, so while I think DSN support is a good idea, it’s a low priority, I’m afraid.

Regards,
Alec Smecher
Public Knowledge Project Team

[ronaldo]

It’s an old question and I don’t know much about OJS working process structure so, it may exist a better solution…

The 3.3.0. 8 version of OJS seems to use Lavarel and Doctare models for database connection and the SSL could be configured by the options parameters in the AddConnection() function.

STEP 1 - At the root folder of OJS, create a new php file with the array of SSL options, for instance, “mysql_ssl.php”. A content example could be like that:

<?php

return array(
   PDO::MYSQL_ATTR_SSL_CA  => '../rootCA.pem',
   PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false
);

?>

STEP 2 - Edit the existent “config.inc.php” file and add to the [database] section two more configurations options. The first new line option indicates if ssl mode is either “On” or “Off”. The second one indicates the file which contains the ssl configuration (“mysql_ssl.php” in our example).

The content added into config.inc.php should be something like:

ssl_mode = On
ssl_options_file = "mysql_ssl.php"

STEP 3 - Edit the file “PKPApplication.inc.php” which should be found at “your_ojs_path/lib/pkp/classes/core” and apply these two adjustments:

3.1 - In PKPApplication.inc.php search for “initializeDatabaseConnection()” function and after the line “$capsule = new Capsule;”, add the following:

$capsule = new Capsule;

// Adjustment to get Database SSL mode options
$opt = [];
if (Config::getVar('database', 'ssl_mode')) {
   $opt = include(Config::getVar('database', 'ssl_options_file'));
}

If the “ssl_mode” in “config.inc.php” is “On”, so the “mysql_ssl.php” content will be associated with the “$opt”.

3.2 - Search for the “$capsule->addConnection” method (it should be the next line after the added lines in step 3.1) and insert the SSL options after the “collation” array item by typing ‘options’ => $opt The result should look like as:

 $capsule->addConnection([
              'driver'    => $driver,
                .
                .
                .   
              'collation' => Config::getVar('database', 'collation', 'utf8_general_ci'),
              'options'   => $opt
           ]);

I am not 100% sure but if you want to process the instalation using ssl mode the STEP 3 (3.1 and 3.2) will be also necessary for the file: “your_ojs_path/lib/pkp/classes/ install/PKPInstall.inc.php

[odkr]

This is just another +1 for TLS support for database connections. I am frankly surprised that this is an issue; not having TLS supports feels pre-2000s.

[asmecher]

Hi @odkr,

OJS is known to perform best when the DBMS and web server are on the same machine, which is the way most users have deployed it. For that reason, encryption between the web server and DBMS hasn’t been a high priority. A modification to add TLS support should be fairly simple and a pull request would be welcome, but be warned that you’ll see performance issues as your content and readership grows if you host the database on a different machine until we have time to optimize that.

Regards,
Alec Smecher
Public Knowledge Project Team

[odkr]

Thanks for the swift reply and sorry that it took me a while. It’s good to know that you’d accept a PR! I’ll add it to my to-do list; I cannot tell when I’ll get around to it.

I’m aware that running the DB on another system incurs a performance penalty; we can handle that. That’s an aside but: wouldn’t any system that couldn’t handle that sort of overhead need to be placed behind a reverse proxy, or at the very least, mod_cache, at any rate?

And, just to describe our use case: Using a single DBMS (to the extent possible) makes it easier for us to integrate our systems.

[asmecher]

@odkr

wouldn’t any system that couldn’t handle that sort of overhead need to be placed behind a reverse proxy, or at the very least, mod_cache, at any rate?

Sorry, I’m not sure I follow – do you mean a scenario where a DBMS was being run externally to the web server, and where performance wasn’t acceptable?

Regards,
Alec Smecher
Public Knowledge Project Team