[emacs-berlin] Execute SQL on remote hosts with org-mode and tramp

[Tilmann Singer]

Hello everyone,

From time to time I need to execute an SQL query on a remote postgresql
database, on a host that is only reachable via ssh, and I was wondering
what would be a good org-mode integrated way to do that. I found a
solution which I'd like to share and ask for feedback from you.

org-babel includes the language sql, which seems like the obvious
choice. According to its implementation in ob-sql.el, it supports a
couple of args, such as :engine, :cmdline, :dbhost etc., and it
automatically formats the output as org-mode table.

One possibility would be to make the database accessible over the
network, and then use :dbhost, :dbport etc. to connect to it, so a
source block could look like this:

#+BEGIN_SRC sql :engine postgresql :database test :dbhost the.remote.host :dbport 5432 :dbuser test :dbpassword secret
SELECT * FROM fruits
#+END_SRC

However for security reasons that might not be feasible. Alternatively,
one could set up port forwarding via ssh, so that after initiating an
ssh connection, the database would be accessible on a local port. The
org-babel block might look like this then:

#+BEGIN_SRC sql :engine postgresql :database test :dbhost localhost :dbport 5433 :dbuser test :dbpassword secret
SELECT * FROM fruits
#+END_SRC

All well, but what if the remote postgresql doesn't even allow that, you
don't want to or can't reconfigure it, and the only way to connect to is
as a specific local user on the remote host? Tramp to the rescue! Here
is an example, including the results which get nicely formatted as an
org-mode table:

#+BEGIN_SRC sql :engine postgresql :database test :dir /ssh:til-test|sudo:localhost|sudo:postgres at localhost:~
SELECT * FROM fruits
#+END_SRC

#+RESULTS:
| id | name    |
|----+---------|
|  1 | apple   |
|  2 | banana  |
|  3 | apricot |

In this example, "til-test" is a remote hostname configured in my
.ssh/config. The host requires to first sudo to root, then sudo to the
postgres user, in order to access the database named "test". Thanks to
tramp magic, this works, and the "psql" command is executed on the
remote host as the user postgres.

The only drawback is that it establishes the connection every time it is
executed and is therefore a bit slow. Specifying a session does not have
an effect:

#+BEGIN_SRC sql :engine postgresql :database test :dir /ssh:til-test|sudo:localhost|sudo:postgres at localhost:~ :session *test*
SELECT * FROM fruits
#+END_SRC

and in ob-sql.el it says

;; TODO:
;;
;; - support for sessions

which might be the reason ;)

If anyone has a better way, or has an idea how hard it would be to add
session support to ob-sql.el, please let me know.


Til


ps: Org mode version 9.0 (9.0-elpa @ /.../org-20161102/), so it could be
outdated, but ob-sql.el didn't change significantly since then according
to https://code.orgmode.org/bzg/org-mode/commits/master/lisp/ob-sql.el
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 487 bytes
Desc: not available
URL: <http://mailb.org/pipermail/emacs-berlin/attachments/20180227/ec6ad06b/attachment.sig>