4

this is a bit complicated, please bear with me

I want to use org-babel to query postgresql, and I can do it for DBs that I can access directly from my localhost:

#+begin_src sql :engine postgresql :cmdline "-h localhost -U postgres -p 5434 -d my_db"

I can even probably do that if DB is on a remote ssh machine, by setting ssh tunnel first.

The problem is: in my company (for security reasons) it's made that you have to:

  • first ssh to a machine (let's call it first-lvl-host)
  • and only then access the DB, which is still on a remote host (let's call it db-lvl-host)

Another problem is, that first-lvl-host does not even have psql (command line utility). In order to access it - you have to ssh hop to another machine (let's call it second-lvl-host)

Basically I'm expected to do:

ssh first-lvl-host ssh second-lvl-host

# and then:
PGPASSWORD=mypass psql -h db-lvl-host -U ag

So, my question is, can I anyhow still do it directly from my org file?

I know I can reduce ssh hops by adding:

Host second-lvl-host
  ProxyJump first-lvl-host

to my ssh/config, but that still doesn't allow me to access db-machine directly, using local psql, and if I can't poke on it with psql, I don't think I can do it with org-babel, or can I?

I tried setting a tunnel like this:

ssh -L 9999:localhost:9999 first-lvl-host ssh -L 9999:localhost:1234 -N db-lvl-host

but that didn't work. I can't ssh to db-lvl-host, even if I do ssh first-lvl-host ssh second-lvl-host ssh db-lvl-host. It doesn't work like that. I can't connect to db-lv-host via ssh, it reports: "Connection timed out", if I can't ssh to it - means I can't establish a tunnel, right?

Improve this question
0

1 Answer 1

Reset to default
3

You might can try TRAMP multi-hops, Like open the org file on db-lv-host, it has the SQL babel, then use TRAMP multi-hops to open it, then execute it. It might will executed on db-lv-host. I have not tested that. But it should work on theory.

#+headers: :cmdline -h localhost -U postgres -w -d my_db -p 5434
#+begin_src sql :engine postgresql :dir /ssh:user1@host1|/ssh:user2@host2
SELECT * from test
#+end_src

If you find it works, can you paste out your steps, I need to know it.

Improve this answer
5
  • I think this should work, but it doesn't work for me, because there's one missing piece: the password. I need to find a way to set it and voila
    – iLemming
    Commented May 29, 2018 at 20:53
  • I've tried setting :dbpassword header as explained in here: orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html, and now getting: gzip: (stdin): unexpected end of file
    – iLemming
    Commented May 29, 2018 at 21:59
  • oh, no... it actually worked, just tried this: #+begin_src sql :engine postgresql :dir /ssh:uat|ssh:sql-machine: :dbpassword "PASSWORD" :dbhost uat-green-rds-postgres.amazonaws.com :dbuser postgresql :database kafka_connect_postgresql :cmdline "-p 5432" select * from bank_transaction limit 1; #+end_src It didn't work previously because of \d command. Although it works locally, you can't expect it to work for remote DBs. Sql statements work fine.
    – iLemming
    Commented May 29, 2018 at 22:33
  • oh. it seems not related to the command... it has something to do with the number of rows. I guess by default it allows limited number of rows to be transferred. Maybe there's a way to extend that?
    – iLemming
    Commented May 29, 2018 at 23:03
  • About TRAMP multi-hops SSH connection password, they can be stored in auth-sources, .netrc etc.
    – stardiviner
    Commented May 30, 2018 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.