gsheets error: [SSL: CERTIFICATE_VERIFY_FAILED]. How to disable SSL verification

[maksvg8]

version - v0.20.4 (2024-12-10)
I'm trying to connect to a public Google spreadsheet. My company installs self-signed certificates and I get an error as a result. How can I disable certificate validation?
https://superset.apache.org/docs/configuration/databases#ssl-access-to-databases

[dosubot[bot]]

Hey @maksvg8! I'm here to help you with that issue. Let's tackle this problem together!

I found a similar closed issue that might be helpful. The suggested solution is to check if the server is not recognizing Google's certificate, possibly due to your company's proxy settings. You might want to look into resolving SSL certificate verification issues, especially if you're running on a local server ^[1].

Additionally, according to the Superset documentation, you can disable SSL certificate validation by using the Extra field in the Edit Databases form. Set the engine_params to include "use_ssl": false in your connection parameters. This should help bypass SSL verification when dealing with self-signed certificates. Make sure to configure any necessary connection strings and parameters specific to your database type as outlined in the Superset documentation.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}

[maksvg8]

Additionally, I tried connecting through Shillelagh. However, I didn't find a way to bypass ssl in the examples

[maksvg8]

Maybe @betodealmeida can help. I have seen many of your commits related to google spreadsheets

[betodealmeida]

The GSheets adapter uses either a requests.Session or a google.auth.transport.requests.AuthorizedSession, depending on the presence of credentials:

https://github.com/betodealmeida/shillelagh/blob/bac8b60227dfb2671a43b5191e2e90b956757e4d/src/shillelagh/adapters/api/gsheets/adapter.py#L202-L206

I think we need to add a new option to the GSheets dialect that allows disabling the SSL verification.

[betodealmeida]

Created a ticket: betodealmeida/shillelagh#499

[betodealmeida]

Fix here: betodealmeida/shillelagh#500

Will release a new version as soon as the PR is green.

[betodealmeida]

@maksvg8 can you try upgrading Shillelagh to 1.3.4? And then set in the database config (engine parameters):

{"session_verify": false}

[maksvg8]

I have tested and it works great. Thank you so much!