[Feature Request]: SSH tunnel - Make dynamically allocated local port available as variable to support more complex JDBC connection strings

[hschildberg]

What would you like to happen?

The native SSH tunnel support for database connections is a very welcome addition. Thank you for working on this feature.

While reviewing the implementation, I thought of a possible enhancement that could make it even more flexible.

Currently, the SSH tunnel integration constructs the database connection URL using:

        String tunnelUrl =
            databaseMeta
                .getIDatabase()
                .getURL(
                    "localhost",
                    String.valueOf(localPort),
                    resolve(databaseMeta.getDatabaseName()));
        url = resolve(tunnelUrl);

This works well for simple, standard connection URLs.

However, some database configurations require manually defined JDBC URLs, where the host and port are embedded in a more complex connection string.

For example:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=localhost)(PORT=<localPortOFSShTunnel>))(CONNECT_DATA=(SID=XYZ))

It would be great if native SSH tunnel support could also be used together with manually specified database URLs.

One possible approach could be exposing the assigned local tunnel port through a variable such as: ${sshTunnel.localPort}

This would allow users to reference the dynamically allocated tunnel port directly within custom JDBC URLs while keeping the flexibility of the existing manual URL configuration.

Just an idea for consideration, as it could help cover some more advanced connection scenarios.

Thanks again for the effort that went into this feature.

Issue Priority

Priority: 3

Issue Component

Component: Database

[hansva]

But that would be using the tunnel via the JDBC driver and not a separate tunnel.
In the JDBC url you could just use the environment variables. Becuase I think the configuration you are suggesting would create a tunnel on a tunnel.

[hschildberg]

Mmh, no, I think that is a misunderstanding.

The JDBC URL still points to the local part of the SSH tunnel - just as today. But the JDBC URL can be more complex than just localhost:<localPort>/<database> if the dynamically allocated local port could be resolved at runtime in the manually specified URL.

From a metadata perspective I would imagine something like this:

{
  "rdbms": {
    "ORACLE": {
      "pluginId": "ORACLE",
      "accessType": 0,
      "hostname": "${db_host_behind_bastion}",
      "port": "${db_port}",
      "username": "${db_user}",
      "password": "${db_password}",
      "manualUrl": "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=localhost)(PORT=<localPortOFSShTunnel>))(CONNECT_DATA=(SID=XYZ))",
      "databaseName": "",
      "pluginName": "Oracle",
      "attributes": {
        "SUPPORTS_TIMESTAMP_DATA_TYPE": "Y",
        "QUOTE_ALL_FIELDS": "N",
        "SUPPORTS_BOOLEAN_DATA_TYPE": "Y",
        "FORCE_IDENTIFIERS_TO_LOWERCASE": "N",
        "PRESERVE_RESERVED_WORD_CASE": "Y",
        "SQL_CONNECT": "",
        "FORCE_IDENTIFIERS_TO_UPPERCASE": "N",
        "PREFERRED_SCHEMA_NAME": ""
      }
    }
  },
  "name": "DB via SSH tunnel"
}

hostname and port still define the remote end of the SSH tunnel. The manualUrl points to the local part of the SSH tunnel, but uses a more complex format.

In this Oracle sample it would be required in case you need to specify a connection protocol of TCPS, for example.

Since the host is always known (localhost) it can be fixed, but the port is dynamically allocated and needs to be resolved at runtime.