SSH tunnelling from php to multiple nodes

Question

I have a central system(web application) which is supposed to connect to a set of nodes one by one (clients), to get a record set from database exposed through mysql views.

I am in a search of best possible implementation for this scenario. Currently I have this flow in my mind.

1. First Approach

Pseudo code

 $dataFetched=array();
  $clients= getListOfClients();
  //
  foreach($clients as $client){
   // client={details:{id,name,etc....},
   //         ssh:{pem:'client.pem',localMysqlPort:3307 ,remoteMyqlPort:3306,
   //              sshUser:'demo@clientHost.com'},
   //         mysql:{user:'tunnelUser',password:'password'}
   //        }
   //connect ssh connection
   $ssh_connection=new Ssh_connect($client->ssh) 
   if($ssh_connection->status){    
      $dataFetched[$client->id]=array("status"=>'OK','data'=>fetchMysqlData($client->mysql))
   }else{
      $dataFetched[$client->id]=array("status"=>'NO_OK','data'=>array())
   }

   $ssh_connection->destroyTunnel();
   unset($ssh_connection);   
  }

Some Concerns/questions with this approach

1. Is there a ssh lib/extension to provide me this kind of functionality ?

2. What is best secure way to store "pem" files for each client ?

3. Another approach ?

2. Second Approach

1. Write bash script to implement above pseudo-code

2. setup cron job to update database

3. Web application does not know about external clients. It will use table updated by cron-job to render data.

Some point:

1. Data size is very light

2. Both consumer and provider are web-applications

3. Applications does not know each other except SSH

4. It should be flexible to add new client node ( I don't known how I will do this in second approach)

5. Which one will be better from security wise Reverse Tunnel or Forward Tunnel ? As I know in case of reverse tunnel, echo client knows about server which desires to connect with it. So we need to setup a less privileged user to on each client node to give access to server, which seems more secure as I don't have to keep all pem files in one place at Central server. Correct me if I am getting it wrong.

Your suggestions are much appreciated

Answer 1

There is an SSH library for PHP. It's called ssh2 and it is available on PECL.

It has a dependancy on libssh2, but it should not be too hard to get up and running on a Linux-based machine.

The best way to store the pem files is outside your web directory, with limited permissions - just enough for your web application to be able to read the files. Alternatively, you could set up the ssh-agent daemon to run as your web application.

(I am hoping here that your PHP apps don't all run under the same user. For Apache, there is the little known mod_ruid2 module that allows you to change the user and chroot a PHP web app, without resorting to setting up fastcgi or suExec).

Sadly, ssh2_tunnel only creates a new PHP resource, it's not something that you can use to create a new mysql connection (well, at least not using mysqli_connect & friends).

What you could do is execute the SQL commands on the local server, storing the database password inside ~/.my.cnf

~/.my.cnf:

[client]
password = lolcats1234

And then with PHP & ssh2 you could run the following code once connected:

stream_set_blocking($stream, true); 
$stream = ssh2_exec($connection, 'mysql -u db -p db -e "select * from table"');

$first = true;
while($line = fgets($stream)) {
     if ($first) {
          $first = false;
          continue;
     }

     // process one line of results...
     echo $line."<br />"; 
} 

Alternatively, if you just use a Reverse Tunnel, you can just mysql_connect using the local script, without needing to worry about doing any SSH work at all.