[Feature] Support ocsp_fail_open setting in dbt-snowflake

[jcrobak]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

the snowflake-connector-python driver has a setting, ocsp_fail_open, that controls the behavior when the ocsp server is unavailable. This setting is not currently exposed in dbt-snowflake.

Ref:
https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect#choosing-fail-open-or-fail-close-mode

Describe alternatives you've considered

Many snowflake settings can be controlled with environment variables. The codebase says this is unsupported: https://github.com/snowflakedb/snowflake-connector-python/blob/53592ed96b240a8a7426f73229c670f57e14312f/src/snowflake/connector/ocsp_snowflake.py#L1051-L1056

Further, setting this via environment variable is difficult when using astronomer-cosmos during an airflow run.

Who will this benefit?

There are security/availability trade-offs for this setting, so it's useful to be able to decide which trade-offs to accept. See: https://docs.snowflake.com/en/user-guide/ocsp#fail-open

Are you interested in contributing this feature?

yes, I will submit a PR shortly

Anything else?

No response

[amychen1776]

Hello @jcrobak ! Would you be able to break down a little bit more about what are the use cases that you will want this feature for? I assume this is to implement more security to ensure that fails closed if the cert is not valid but want to make sure.

Also why is this a difficult environment variable to set in cosmos?

[jcrobak]

Hi @amychen1776. Because the setting is not exposed, right now you are always in OCSP fail open mode. This is the default because it provides availability over security. Some users, though, would want the extra security and be OK with jobs failing in the case where the OCSP service isn't available. In particular, the OCSP response is used to revoke a certificate e.g. in the case where the private key could have been compromised.

regarding the environment variables via cosmos - when running in airflow, you have to set the environment variable inside of the worker, but that's typically controlled at a different level / team than the person authoring the dag. That means you can only turn it on/off globally and not per-DAG/task.

Hope that makes sense - happy to elaborate more!