The connection attempt failed when try connect PostgreSQL via SSH tunnel. I using Java with jsch lib

Question

I'm facing an issue when I try to connect to PostgreSQL Database after connected SSH successfully. I'm using Java + JSCH library to help connect to DB PostgreSQL via SSH tunnel. Please help to take a look on my code:

public static Connection connectDBAWS(String ipAddress, int port, String dbName, String username, String password, String tunnelHost, int tunnelPort, String tunnelUsername, String tunnelPassword) {

        Connection connection;
        Session session;

        try {

            //SSH TUNNEL connection
            JSch jsch = new JSch();

            session = jsch.getSession(tunnelUsername, tunnelHost, 22);
            session.setPassword(tunnelPassword);
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect();
            System.out.println("Connected Successfully");
            int assinged_port = session.setPortForwardingL(port, tunnelHost, tunnelPort);
            System.out.println("assinged_port -> " + assinged_port);
            System.out.println("Port Forwarded");

            //DB connection
            String dbUrl = "jdbc:postgresql://" + ipAddress + ":" + assinged_port + "/" + dbName;
            Class.forName("org.postgresql.Driver");;
            connection = DriverManager.getConnection(dbUrl, username, password);
            System.out.println("connect DB success");
        } catch (Exception e) {
            connection = null;
            e.printStackTrace();
        }

        return connection;

    } 

An exception is thrown at following step:

connection = DriverManager.getConnection(dbUrl, username, password);

Here is result:

Connected Successfully
assinged_port -> 5432
Port Forwarded
org.postgresql.util.PSQLException: The connection attempt failed.
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:292)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
    at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
    at org.postgresql.Driver.makeConnection(Driver.java:454)
    at org.postgresql.Driver.connect(Driver.java:256)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
...

I used value above to connect PostgreSQL manually, using pgAdmin4, and connect successfully: See configuration image here.

Please someone could give some ideal for fixing my code.

Answer 1

int assinged_port = session.setPortForwardingL(port, tunnelHost, tunnelPort);

I think the problem is that you have no idea what any of these values are supposed to mean. You're passing the wrong values in the wrong places. The assigned_port value makes no sense at all here, for example.

The idea behind an L-type port forward is this:

• connections to the local port port go across the SSH connection to the server you connected to...
• and then pop out the other side. The other side will then connect to host tunnelHost, on port tunnelPort, and thus the forward will proceed.

Given that's how it works, the names and values are all wrong.

• tunnelPort is surely the 'port of the tunnel'. In other words, that's the local port - it should be the first argument passed to setPortForwardingL, not the last.
• the second and third parameter tell the remote SSH server what to connect to, to finish the ports. Given that you're SSHing to the server, you don't want the server to then connect to the server. That sometimes works, but often it doesn't, depends on network sessions. You instead want localhost there! Remember, that server/port combo is connected to server-side. 'localhost' here as value would be referring to what resolved as localhost on the server. Which is what you want.
• Finally, the point of SSH tunnels is that you have a local port on your local box - that is where the tunnel 'starts'. Thus, you want your JDBC to connect to localhost. You're connecting to ipAddress, whatever that might be.

So, the fixed code is:

// The host in the cloud running an SSH server that you're connecting to.
String tunnelHost = "mypsqlserverinthecloud.com";
// The host in the cloud with the PSQL server, as you would
// connect to it from tunnelHost! In most cases this should be localhost!
String psqlHost = "localhost";
int psqlPort = 5432; // port of the psql server on the psql box.
int tunnelPort = 1234; // pick whatever you want here.
String username = "LDT";
String password = "hunter2"; // please see note below!

JSch session = new JSch();
session = jsch.getSession(username, tunnelHost, 22);
// Why aren't you using key-based authentication?
// Why aren't you using strict host key checking?
// None of this is particularly secure as a consequence!
session.setPassword(password);
session.setConfig("StrictHostKeyChecking", "no");
session.connect();

System.out.println("Connected Successfully");
session.setPortForwardingL(tunnelPort, psqlHost, psqlPort);
System.out.println("Port Forwarded");

String dbUrl = "jdbc:postgresql://localhost:" + tunnelPort + "/" + dbName;
// you do not need Class.forName. Not for the past 20 years.
// what stone age tutorial are you following?
// Consider that it probably contains more bad / obsolete advice!
Connection con = DriverManager.getConnection(dbUrl, username, password);
System.out.println("connect DB success");

There are also multiple code style errors in this. For example, if you ever write code that catches an exception, e.printStackTrace() it, and then keeps going, stop what you're doing. That is horrible, horrible error handling. Just make these methods throw the checked exception. A method that connects to a DB should be throwing SQLException and probably IOException. If you really can't do that or don't want to, then either handle the exception, and for the 90%+ of exceptions where there is nothing you can think of (that's normal), it shuold always end in throw. So not:

} catch (SQLException e) {
e.printStackTrace();
return null; // idiotic. Do not do this.
}

Instead, do:

public void theMethodThisCodeWasIn() throws SQLException {

or if you can't, do:

} catch (SQLException e) {
    throw new RuntimeException("Not handled", e);
}

for example. Preferably, write up how exceptions are handled; throwing the type RuntimeException is a last resort. But still miles better than e.printStackTrace(); and continuing to run!