Repository: dbt-labs/dbt-redshift (python)

Issue:
[ADAP-500] [Bug] dbt-redshift 1.5 does not work with non-standard redshift hostnames
### Is this a new bug in dbt-redshift?

- [X] I believe this is a new bug in dbt-redshift
- [X] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

When upgrading to dbt 1.5, I get an 'index out of range error' when attempting to use things that connect to the database.

This is related to using a non-standard hostname for redshift - in my case I am opening a SSH tunnel to Redshift and using localhost as the host.

### Expected Behavior

It works with the same configuration as prior versions of DBT or allows a region configuration to side-step this issue.

### Steps To Reproduce

1. Start a SSH tunnel to redshift (the bug would also manifest if you have a cname to a redshift cluster) and localhost or the cname for the 'host' in your `profiles.yml`.
2. Attempt to run a model or do something that connects to redshift

### Relevant log output

```shell
❯ dbt compile
19:37:05  Running with dbt=1.5.0
19:37:05  Found 111 models, 356 tests, 6 snapshots, 0 analyses, 746 macros, 0 operations, 1 seed file, 135 sources, 0 exposures, 0 metrics, 0 groups
19:37:05
19:37:05  Encountered an error:
list index out of range
19:37:05  Traceback (most recent call last):
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 190, in exception_handler
    yield
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 80, in add_query
    cursor = connection.handle.cursor()
             ^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 94, in handle
    self._handle.resolve(self)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 118, in resolve
    return self.opener(connection)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 249, in open
    connect=connect_method_factory.get_connect_method(),
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 102, in get_connect_method
    "region": self.credentials.host.split(".")[2],
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 190, in exception_handler
    yield
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/impl.py", line 1043, in execute_macro
    result = macro_function(**kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 330, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 21, in macro
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 330, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 21, in macro
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 330, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 39, in macro
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 330, in __call__
    return self.call_macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/clients/jinja.py", line 257, in call_macro
    return macro(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 763, in __call__
    return self._invoke(arguments, autoescape)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 777, in _invoke
    rv = self._func(*arguments)
         ^^^^^^^^^^^^^^^^^^^^^^
  File "<template>", line 52, in macro
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/sandbox.py", line 393, in call
    return __context.call(__obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/jinja2/runtime.py", line 298, in call
    return __obj(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/impl.py", line 289, in execute
    return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 259, in execute
    _, cursor = self.add_query(sql, auto_begin)
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 284, in add_query
    connection, cursor = super().add_query(
                         ^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 58, in add_query
    self.begin()
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 170, in begin
    self.add_begin_query()
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 153, in add_begin_query
    return self.add_query("BEGIN", auto_begin=False)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 284, in add_query
    connection, cursor = super().add_query(
                         ^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/connections.py", line 67, in add_query
    with self.exception_handler(sql):
  File "/Users/alexkoch/.asdf/installs/python/3.11.2/lib/python3.11/contextlib.py", line 155, in __exit__
    self.gen.throw(typ, value, traceback)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 203, in exception_handler
    self.rollback_if_open()
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/connections.py", line 127, in rollback_if_open
    if conn is not None and conn.handle and conn.transaction_open:
                            ^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 94, in handle
    self._handle.resolve(self)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 118, in resolve
    return self.opener(connection)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 249, in open
    connect=connect_method_factory.get_connect_method(),
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 102, in get_connect_method
    "region": self.credentials.host.split(".")[2],
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 86, in wrapper
    result, success = func(*args, **kwargs)
                      ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 71, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 142, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 168, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 215, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/requires.py", line 250, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/cli/main.py", line 346, in compile
    results = task.run()
              ^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/task/runnable.py", line 441, in run
    result = self.execute_with_hooks(selected_uids)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/task/runnable.py", line 403, in execute_with_hooks
    self.before_run(adapter, selected_uids)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/task/runnable.py", line 390, in before_run
    self.populate_adapter_cache(adapter)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/task/runnable.py", line 381, in populate_adapter_cache
    adapter.set_relations_cache(self.manifest)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/impl.py", line 461, in set_relations_cache
    self._relations_cache_for_schemas(manifest, required_schemas)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/impl.py", line 150, in _relations_cache_for_schemas
    super()._relations_cache_for_schemas(manifest, cache_schemas)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/impl.py", line 438, in _relations_cache_for_schemas
    for relation in future.result():
                    ^^^^^^^^^^^^^^^
  File "/Users/alexkoch/.asdf/installs/python/3.11.2/lib/python3.11/concurrent/futures/_base.py", line 449, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/.asdf/installs/python/3.11.2/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/Users/alexkoch/.asdf/installs/python/3.11.2/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/utils.py", line 464, in connected
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/sql/impl.py", line 180, in list_relations_without_caching
    results = self.execute_macro(LIST_RELATIONS_MACRO_NAME, kwargs=kwargs)
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/impl.py", line 1042, in execute_macro
    with self.connections.exception_handler(f"macro {macro_name}"):
  File "/Users/alexkoch/.asdf/installs/python/3.11.2/lib/python3.11/contextlib.py", line 155, in __exit__
    self.gen.throw(typ, value, traceback)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 203, in exception_handler
    self.rollback_if_open()
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/base/connections.py", line 127, in rollback_if_open
    if conn is not None and conn.handle and conn.transaction_open:
                            ^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 94, in handle
    self._handle.resolve(self)
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/contracts/connection.py", line 118, in resolve
    return self.opener(connection)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 249, in open
    connect=connect_method_factory.get_connect_method(),
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/alexkoch/Library/Caches/pypoetry/virtualenvs/espark-dbt-7mBGFPKz-py3.11/lib/python3.11/site-packages/dbt/adapters/redshift/connections.py", line 102, in get_connect_method
    "region": self.credentials.host.split(".")[2],
              ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range
```


### Environment

```markdown
- OS: ubuntu 20.04, OSX 13.3.1
- Python: 3.11.2
- dbt-core: 1.5.0
- dbt-redshift: 1.5.0
```


### Additional Context

The adapter is hard-coded to parse the region from the host value.

https://github.com/dbt-labs/dbt-redshift/blob/aa98c958981fe112079e55b5831daf32b45b39f4/dbt/adapters/redshift/connections.py#L102

Pull request description:
Fix for #419: dbt-redshift 1.5 does not work with non-standard redshift hostnames
resolves #419 

<!---
  Include the number of the issue addressed by this PR above if applicable.
  PRs for code changes without an associated issue *will not be merged*.
  See CONTRIBUTING.md for more information.

  Example:
    resolves #1234
-->

### Description

Added region as a field and remove the host parsing hard-coding

### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-redshift/blob/main/CONTRIBUTING.md) and understand what's expected of me
- [x] I have signed the [CLA](https://docs.getdbt.com/docs/contributor-license-agreements)
- [x] I have run this code in development and it appears to resolve the stated issue
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [ ] I have [opened an issue to add/update docs](https://github.com/dbt-labs/docs.getdbt.com/issues/new/choose), or docs changes are not required/relevant for this PR
- [ ] I have run `changie new` to [create a changelog entry](https://github.com/dbt-labs/dbt-redshift/blob/main/CONTRIBUTING.md#Adding-CHANGELOG-Entry)


Patch:
diff --git a/.changes/unreleased/Fixes-20230428-142321.yaml b/.changes/unreleased/Fixes-20230428-142321.yaml
new file mode 100644
index 00000000..c7fefda4
--- /dev/null
+++ b/.changes/unreleased/Fixes-20230428-142321.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Adding region as independent param in profiles
+time: 2023-04-28T14:23:21.041865-07:00
+custom:
+  Author: nssalian
+  Issue: "419"
diff --git a/dbt/adapters/redshift/connections.py b/dbt/adapters/redshift/connections.py
index 9e830cb5..59058f53 100644
--- a/dbt/adapters/redshift/connections.py
+++ b/dbt/adapters/redshift/connections.py
@@ -7,6 +7,8 @@ from dataclasses import dataclass, field
 import agate
 import sqlparse
 import redshift_connector
+import urllib.request
+import json
 from redshift_connector.utils.oids import get_datatype_name
 
 from dbt.adapters.sql import SQLConnectionManager
@@ -17,13 +19,10 @@ import dbt.flags
 from dbt.dataclass_schema import FieldEncoder, dbtClassMixin, StrEnum
 from dbt.helper_types import Port
 
-
 logger = AdapterLogger("Redshift")
 
-
 drop_lock: Lock = dbt.flags.MP_CONTEXT.Lock()  # type: ignore
 
-
 IAMDuration = NewType("IAMDuration", int)
 
 
@@ -36,6 +35,23 @@ class IAMDurationEncoder(FieldEncoder):
 dbtClassMixin.register_field_encoders({IAMDuration: IAMDurationEncoder()})
 
 
+def _get_aws_regions():
+    # Extract the prefixes from the AWS IP ranges JSON to determine the available regions
+    url = "https://ip-ranges.amazonaws.com/ip-ranges.json"
+    response = urllib.request.urlopen(url)
+    data = json.loads(response.read().decode())
+    regions = set()
+
+    for prefix in data["prefixes"]:
+        if prefix["service"] == "AMAZON":
+            regions.add(prefix["region"])
+
+    return regions
+
+
+_AVAILABLE_AWS_REGIONS = _get_aws_regions()
+
+
 class RedshiftConnectionMethod(StrEnum):
     DATABASE = "database"
     IAM = "iam"
@@ -60,6 +76,7 @@ class RedshiftCredentials(Credentials):
     role: Optional[str] = None
     sslmode: Optional[str] = None
     retries: int = 1
+    region: Optional[str] = None  # if not provided, will be determined from host
 
     _ALIASES = {"dbname": "database", "pass": "password"}
 
@@ -78,6 +95,7 @@ class RedshiftCredentials(Credentials):
             "cluster_id",
             "iam_profile",
             "sslmode",
+            "region",
         )
 
     @property
@@ -85,6 +103,13 @@ class RedshiftCredentials(Credentials):
         return self.host
 
 
+def _is_valid_region(region):
+    if region is None or len(region) == 0:
+        logger.warning("Couldn't determine AWS regions. Skipping validation to avoid blocking.")
+        return True
+    return region in _AVAILABLE_AWS_REGIONS
+
+
 class RedshiftConnectMethodFactory:
     credentials: RedshiftCredentials
 
@@ -99,9 +124,27 @@ class RedshiftConnectMethodFactory:
             "port": self.credentials.port if self.credentials.port else 5439,
             "auto_create": self.credentials.autocreate,
             "db_groups": self.credentials.db_groups,
-            "region": self.credentials.host.split(".")[2],
+            "region": self.credentials.region,
             "timeout": self.credentials.connect_timeout,
         }
+        if kwargs["region"] is None:
+            logger.debug("No region provided, attempting to determine from host.")
+            try:
+                region_value = self.credentials.host.split(".")[2]
+            except IndexError:
+                raise dbt.exceptions.FailedToConnectError(
+                    "No region provided and unable to determine region from host: "
+                    "{}".format(self.credentials.host)
+                )
+
+            kwargs["region"] = region_value
+
+        # Validate the set region
+        if not _is_valid_region(kwargs["region"]):
+            raise dbt.exceptions.FailedToConnectError(
+                "Invalid region provided: {}".format(kwargs["region"])
+            )
+
         if self.credentials.sslmode:
             kwargs["sslmode"] = self.credentials.sslmode
 
@@ -117,7 +160,9 @@ class RedshiftConnectMethodFactory:
             def connect():
                 logger.debug("Connecting to redshift with username/password based auth...")
                 c = redshift_connector.connect(
-                    user=self.credentials.user, password=self.credentials.password, **kwargs
+                    user=self.credentials.user,
+                    password=self.credentials.password,
+                    **kwargs,
                 )
                 if self.credentials.role:
                     c.cursor().execute("set role {}".format(self.credentials.role))


Test patch:
diff --git a/tests/unit/test_redshift_adapter.py b/tests/unit/test_redshift_adapter.py
index 27bcd98f..25f3b3d7 100644
--- a/tests/unit/test_redshift_adapter.py
+++ b/tests/unit/test_redshift_adapter.py
@@ -97,7 +97,9 @@ class TestRedshiftAdapter(unittest.TestCase):
     @mock.patch("redshift_connector.connect", Mock())
     def test_explicit_iam_conn_without_profile(self):
         self.config.credentials = self.config.credentials.replace(
-            method="iam", cluster_id="my_redshift", host="thishostshouldnotexist.test.us-east-1"
+            method="iam",
+            cluster_id="my_redshift",
+            host="thishostshouldnotexist.test.us-east-1",
         )
         connection = self.adapter.acquire_connection("dummy")
         connection.handle
@@ -171,6 +173,92 @@ class TestRedshiftAdapter(unittest.TestCase):
             port=5439,
         )
 
+    @mock.patch("redshift_connector.connect", Mock())
+    @mock.patch("boto3.Session", Mock())
+    def test_explicit_region(self):
+        # Successful test
+        self.config.credentials = self.config.credentials.replace(
+            method="iam",
+            iam_profile="test",
+            host="doesnotexist.1233.redshift-serverless.amazonaws.com",
+            region="us-east-2",
+        )
+        connection = self.adapter.acquire_connection("dummy")
+        connection.handle
+        redshift_connector.connect.assert_called_once_with(
+            iam=True,
+            host="doesnotexist.1233.redshift-serverless.amazonaws.com",
+            database="redshift",
+            cluster_identifier=None,
+            region="us-east-2",
+            auto_create=False,
+            db_groups=[],
+            db_user="root",
+            password="",
+            user="",
+            profile="test",
+            timeout=30,
+            port=5439,
+        )
+
+    @mock.patch("redshift_connector.connect", Mock())
+    @mock.patch("boto3.Session", Mock())
+    def test_explicit_region_failure(self):
+        # Failure test with no region
+        self.config.credentials = self.config.credentials.replace(
+            method="iam",
+            iam_profile="test",
+            host="doesnotexist.1233_no_region",
+            region=None,
+        )
+
+        with self.assertRaises(dbt.exceptions.FailedToConnectError):
+            connection = self.adapter.acquire_connection("dummy")
+            connection.handle
+            redshift_connector.connect.assert_called_once_with(
+                iam=True,
+                host="doesnotexist.1233_no_region",
+                database="redshift",
+                cluster_identifier=None,
+                auto_create=False,
+                db_groups=[],
+                db_user="root",
+                password="",
+                user="",
+                profile="test",
+                timeout=30,
+                port=5439,
+            )
+
+    @mock.patch("redshift_connector.connect", Mock())
+    @mock.patch("boto3.Session", Mock())
+    def test_explicit_invalid_region(self):
+        # Invalid region test
+        self.config.credentials = self.config.credentials.replace(
+            method="iam",
+            iam_profile="test",
+            host="doesnotexist.1233_no_region.us-not-a-region-1",
+            region=None,
+        )
+
+        with self.assertRaises(dbt.exceptions.FailedToConnectError):
+            connection = self.adapter.acquire_connection("dummy")
+            connection.handle
+            redshift_connector.connect.assert_called_once_with(
+                iam=True,
+                host="doesnotexist.1233_no_region",
+                database="redshift",
+                cluster_identifier=None,
+                auto_create=False,
+                db_groups=[],
+                db_user="root",
+                password="",
+                user="",
+                profile="test",
+                timeout=30,
+                port=5439,
+            )
+
     @mock.patch("redshift_connector.connect", Mock())
     @mock.patch("boto3.Session", Mock())
     def test_serverless_iam_failure(self):
@@ -263,7 +351,10 @@ class TestRedshiftAdapter(unittest.TestCase):
 
             self.assertEqual(len(list(self.adapter.cancel_open_connections())), 1)
             add_query.assert_has_calls(
-                [call("select pg_backend_pid()"), call("select pg_terminate_backend(42)")]
+                [
+                    call("select pg_backend_pid()"),
+                    call("select pg_terminate_backend(42)"),
+                ]
             )
 
         master.handle.get_backend_pid.assert_not_called()