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

Edit | Attach | Watch | Print version | History: r3 < r2 < r1 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r3 - 2014-06-26 - KirstenSachs
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    Inspire All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2023 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback