Hi guys, I'm having difficulty getting dbt to connect to db through ssh tunnelling. Normally, without Dagster, I can run this command

ssh dbt@bastion-prod.company.something -N -C -L "5432:<http://prod-db.something.amazonaws.com:5432|prod-db.something.amazonaws.com:5432>"

and my

profiles.yml

would look something like this:

threads: 4
host: localhost
port: 5432
user: dbt
pass: psql_pass
dbname: db
schema: schema

I tried replicating this in Dagster using dagster-ssh, but there aren't any examples to see how. Using the source code as reference, I tried this but it didn't work:

resources = {
    "dbt": DbtCliClientResource(
        project_dir=DBT_PROJECT_PATH,
        profiles_dir=DBT_PROFILES,
    ),
    "ssh": SSHResource(
        remote_host="bastion-prod.company.something", 
        remote_port=22,
        key_string=PRIVATE_KEY,
        username="dbt", timeout=20).get_tunnel(
            remote_port=5432, 
            remote_host="<http://prod-db.something.amazonaws.com|prod-db.something.amazonaws.com>",
            local_port=5432)
   
}

I get this error (but the key is correct when i do it outside Dagster)

Database Error
  connection to server at "localhost" (::1), port 5432 failed: FATAL:  password authentication failed for user "dbt"

If it helps, this is how I'm loading by dbt assets

dbt_assets = load_assets_from_dbt_project(
    project_dir=DBT_PROJECT_PATH, profiles_dir=DBT_PROFILES, 
)

thanks :)

dagster-ssh

uses paramiko under the covers, and i bet it's paramiko that needs your connection parameters to be different, probably the key. i have used paramiko in a few standalone scripts (outside of dagster) to connect to our postgres db via ssh tunnel, and IIRC i couldn't just take the flags and values from my typical

ssh navah@integration-db... -N -C

command and make them kwargs in a

paramiko.SSHTunnelForwarder()

object. but i don't remember what i had to change 😩 sorry!

Thanks for the response, this is what it looks like in its own python script. It works fine like this

server = SSHTunnelForwarder(
    'bastion-prod.company.something',
    ssh_username="dbt",
    ssh_pkey="~/.ssh/id_rsa",
    remote_bind_address=("<http://prod-db.something.amazonaws.com|prod-db.something.amazonaws.com>", 5432),
    local_bind_address=('0.0.0.0', 5432)
)

server.start()
conn = psycopg2.connect(host="127.0.0.1", port=5432, dbname="something", user="dbt", password="something")

but when I try to use

dagster-ssh

like this:

resources = {
    "dbt": DbtCliClientResource(
        project_dir=DBT_PROJECT_PATH,
        profiles_dir=DBT_PROFILES,
        target="prod",
    ),
    "ssh": SSHResource(
        remote_host="bastion-prod.company.something", 
        remote_port=22,
        key_file="~/.ssh/id_rsa",
        username="dbt").get_tunnel(
            remote_port=5432, 
            remote_host="<http://prod-db.something.amazonaws.com|prod-db.something.amazonaws.com>",
            local_port=5432),
   
}

I get the following dbt error, which I'm assuming means the tunnel wasn't setup correctly. Am I using the

SSHResource

correctly here?

Database Error
  connection to server at "localhost" (::1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
  connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

i'm on mobile so i can check if it's the EXACT same, but at a glance you might be doing the same thing that was fixed in this support thread?

Forgot to come back, but that did end up working. Thank you Navah, ur awesome 🙂

@Navah Farahat or @Ahmed Al-Dulimi would you be able to provide a code snippet to your working dbt + ssh resources?

@Todd de Quincey sure thing, i'll write out my suggestion that ended up working for Ahmed as an actual snippet when i'm at my computer

That would be absolutely amazing if you could @Navah Farahat!

@Todd de Quincey okay, here’s what ahmed was doing first to try to instantiate an ssh resource object, and it didn’t work:

ssh_resource = SSHResource(remote_host=...
                           remote_port=...,
                           username=..., 
                           ...)

and here’s the syntax you actually want to use to create a configured ssh resource object:

ssh_connection = ssh_resource
                  .configured({"remote_host": ...,
                                "remote_port": ...,
                                "username":...,
                                ...
                                })

basically you have to give the configuration params as a dict through the

.configured()

method, and that’s general advice for all resource objects with configuration, which is why i made the example so generalized 😆

Crossposting the solution for posterity: https://dagster.slack.com/archives/C04CW71AGBW/p1691779596773389?thread_ts=1691656178.689729&cid=C04CW71AGBW