10

I'm using org-babel to do litterate programming and I find it very convenient. I am able to execute shell commands on remote hosts, and local database commands with the postgres client. To open a file as postgres user, it would look for example like 

C-x C-f /sshx:user@remotehost|sudo:postgres@remotehost:/tmp/testfile

To do so on Emacs 24.5.1, I needed to add:

(require 'tramp)
(add-to-list 'tramp-default-proxies-alist
             '(nil "\\`postgres\\'" "/ssh:%h:"))
(add-to-list 'tramp-default-proxies-alist
             '((regexp-quote (system-name)) nil nil))

So far, I have failed to understand how one could execute a postgresql (or mysql if you so choose) command on a remote server. The database server is not accessible directly over the network, I have to ssh into the remote host, su as the postgres user and then I'm able to execute commands through the psql client.

How could one use such a command:

#+BEGIN_SRC sql :engine postgresql :ExtraParametersIfNeeded
SELECT * from pg_database
#+END_SRC

On a remote host? I know the :dir switch, but have so far only been able to use it for shell scripts.

Many thanks!

KookieMonster
  • 387
  • 4
  • 10
  • 1
    You could do some SSH tunnelling... You would do something akin to "`ssh yourbox -L7590:127.0.0.1:7590`" replacing the "`7590`" with whatever port your database is on. Then, in your Org babel block, you can use the "`:cmdline`" header argument to set a command line argument to set the database port. The whole thing might look something like this at the top of your Org file: "`#+PROPERTY: header-args:sql :engine mysql :dbhost 127.0.0.1 :dbuser someone :dbpassword somepassword :database database :cmdline -P7590 `". After `C-c C-c`-ing that, you can use any SQL block in your buffer like that. – Archenoth Jul 27 '15 at 17:53
  • Hi, I thought about it and I could not find any other way myself too. If you make it an answer, I'll gladly accept it. – KookieMonster Jul 29 '15 at 07:22
  • Well... I posted that as a comment because, while it solves your problem, it doesn't really answer your question. Nothing has changed on the Emacs end; you would just be creating an SSH tunnel manually so that the database *can* be accessed directly as opposed to making Emacs control the entire process through a header argument or some arbitrary configuration--so, I don't think I'll make an answer of that. Maybe someone else knows of a way to tell Org-babel to create this tunnel without any manual fudging. – Archenoth Jul 29 '15 at 16:16

2 Answers2

6

I'm not sure if it is working now because of updates in Emacs and tramp, but as there is no accepted solution and no exact snippet of code for those who are looking for answer, I'm providing one which is working in Emacs 25 and recent org-mode and tramp.

As you already proposed in your question, :dir is the solution, just take your src section and add same string as you used for opening files to the :dir arugment. There is no need to do SSH tunnel as this is something tramp can do out of the box.

#+BEGIN_SRC sql :engine postgresql :dir /sshx:user@remotehost|sudo:postgres@remotehost:
SELECT * FROM pg_database
#+END_SRC

Working just find and returns:

#+RESULTS:
| datname             | datdba | encoding | datcollate  | datctype    | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl                              |
|---------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+------------+---------------+-------------------------------------|
| postgres            |     10 |        6 | C.UTF-8     | C.UTF-8     | f             | t            |           -1 |         12408 |          545 |          1 |          1663 |                                     |
| template0           |     10 |        6 | C.UTF-8     | C.UTF-8     | t             | f            |           -1 |         12408 |          545 |          1 |          1663 | {=c/postgres,postgres=CTc/postgres} |
Kepi
  • 259
  • 3
  • 6
2

I'm not an org user, so I can answer only from the Tramp point of view. The Tramp settings you have shown above are not needed (add-to-list forms), since you are using ad-hoc multi-hops in your file name. So I assume a simple :dir "/sshx:user@remotehost|sudo:postgres@remotehost:/path/to/dir" should do the job.

Michael Albinus
  • 6,647
  • 14
  • 20
  • Thanks for you comment. In order to execute shell scripts (using the :dir command) I had to use the lines mentionned above, as I was getting a message in the style of "sudo is only allowed on local host". – KookieMonster Jul 27 '15 at 13:07
  • You're right, there is a subtle error in Tramp. I could reproduce the problem with the following code snippet: `#+BEGIN_SRC sh :dir /ssh:remotehost|sudo:postgres@remotehost:/tmp` `whoami` `#+END_SRC` As workaround, you could do something like this (untested): `#+BEGIN_SRC emacs-lisp` `(progn` `(require 'tramp)` `(add-to-list 'tramp-default-proxies-alist '(nil "\\`root\\'" "/ssh:%h:")))` `#+END_SRC` `#+BEGIN_SRC sh :dir /sudo:remotehost:/tmp` `whoami` `#+END_SRC` – Michael Albinus Jul 27 '15 at 17:47