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

Issue:
Failure to resolve Snowflake OCSP server creates issues with dbt
### Describe the bug
When dbt is unable to resolve the Snowflake OCSP server for any reason the environment becomes unusable. Queries hang for long periods waiting for the OCSP server resolution step to time out and dbt cannot run (because every attempt to verify the security of the Snowflake environment fails).

### Steps To Reproduce
Follow the steps listed in the [SnowCD user-guide](https://docs.snowflake.com/en/user-guide/snowcd.html) to find the OCSP server information. Block access to the OCSP server so that dbt cannot access it. Try to run any dbt command.

### Expected behavior
When trying to access Snowflake with a blocked OCSP server, dbt should hang on every query execution step waiting for the timeout before failing and moving on to the next query. When dbt can access Snowflake OCSP servers, it should operate normally.

### Screenshots and log output
The output of dbt -d test during the error state:
```
{'eventType': 'RevocationCheckFailure', 'eventSubType': 'OCSPResponseFailedToConnectCacheServer|OCSPResponseFetchException', 'sfcPeerHost': '<account>.privatelink.snowflakecomputing.com', 'certId': '<cert>', 'ocspRequestBase64': '<request>', 'ocspResponderURL': 'http://ocsp.rootca1.amazontrust.com', 'errorMessage': "254003: Could not fetch OCSP Response from server. Considerchecking your whitelists : Exception - HTTPConnectionPool(host='ocsp.<account>.privatelink.snowflakecomputing.com', port=80): Read timed out. (read timeout=10)", 'insecureMode': False, 'failOpen': True, 'cacheEnabled': True, 'cacheHit': False, 'exceptionMessage': "254003: Could not fetch OCSP Response from server. Considerchecking your whitelists : Exception - HTTPConnectionPool(host='ocsp.<account>.privatelink.snowflakecomputing.com', port=80): Read timed out. (read timeout=10)", 'exceptionStackTrace': 'Traceback (most recent call last):\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 445, in _make_request\n    six.raise_from(e, None)\n  File "<string>", line 3, in raise_from\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 440, in _make_request\n    httplib_response = conn.getresponse()\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\http\\client.py", line 1349, in getresponse\n    response.begin()\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\http\\client.py", line 316, in begin\n    version, status, reason = self._read_status()\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\http\\client.py", line 277, in _read_status\n    line = str(self.fp.readline(_MAXLINE + 1), "iso-8859-1")\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\socket.py", line 704, in readinto\n    return self._sock.recv_into(b)\nsocket.timeout: timed out\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\requests\\adapters.py", line 439, in send\n    resp = conn.urlopen(\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 755, in urlopen\n    retries = retries.increment(\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\util\\retry.py", line 532, in increment\n    raise six.reraise(type(error), error, _stacktrace)\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\packages\\six.py", line 770, in reraise\n    raise value\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 699, in urlopen\n    httplib_response = self._make_request(\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 447, in _make_request\n    self._raise_timeout(err=e, url=url, timeout_value=read_timeout)\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\urllib3\\connectionpool.py", line 336, in _raise_timeout\n    raise ReadTimeoutError(\nurllib3.exceptions.ReadTimeoutError: HTTPConnectionPool(host=\'ocsp.<account>.privatelink.snowflakecomputing.com\', port=80): Read timed out. (read timeout=10)\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\snowflake\\connector\\ocsp_snowflake.py", line 1563, in _fetch_ocsp_response\n    response = session.request(\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\requests\\sessions.py", line 542, in request\n    resp = self.send(prep, **send_kwargs)\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\requests\\sessions.py", line 655, in send\n    r = adapter.send(request, **kwargs)\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\requests\\adapters.py", line 529, in send\n    raise ReadTimeout(e, request=request)\nrequests.exceptions.ReadTimeout: HTTPConnectionPool(host=\'ocsp.<account>.privatelink.snowflakecomputing.com\', port=80): Read timed out. (read timeout=10)\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\snowflake\\connector\\ocsp_snowflake.py", line 1222, in validate_by_direct_connection\n    ocsp_response = self._fetch_ocsp_response(\n  File "d:\\users\\<user>\\appdata\\local\\programs\\python\\python39\\lib\\site-packages\\snowflake\\connector\\ocsp_snowflake.py", line 1598, in _fetch_ocsp_response\n    raise RevocationCheckError(\nsnowflake.connector.errors.RevocationCheckError: 254003: Could not fetch OCSP Response from server. Considerchecking your whitelists : Exception - HTTPConnectionPool(host=\'ocsp.<account>.privatelink.snowflakecomputing.com\', port=80): Read timed out. (read timeout=10)\n'}
2021-10-20 19:58:54.823774 (ThreadPoolExecutor-1_1): WARNING!!! Using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificate Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details:
```

### System information
**The output of `dbt --version`:**
```
installed version: 0.21.0
   latest version: 0.21.0

Up to date!

Plugins:
  - bigquery: 0.21.0
  - postgres: 0.21.0
  - redshift: 0.21.0
  - snowflake: 0.21.0
```

**The operating system you're using:**
MacOS 11.6
**The output of `python --version`:**
Python 3.9.6

### Additional context
Add any other context about the problem here.


Pull request description:
Update connections.py
…to dbt.

resolves #31
Resolves issues when the Snowflake OCSP server is not reachable and the dbt user would like to run commands without checking the OCSP server. OCSP failures/inaccessibilitiy are possible due to network routing issues or when corporate network security has exposed Snowflake (especially in privatelink) but not the OCSP server.

### Description
Exposes the insecure_mode option in the Snowflake python connector to dbt via the profile.yml file.

### Checklist

- [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
- [ ] This PR includes tests, or tests are not required/relevant for this PR
- [X ] I have updated the `CHANGELOG.md` and added information about my change to the "dbt-snowflake next" section.

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8385d52..f85cd59 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,9 +1,14 @@
 ## dbt-snowflake 1.0.0 (Release TBD)
 
-## dbt-snowflake 1.0.0rc2 (TBD)
-
 ### Fixes
-- Apply query tags for Seed and Snapshot materialisations ([#48](https://github.com/dbt-labs/dbt-snowflake/issues/48))
+- Apply query tags for Seed and Snapshot materialisations ([#20](https://github.com/dbt-labs/dbt-snowflake/issues/20), [#48](https://github.com/dbt-labs/dbt-snowflake/issues/48))
+
+### Under the hood
+- Resolves an issue caused when the Snowflake OCSP server is not accessible, by exposing the `insecure_mode` boolean avalable in the Snowflake python connector ([#31](https://github.com/dbt-labs/dbt-snowflake/issues/31), [#49](https://github.com/dbt-labs/dbt-snowflake/pull/49))
+
+### Contributors
+- [@anthu](https://github.com/anthu) ([#48](https://github.com/dbt-labs/dbt-snowflake/pull/48))
+- [@JoshuaHuntley](https://github.com/JoshuaHuntley) ([#49](https://github.com/dbt-labs/dbt-snowflake/pull/49))
 
 ## dbt-snowflake 1.0.0rc1 (November 10, 2021)
 
diff --git a/dbt/adapters/snowflake/connections.py b/dbt/adapters/snowflake/connections.py
index dfa5e17..869f5f9 100644
--- a/dbt/adapters/snowflake/connections.py
+++ b/dbt/adapters/snowflake/connections.py
@@ -52,6 +52,7 @@ class SnowflakeCredentials(Credentials):
     connect_timeout: int = 10
     retry_on_database_errors: bool = False
     retry_all: bool = False
+    insecure_mode: Optional[bool] = False
 
     def __post_init__(self):
         if (
@@ -252,6 +253,7 @@ class SnowflakeConnectionManager(SQLConnectionManager):
                     autocommit=True,
                     client_session_keep_alive=creds.client_session_keep_alive,
                     application='dbt',
+                    insecure_mode=creds.insecure_mode,
                     **creds.auth_args()
                 )
 


Test patch:
diff --git a/tests/unit/test_snowflake_adapter.py b/tests/unit/test_snowflake_adapter.py
index 87d1e12..f0791c3 100644
--- a/tests/unit/test_snowflake_adapter.py
+++ b/tests/unit/test_snowflake_adapter.py
@@ -261,7 +261,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 account='test_account', autocommit=True,
                 client_session_keep_alive=False, database='test_database',
                 role=None, schema='public', user='test_user',
-                warehouse='test_warehouse', private_key=None, application='dbt')
+                warehouse='test_warehouse', private_key=None, application='dbt', insecure_mode=False)
         ])
 
     def test_client_session_keep_alive_true(self):
@@ -277,7 +277,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 account='test_account', autocommit=True,
                 client_session_keep_alive=True, database='test_database',
                 role=None, schema='public', user='test_user',
-                warehouse='test_warehouse', private_key=None, application='dbt')
+                warehouse='test_warehouse', private_key=None, application='dbt', insecure_mode=False)
         ])
 
     def test_user_pass_authentication(self):
@@ -295,7 +295,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 client_session_keep_alive=False, database='test_database',
                 password='test_password', role=None, schema='public',
                 user='test_user', warehouse='test_warehouse', private_key=None,
-                application='dbt')
+                application='dbt', insecure_mode=False)
         ])
 
     def test_authenticator_user_pass_authentication(self):
@@ -315,7 +315,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 password='test_password', role=None, schema='public',
                 user='test_user', warehouse='test_warehouse',
                 authenticator='test_sso_url', private_key=None,
-                application='dbt', client_store_temporary_credential=True)
+                application='dbt', client_store_temporary_credential=True, insecure_mode=False)
         ])
 
     def test_authenticator_externalbrowser_authentication(self):
@@ -333,7 +333,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 client_session_keep_alive=False, database='test_database',
                 role=None, schema='public', user='test_user',
                 warehouse='test_warehouse', authenticator='externalbrowser',
-                private_key=None, application='dbt', client_store_temporary_credential=True)
+                private_key=None, application='dbt', client_store_temporary_credential=True, insecure_mode=False)
         ])
 
     def test_authenticator_oauth_authentication(self):
@@ -352,7 +352,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 client_session_keep_alive=False, database='test_database',
                 role=None, schema='public', user='test_user',
                 warehouse='test_warehouse', authenticator='oauth', token='my-oauth-token',
-                private_key=None, application='dbt', client_store_temporary_credential=True)
+                private_key=None, application='dbt', client_store_temporary_credential=True, insecure_mode=False)
         ])
 
     @mock.patch('dbt.adapters.snowflake.SnowflakeCredentials._get_private_key', return_value='test_key')
@@ -373,7 +373,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 client_session_keep_alive=False, database='test_database',
                 role=None, schema='public', user='test_user',
                 warehouse='test_warehouse', private_key='test_key',
-                application='dbt')
+                application='dbt', insecure_mode=False)
         ])
 
     @mock.patch('dbt.adapters.snowflake.SnowflakeCredentials._get_private_key', return_value='test_key')
@@ -394,7 +394,7 @@ class TestSnowflakeAdapter(unittest.TestCase):
                 client_session_keep_alive=False, database='test_database',
                 role=None, schema='public', user='test_user',
                 warehouse='test_warehouse', private_key='test_key',
-                application='dbt')
+                application='dbt', insecure_mode=False)
         ])