Using dbt-core on Heroku app with proxy to connect to a private database

Hi dbt community!

I’ve connected to private databases using dbt-core before by whitelisting a static IP that the app uses in AWS.

I’m trying this on a Heroku app with the Proximo addon that forwards all the apps traffic through a static IP that I’m whitelisting on the database server’s firewall.

When I run ‘dbt debug’ in this environment, I get “All checks passed”. But when I try to run one of my models, I get a Database Error: “timeout expired”. I’ve never seen a situation where debug succeeds and run fails to connect! I’ve also successfully connected to the database in this environment in a python app that uses psycopg2. Please share any insight that you may have. Thank you! Detailed logs below:

dbt run logs:

19:17:22.536295 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc29836d0>]}
19:17:22.544922 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc2982fb0>]}
19:17:22.546241 [info ] [MainThread]: Found 2 models, 4 tests, 0 snapshots, 0 analyses, 193 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
19:17:22.546740 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': 'fd8ce4c8-6736-4d26-a10f-d2820dffd438', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc314e470>]}
19:17:22.549925 [info ] [MainThread]: 
19:17:22.550592 [debug] [MainThread]: Acquiring new redshift connection "master"
19:17:22.555779 [debug] [ThreadPool]: Acquiring new redshift connection "list_dev"
19:17:22.581653 [debug] [ThreadPool]: Using redshift connection "list_dev"
19:17:22.583627 [debug] [ThreadPool]: On list_dev: /* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "ip_test", "target_name": "prod", "connection_name": "list_dev"} */

    select distinct nspname from pg_namespace
  
19:17:22.584550 [debug] [ThreadPool]: Opening a new connection, currently in state init
19:17:22.585092 [debug] [ThreadPool]: Redshift adapter: Connecting to Redshift using 'database' credentials
19:17:32.606154 [debug] [ThreadPool]: Postgres adapter: Got an error when attempting to open a postgres connection: 'connection to server at "[removed for privacy]" ([removed for privacy]), port 5439 failed: timeout expired
'
19:17:32.606839 [debug] [ThreadPool]: Postgres adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.1.0", "profile_name": "ip_test", "target_name": "prod", "connection_name": "list_dev"} */

    select distinct nspname from pg_namespace
  
19:17:32.607185 [debug] [ThreadPool]: Postgres adapter: Rolling back transaction.
19:17:32.608147 [debug] [ThreadPool]: Postgres adapter: Error running SQL: macro list_schemas
19:17:32.608396 [debug] [ThreadPool]: Postgres adapter: Rolling back transaction.
19:17:32.609018 [debug] [ThreadPool]: On list_dev: No close available on handle
19:17:32.611206 [debug] [MainThread]: Connection 'master' was properly closed.
19:17:32.611439 [debug] [MainThread]: Connection 'list_dev' was properly closed.
19:17:32.611669 [error] [MainThread]: Encountered an error:
Database Error
  connection to server at "[removed for privacy]" ([removed for privacy]), port 5439 failed: timeout expired
  
19:17:32.612484 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc2983a00>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc4b2e710>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7f1fc41c5390>]}
19:17:32.612953 [debug] [MainThread]: Flushing usage events

dbt debug logs:

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
19:26:32.050026 [debug] [MainThread]: Executing "git --help"
19:26:32.060361 [debug] [MainThread]: STDOUT: "b"usage: git [--version] [--help] [-C <path>] [-c <name>=<value>]\n           [--exec-path[=<path>]] [--html-path] [--man-path] [--info-path]\n           [-p | --paginate | -P | --no-pager] [--no-replace-objects] [--bare]\n           [--git-dir=<path>] [--work-tree=<path>] [--namespace=<name>]\n           [--super-prefix=<path>] [--config-env=<name>=<envvar>]\n           <command> [<args>]\n\nThese are common Git commands used in various situations:\n\nstart a working area (see also: git help tutorial)\n   clone     Clone a repository into a new directory\n   init      Create an empty Git repository or reinitialize an existing one\n\nwork on the current change (see also: git help everyday)\n   add       Add file contents to the index\n   mv        Move or rename a file, a directory, or a symlink\n   restore   Restore working tree files\n   rm        Remove files from the working tree and from the index\n\nexamine the history and state (see also: git help revisions)\n   bisect    Use binary search to find the commit that introduced a bug\n   diff      Show changes between commits, commit and working tree, etc\n   grep      Print lines matching a pattern\n   log       Show commit logs\n   show      Show various types of objects\n   status    Show the working tree status\n\ngrow, mark and tweak your common history\n   branch    List, create, or delete branches\n   commit    Record changes to the repository\n   merge     Join two or more development histories together\n   rebase    Reapply commits on top of another base tip\n   reset     Reset current HEAD to the specified state\n   switch    Switch branches\n   tag       Create, list, delete or verify a tag object signed with GPG\n\ncollaborate (see also: git help workflows)\n   fetch     Download objects and refs from another repository\n   pull      Fetch from and integrate with another repository or a local branch\n   push      Update remote refs along with associated objects\n\n'git help -a' and 'git help -g' list available subcommands and some\nconcept guides. See 'git help <command>' or 'git help <concept>'\nto read about a specific subcommand or concept.\nSee 'git help git' for an overview of the system.\n""
19:26:32.061313 [debug] [MainThread]: STDERR: "b"ERROR: ld.so: object 'libdl.so' from LD_PRELOAD cannot be preloaded (cannot open shared object file): ignored.\n""
 - git [OK found]

Connection:
  host: [removed for privacy]
  port: 5439
  user: awsuser
  database: dev
  schema: public
  search_path: None
  keepalives_idle: 240
  sslmode: None
  method: database
  cluster_id: None
  iam_profile: None
  iam_duration_seconds: 900
19:26:32.066420 [debug] [MainThread]: Acquiring new redshift connection "debug"
19:26:32.067448 [debug] [MainThread]: Using redshift connection "debug"
19:26:32.067980 [debug] [MainThread]: On debug: select 1 as id
19:26:32.073439 [debug] [MainThread]: Opening a new connection, currently in state init
19:26:32.074219 [debug] [MainThread]: Redshift adapter: Connecting to Redshift using 'database' credentials
19:26:32.359175 [debug] [MainThread]: SQL status: SELECT in 0.28 seconds
19:26:32.362744 [debug] [MainThread]: On debug: Close
  Connection test: [OK connection ok]

All checks passed!
19:26:32.377432 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e270fa0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e271330>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x7fae8e2738e0>]}
19:26:32.378168 [debug] [MainThread]: Flushing usage events
19:26:32.676501 [debug] [MainThread]: Connection 'debug' was properly closed.