https://dagster.io/ logo
#ask-community
Title
# ask-community
a

Ahmed Al-Dulimi

07/05/2023, 5:12 PM
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:
Copy code
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:
Copy code
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)
Copy code
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
Copy code
dbt_assets = load_assets_from_dbt_project(
    project_dir=DBT_PROJECT_PATH, profiles_dir=DBT_PROFILES, 
)
thanks :)
n

Navah Farahat

07/05/2023, 9:07 PM
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!
but anyway, that's all the
dagster-ssh
resource is doing! it's establishing the connection using
paramiko.SSHTunnelForwarder()
, so if you can put together an independent python script that tries to connect that way, the error will probably be more descriptive!
a

Ahmed Al-Dulimi

07/06/2023, 6:25 AM
Thanks for the response, this is what it looks like in its own python script. It works fine like this
Copy code
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:
Copy code
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?
Copy code
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?
n

Navah Farahat

07/06/2023, 1:59 PM
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?
i can't tell on mobile if
SSHResource
and the
DbtCliClientResource
also need to be given their args through the
.configured()
method, but i just noticed you didn't
i just pulled up my own code and all of my resources are configured with
snake_case_resource.configured({kwargs_dict})
, including multiple `dbt_cli_resource`s, `snowflake_resource`s, etc.
a

Ahmed Al-Dulimi

08/04/2023, 4:13 PM
Forgot to come back, but that did end up working. Thank you Navah, ur awesome 🙂
daggy love 1
t

Todd de Quincey

08/09/2023, 3:03 PM
@Navah Farahat or @Ahmed Al-Dulimi would you be able to provide a code snippet to your working dbt + ssh resources?
In particular, struggling to get the port forwarding to work
n

Navah Farahat

08/10/2023, 1:50 PM
@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
👀 1
t

Todd de Quincey

08/11/2023, 12:52 PM
That would be absolutely amazing if you could @Navah Farahat!
n

Navah Farahat

08/11/2023, 4:22 PM
@Todd de Quincey okay, here’s what ahmed was doing first to try to instantiate an ssh resource object, and it didn’t work:
Copy code
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:
Copy code
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 😆
@Todd de Quincey however, i went to go find a clear example in the docs for another resource that’s configured the same way, and i see that there has been some big changes to resources in 1.3!! the examples i just gave are based on what seem to now be “legacy” resources. my deployments are running 1.2.6, so that’s all i know. looks like i’ve got some reading to to here, so keep in mind what applies to you and what doesn’t for your version!
5 Views