org-babel and remote sql commands

Question

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!

Answer 1

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} |

Answer 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.