Failure to resolve Snowflake OCSP server creates issues with dbt

[JoshuaHuntley]

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 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.

[jtcohen6]

@JoshuaHuntley Thanks for opening the issue, and sorry for the delay getting back to you. I'll be honest, this isn't something I understand very well, but it sounds like the fix required is just adding the insecure_mode property and passing it along to the Snowflake connector. I'll comment on the PR, and we can try to get this merged in.