phpMyAdmin usage over an SSH Tunnel in Drupal

David G - DrupalAt my work I was created a new Staging or Demonstration server. This is a server that I can use to show preview of web applications to clientele. As I develop on a laptop my own system is not always available so a dedicated system for Staging use is beneficial to my clients. My sysadmin provided a new system running Ubuntu 14.04 LTS. And he stated: I have also given you sudo access to update, install and uninstall as well as write to the apache2 directory and php5 directory.  Mysql 5.5 is on there and you have an account accessible from localhost with the username dgurba. I don’t like doing work over the command line for database development, I prefer phpMyAdmin which I’ve used for many years. So I wanted to make use of phpMyAdmin locally installed on my laptop, to this remote server over an SSH connection.

Creating an SSH Tunnel and Configuring phpMyAdmin

SSH tunneling is a means to talk to a remote MySQL instance as if it resides on the local computer. A MySQL instance typically listens on port 3306 for payloads (query, authentication, etc). Since I run MySQL locally this is the ssh command I use to setup a tunnel to my remote server:

/usr/bin/ssh -fNL 3307:localhost:3306 dgurba@remote.server.example.com

As you can see this command breaks down as follows:

  • using the -f option requests ssh to go to background just before command execution. This is useful for having the SSH tunnel run in the background. If you are using a password to connect to the remote server, you’ll want to remove this argument.
  • using the -N option tells ssh to not execute a remote command. This is useful for just forwarding ports, which is what we want to accomplish.
  • the -L option specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side.
  • a useful way to get further information on what SSH is doing if you encounter problems is to pass the -v option for verbose output.

So the generic form of this command is:
ssh -fNL LOCAL_PORT:localhost:REMOTE_PORT user@REMOTE_IP

After issueing the above SSH command you can see if the tunnel is running using the ps command and looking for ssh applications:

 > ps -aux | grep ssh
dgurba    2806  0.0  0.0  10616   316 ?  Ss   11:24   0:00 ssh-agent -s
dgurba    3139  0.0  0.1 418896 21820 ?  Sl   11:24   0:00 /usr/bin/python /home/dgurba/apps/sshplus/sshplus.py
dgurba    7122  0.0  0.0  48276   912 ?  Ss   12:44   0:00 /usr/bin/ssh -fNL 3307:localhost:3306 dgurba@example.com

This verifies our tunnel is up and running (if it’s not, you may want to check your local firewall configuration).

In order for phpMyAdmin to make use of this we must add a server entry to our local systems config.inc.php file. As of this writing the current stable version of phpMyAdmin on Ubuntu 14.04 is: 4.2.9.1deb0.1

$i++;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
// phpMyAdmin should communicate over the wired-up
// SSH port which should be already connected previously.
$cfg['Servers'][$i]['port'] = 3307;
$cfg['Servers'][$i]['verbose'] = 'ID Web-Stages [via SSH tunnel]';
// The host is "localhost", and using the above port.
// The tunnel does the rest of the work making phpMyAdmin
// think that it's a local resource.
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = FALSE;
$cfg['Servers'][$i]['extension'] = 'mysqli';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

The most important thing to realize about this $cfg instance (which tripped me up too) is that the host value is 127.0.0.1 or localhost. I had put in the remote IP address here — But we’re using a tunnel!

We can then go to http://localhost/phpmyadmin and select our remote host MySQL instance and attempt to login with our credentials. This all occurs over the SSH tunnel and we can gain access:

Tunneled access to a remote MySQL instance.

Tunneled access to a remote MySQL instance.

As you can see from this screenshot:

  • we’re on a localhost url (our development system).
  • we are logged in as dgurba@localhost (the remote system, thinks we’re local)
  • we are running stable versions of MySQL remotely; and of phpMyAdmin locally.

So it’s all working! GREAT! But if we reboot our development system we lose our tunnel :(, can we have our development system create the tunnel on boot or login? You betcha.

Activating the SSH Tunneling on (Re)boot on your Development System

Now that the whole setup works we want to be hassle free without having to remember if the tunnel exists before trying to use phpMyAdmin.

What we want to do is run our above SSH command to create the tunnel when the system starts. Under Linux you may be familiar with the .profile, .xinit or .bashrc files. Many of these load on user login or GUI subsystem init — but we simply want this tunnel to be made on startup. And for instance the .profile file is also process on FTP and other logins — we don’t want tunnels spawning weirdly all over the place! So, we will add our tunnel creation to the /etc/rc.local file. I tend to place custom commands in a seperate file and call the file from rc.local. In the event of error I can easily CAT the file and not rc.local which may be offlimits of some system configurations.

In $HOME/init_scripts/create_ssh_tunnels.sh I place the following commands:

#!/bin/sh

# David Gurba <my-email@email.com>
#
# This file should be called by your system's rc.local file to be run at
# startup. For Ubuntu 14.04 that file is at /etc/rc.local, depending on your
# OS distribution it's path may be different.
#
# These tunnels are created via rc.local as opposed to .profile, or .xinit as
# other init files such as .profile are run on every login session. You do not
# want to be creating tunnels all over the place.

/usr/bin/ssh -fNL 3307:localhost:3306 dgurba@remote.example

I use full system paths in my script(s) because typically scripts when run by the system may be in a different environment than what you use when you login, if you don’t know what $PATH values are set for shortcut commands (like just call ssh arg1 arg2) may fail to be found — so full paths is just a debug/safety precaution that assures the script behaves as expected later (or is being used as strictly as possible).

In /etc/rc.local I then added the following commands:

#!/bin/sh -e
#
# rc.local
#
# This script is executed at the end of each multiuser runlevel.
# Make sure that the script will "exit 0" on success or any other
# value on error.
#
# In order to enable or disable this script just change the execution
# bits.
#
# By default this script does nothing.


exec 2> /tmp/rc.local.log   # send stderr from rc.local to a log file
exec 1>&2                   # send stdout to the same log file
set -x                      # tell sh to display commands before execution

/home/dgurba/init_scripts/create_ssh_tunnels.sh

exit 0

We can then restart the system and see if our tunnel is made. Unfortunately for me it was not. Does our debug log have any useful information:

+ /home/dgurba/init_scripts/create_ssh_tunnels.sh
OpenSSH_6.6.1, OpenSSL 1.0.1f 6 Jan 2014
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: Applying options for *
debug1: Connecting to web-stage01.id.ucsb.edu [128.111.138.42] port 22.
debug1: Connection established.
debug1: permanently_set_uid: 0/0
debug1: identity file /root/.ssh/id_rsa type -1
debug1: identity file /root/.ssh/id_rsa-cert type -1
debug1: identity file /root/.ssh/id_dsa type -1
debug1: identity file /root/.ssh/id_dsa-cert type -1
debug1: identity file /root/.ssh/id_ecdsa type -1
debug1: identity file /root/.ssh/id_ecdsa-cert type -1
debug1: identity file /root/.ssh/id_ed25519 type -1
debug1: identity file /root/.ssh/id_ed25519-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_6.6.1p1 Ubuntu-2ubuntu2
debug1: Remote protocol version 2.0, remote software version OpenSSH_6.6.1p1 Ubuntu-2ubuntu2
debug1: match: OpenSSH_6.6.1p1 Ubuntu-2ubuntu2 pat OpenSSH_6.6.1* compat 0x04000000
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: server->client aes128-ctr hmac-md5-etm@openssh.com none
debug1: kex: client->server aes128-ctr hmac-md5-etm@openssh.com none
debug1: sending SSH2_MSG_KEX_ECDH_INIT
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: Server host key: ECDSA eb:3b:da:de:0f:8d:5e:67:d6:1f:90:32:7f:d0:ff:f1
debug1: read_passphrase: can't open /dev/tty: No such device or address
Host key verification failed.

I see 2 items of note here:

  1. The shell script we wrote to spawn the SSH tunnel is running as the root user, not as dgurba. The remote account expects my dgurba id_rsa credentials — not those of root.
  2. It complained something about a TTY that did not exist.

These problems exist because we’re not running as the correct user. We could copy the id_rsa file from dgurba to root, but ewww. We could also make the create_ssh_tunnels.sh script owned by root and chmod 700 — but again eww.

I ended up playing with setuid bit on the script to try and make it always run as the dgurba user, but it turns out setuid on shebanged scripts are usually ignored (see http://unix.stackexchange.com/a/2910/86052).

I ended up changing my rc.local file to run the following command:

// We use the "su" command to run the given command as the
// desired user.
su -lc "/home/dgurba/init_scripts/create_ssh_tunnels.sh" dgurba

Now you can securely connect to and manage your MySQL instance! 🙂 wooo!

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , | Posted under Drupal, phpMyAdmin | RSS 2.0

Author Spotlight

David Gurba

I am a web programmer currently employed at UCSB. I have been developing web applications professionally for 8+ years now. For the last 5 years I’ve been actively developing websites primarily in PHP using Drupal. I have experience using LAMP and developing data driven websites for clients in aviation, higher education and e-commerce. If you’d like to contact me I can be reached at david.gurba@arvixe.com

Leave a Reply

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