Setting up an SSH tunnel for DSS Connection

tgb417
tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron

What is the best way to configure a design node to make a secure connection to a PostgreSQL server?

I'm thinking that an ssh tunnel might be a way, using an ssh command like this:

ssh -L5211:/var/run/postgresql/.s.PGSQL.5432 account@myremotehost.com

The above seems to complete successfully from my localhost.

Then to create a Postgress DSS Connection sort of like this:

Host = localhost
Database = mydatabase
port = 5211
User = account
Password = [blank]

Unfortunately. when trying to test this connection I get.

Inital PostgreSQL connection error.jpg

When I look at the "more errors" link I get.

Furrther PostgreSQL error message.jpg

If folks think that this is a good approach for connecting to a PostgreSQL server remotely in an encrypted way to a design node. Then I also wonder if there is a way to automatically make setup of the ssh tunnel each time that DSS is started or each time this connection is used less manual. From an operational point of view, I'd hate to have to manually start the ssh tunnel, then start DSS.

I'm currently working from a macintosh computer running OSX 10.13.x, connecting to a Linux remote server with the PostgreSQL server.

Tagged:

Answers

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker
    edited July 17

    Hi

    considering the settings in the DSS connection, why not simply:

    ssh -L 5211:localhost:5432 account@myremotehost.com

    Is your postgresql server not binding on all interfaces for port 5432? (on the remote machine)

    Regards,

    Frederic

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron
    edited July 17

    I found that a command:

    ssh -N -L 5211:/var/run/postgresql/.s.PGSQL.5432 account@myremotehost.com

    Worked OK.

    Note: I add a space between the “L” and the first “5” and adding a “-N” as the first parameter of the command.

    I also found that you had to click the create button on the DSS connection screen before you could test the connection without error.

    Now the question, I'd like to grant DSS the opportunity to set up its own secure tunnel rather than having to go to a terminal window to open the tunnel. Is there a "safe" and "effective" way to get this done?

  • fchataigner2
    fchataigner2 Dataiker Posts: 355 Dataiker

    DSS has no facility to open SSH tunnels on your behalf. If you need tunnels, it's a manual process

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron

    @fchataigner2

    Thanks for the insight.

  • laurelix
    laurelix Partner, Dataiku DSS Core Designer, Registered Posts: 3 Partner

    quick question, how to type this command on dataiku
    I don't find any cmd or terminal like in dataiku dss

  • tgb417
    tgb417 Dataiku DSS Core Designer, Dataiku DSS & SQL, Dataiku DSS ML Practitioner, Dataiku DSS Core Concepts, Neuron 2020, Neuron, Registered, Dataiku Frontrunner Awards 2021 Finalist, Neuron 2021, Neuron 2022, Frontrunner 2022 Finalist, Frontrunner 2022 Winner, Dataiku Frontrunner Awards 2021 Participant, Frontrunner 2022 Participant, Neuron 2023 Posts: 1,598 Neuron

    @laurelix
    ,

    This command was not typed into dss directly. This was being typed into a separate terminal connection of the operating system supporting the DSS Server application.

    Hope that helps.

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,067 Neuron

    SSH is really a very archaic way of establishing a remote connection. My advice will be to setup a proper VPN. WireGuard is now the way to go although it's a bit fiddly to configure. Tailscale is an alternative to setup WireGuard in a more user friendly way.

  • Turribeach
    Turribeach Dataiku DSS Core Designer, Neuron, Dataiku DSS Adv Designer, Registered, Neuron 2023 Posts: 2,067 Neuron

    I forgot to mention SSHFS. While is less than ideal to use SSH as a file system if that's the only option you got SSHFS might be a viable option. Don't expect proper file system reliability over these SSH mounts so make sure you build resiliancy in your processes to handle connection issues.

Setup Info
    Tags
      Help me…