Java + SSH + Postgres

Question

We are implementing an university project: a car-pooling service in Java.

We need to solve a problem linked "how to manage a postgres server": the PostgreSQL Database is configured in a lab server called "golem" (130.136.4.sth) reachable only through terminals in the same subnet (130.136.4.0). We have four account (ours) through we can establish a ssh connection to an host.

Is it possible to make SQL queries through SSH towards Postgres DB in JAVA?

Thank you :) Davide

Answer 1

If you run "ssh" followed by any command that command gets executed on the remote host. So you should be able to run pre-baked queries in batch mode via ssh.

Consider doing key-gen and key exchanges to enable passwordless ssh execution.

Example (this just dumps a directory listing to your terminal):

ssh me@mybox ls

Answer 2

If this is just for development, you can use ssh port forwarding to access the database as if it was installed locally. How port forwarding is enabled depends on the client software you use, openssh for example has a command line switch for it (-L):

ssh user@host -L localport:remotehost:remoteport

This command would make the remoteport on remotehost, though accessible only through host, available on localport on your computer.

Answer 3

Take a look at the other suggested answers as they seem easier to accomplish what you need.

However, if you really need to implement the command submission with Java for your lab assignment, you can take a look at the JSch (Java Secure Channel) library found here: http://www.jcraft.com/jsch/ Examples are here http://www.jcraft.com/jsch/examples/

With it you can submit ssh commands and perform any kind of operation via a Java API