How to query the INSPIRE database at CERN from you local host

General description

There's extensive email on -dev on how to set this up conveniently.

The basic idea is to use local port forwarding capability of ssh via -L option (man ssh) .
What is needed:

1. a shell account on a worker node
2. the address of the mysql host and the port it is listening on (3306)
3. a mysql account (username and password) which is allowed to connect from the chosen worker node
4. a (external) host which will connect to mysql

Then use the worker node as a relay to forward local port <x> to remote port 3306 on the mysql host
ssh -L<x>:inspire01.cern.ch:3306 <cern-user>@inspire04.cern.ch
now connect to mysql via
mysql -u <mysql-username> -h 127.0.0.1 --port=<x> -p
mysql-username is: readonly port <x> can be choosen to be 3306 or anything else

a local invenio instance can be configured to talk to the remote mysql via the tunnel simply
by setting CFG_DATABASE_.. variables, i.e. these
invenio-local.conf-example:#CFG_DATABASE_HOST = localhost invenio-local.conf-example:#CFG_DATABASE_PORT = 3306 invenio-local.conf-example:#CFG_DATABASE_NAME = cdsinvenio invenio-local.conf-example:#CFG_DATABASE_USER = cdsinvenio invenio-local.conf-example:#CFG_DATABASE_PASS = ...............
in the site's invenio-local.conf file.
The above ssh line assumes a ~/.ssh/config convenience to directly ssh
to a worker node via lxplus/netcat (ProxyCommand directive for ssh)
I (TS) use
Host i4 inspire04 inspire04.cern.ch p05153026581150.cern.ch HostName 188.184.3.37
ProxyCommand ssh <cern-user>@lxplus.cern.ch
exec /usr/bin/nc %h %p Compression yes ControlMaster auto ControlPath ~/.ssh/%r@%h:%p # ControlPersist 120 GSSAPIAuthentication yes GSSAPIDelegateCredentials yes PubkeyAuthentication no ServerAliveInterval 120

Also note that the above works from any external host anywhere.
It doesn't have dependencies on invenio. It simply establishes a channel for connections to mysql db.
With a properly configured remote invenio/inspire installation commands like
$ dbexec -i or python scripts using run_sql() from invenio will run transparently,
and so will ipython sessions.

Worker nodes for which "readonly" mysql account is allowed to connect to mysql
inspire0(3|4|5|6).cern.ch
mysql> select Host,Db,User,Select_priv from db where User="readonly";

inspire06.cern.ch
inspire05.cern.ch	p05153026637155.cern.ch
inspire04.cern.ch	p05153026581150.cern.ch	188.184.3.37
inspire03.cern.ch	p05153026485494.cern.ch
	p05153026131444.cern.ch	188.184.3.55

master db server: inspire01.cern.ch
test db server: inspirevm06.cern.ch

See also:
https://app.asana.com/0/2691557432415/11021953850167

How to set it up

Steps for testing:

1. log into a worker node and test db connection
   inspire04.cern.ch> mysql -u readonly -h inspire01.cern.ch -p Enter password:
   should result in mysql prompt
   
2. ensure that the ProxyCommand is working.
   remotehost> ssh inspire04.cern.ch -l<cern-user>
   should give login session on inspire04.cern.ch
   
3. put things together
   remotehost> ssh -L 3306:inspire01.cern.ch:3306 <cern-user>@inspire04.cern.ch
   remotehost> mysql -u readonly -h 127.0.0.1 --port=3306 -p
   
4. check the tunnel via
   remotehost> lsof -i4:3306
   COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME ssh 17522 thorsten 11u IPv4 2454309 0t0 TCP localhost.localdomain:mysql (LISTEN)
   voila, ssh is listening on standard local mysql port
   
5. improve the ssh command to not lead to a login shell and some other refinements: add options
   -C2qfTnN
   e.g. remotehost> ssh -C2qfTnN -L3306:inspire01.cern.ch:3306 <cern-user>@inspire04.cern.ch
   this will establish the tunnel and also give you back the command prompt on remotehost.

Keep the tunnel up with autossh

Maintaining the tunnel via autossh from remotehost to <cern-user>@lxplus.cern.ch

generate public/private key pair: ssh-keygen -t rsa
e.g. with name id_dsa
Make sure the private key id_dsa is not in a public directory.

on lxplus:
Copy the public key to ~/.ssh/id_dsa.pub and make it public (if necessary make the directory public).
Append the public key to ~/.ssh/authorized_keys and make it public.
On lxplus if you need private info in .ssh create a private/.ssh, move the corresponding files there
and link to the private file from the public directory.

on remotehost:
Copy the private key to ~/.ssh/id_dsa and make it private (if necessary make the directory private).
If you need public info in .ssh do the same trick as above.
Alternatively use a local, private directory like /home/.../ssh

Create ~/.ssh/config (to be on the save side replace ~ by full path.)

 Host prod4
 HostName 188.184.3.55
 ServerAliveInterval 60
 ProxyCommand ssh <cern-user>@lxplus.cern.ch  -i ~/.ssh/id_dsa -F ~/.ssh/config -o ForwardX11=no exec /usr/bin/nc %h %p
 User <cern-user>
 # GSSAPIAuthentication yes
 # GSSAPIDelegateCredentials yes

Create /etc/init.d/autossh:

 #!/bin/bash
 #
 export AUTOSSH_GATETIME=0
 export AUTOSSH_DEBUG=1
 autossh -M 0 -fTnN -i ~/.ssh/id_dsa -F ~/.ssh/config -o ForwardX11=no -L  3307:p05153026131444.cern.ch:3306  prod4

You can now start the tunnel manually via
remotehost> sudo ssh -fTnN -i ~/.ssh/id_dsa -F ~/.ssh/config -o ForwardX11=no -L 3307:p05153026131444.cern.ch:3306 prod4

See also
https://twiki.atlas-canada.ca/bin/view/AtlasCanada/Password-lessSsh
http://www.linuxproblem.org/art_9.html

-- KirstenSachs - 10 Apr 2014