'Connect dbt to Postgres using SSH bastion

We are looking to connect dbt to Postgres using SSH bastion. I followed the comments left under this issue, but I get a timeout error.

A few questions:

  1. How should the profiles.yml be configure to connect via SSH? I added ssh-host but that did not get it working.
  2. Is there any other configurations that I'd need to set up?
postgresqlsshconnectiondbtbastion-host


Solution 1:[1]

I think you need to follow Jeremy's instructions from this comment:

The basic idea, as I remember it:

  • You register a public SSH key with the remote location, tied to a private key that lives on your machine
  • You use a CLI tool (e.g. ssh, autossh) to "forward" a local port to the remote location (bastion host)
  • In profiles.yml, instead of putting the host/port of a remote database, you put localhost and the number of the "forwarding" port
  • Voila! Your connection is forwarded to the bastion host, authenticated via SSH, and passed along to the database

To be fair, he was also asking for definitive walkthroughs and included the caveat that this has had varying levels of success based on the particulars of the client, host, environment etc.

Solution 2:[2]

I just hacked my way through figuring this out and the steps listed in the above comment were very helpful for someone with zero experience in this realm who still needs to use dbt with a bastion host. Here is specifically how I did this and some helpful resources I came across. Hopefully others will find these examples helpful.

You register a public SSH key with the remote location, tied to a private key that lives on your machine

Github has a helpful guide for how to do this: https://docs.github.com/en/authentication/connecting-to-github-with-ssh/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent
Add keys to ~/.ssh/config: Adding an RSA key without overwriting.
I also had to add IgnoreUnknown UseKeychain to ~/.ssh/config

You use a CLI tool (e.g. ssh, autossh) to "forward" a local port to the remote location (bastion host)

To forward the local port to the bastion host, save your user/bastion host/db host into environment variables. I used Postgres so it looked like this.

ssh -l $BASTION_USER $BASTION_HOST -p 22 -N -C -L "5432:${POSTGRES_HOST}:5432";

In profiles.yml, instead of putting the host/port of a remote database, you put localhost and the number of the "forwarding" port

Then in my ~/.dbt/profiles.yml looks includes this:

dev:
    type: postgres
    threads: 1
    host: localhost 
    port: 5432
    user: POSTGRES_USER
    pass: POSTGRES_PWD
    dbname: POSTGRES_DB_NAME
    schema: dbt_tmp

Voila! Your connection is forwarded to the bastion host, authenticated via SSH, and passed along to the database

At that point I ran dbt debug against my target and it connected with all checks passed.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sgdata
Solution 2

Related Questions