`1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess` and OCSP warning due to missing OCSP Responder in Google cert

archiewood commented 1 month ago

We are getting warning messages about OCSP Responses using the latest driver version. These are very noisy for our clients.

WARNING!!! using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificated Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess

This happens in particular with larger queries, see my repro

Info

What version of NodeJS driver are you using?

"snowflake-sdk": "^1.14.0"

What operating system and processor architecture are you using?

Mac Silicon

What version of NodeJS are you using? (node --version and npm --version)

node --version
v20.15.0
npm --version
10.7.0

What are the component versions in the environment (npm list)?

npm list
repro-snowflake-ocsp@1.0.0 /Users/archie/Projects/repro-snowflake-ocsp
└── snowflake-sdk@1.14.0

6.Server version:* E.g. 1.90.1

'CURRENT_VERSION()': '8.38.2'`

What did you do?

See minimal repro in this repository: https://github.com/archiewood/repro-snowflake-ocsp

const snowflake = require('snowflake-sdk');

// Connection configuration
const config = {
    account: '<your_account>',
    username: '<your_username>',
    password: '<your_password>',
    warehouse: '<your_warehouse>',
    database: '<your_database>',
    schema: '<your_schema>'
};

// Create connection
const connection = snowflake.createConnection(config);

// Connect to Snowflake
connection.connect((err, conn) => {
    if (err) {
        console.error('Unable to connect: ' + err.message);
    } else {
        console.log('Successfully connected to Snowflake.');
        // Execute a sample query
        const query = `
        SELECT CURRENT_VERSION();
        WITH RECURSIVE generate_series(n) AS (
            SELECT 1
            UNION ALL
            SELECT n + 1
            FROM generate_series
            WHERE n < 3000000
        )
        SELECT 
            n AS id,
            RANDSTR(10, RANDOM()) AS random_string,
            UNIFORM(1, 100, RANDOM()) AS random_number,
            DATEADD(day, UNIFORM(0, 3650, RANDOM()), CURRENT_DATE()) AS random_date
        FROM generate_series
        `;

        connection.execute({
            sqlText: query,
            complete: (err, stmt, rows) => {
                if (err) {
                    console.error('Failed to execute statement due to the following error: ' + err.message);
                } else {
                    console.log('Successfully executed statement: ' + stmt.getSqlText());
                    console.log(rows);
                }
                // Close the connection
                connection.destroy((err) => {
                    if (err) {
                        console.error('Unable to disconnect: ' + err.message);
                    } else {
                        console.log('Disconnected connection');
                    }
                });
            }
        });
    }
});

What did you expect to see?

No warning messages are printed

Can you set logging to DEBUG and collect the logs?

{"level":"INFO","message":"[6:58:35.853 PM]: Connecting to GLOBAL Snowflake domain"}
{"level":"INFO","message":"[6:58:35.868 PM]: Trying to initialize Easy Logging"}
{"level":"DEBUG","message":"[6:58:35.868 PM]: Retrieving client config"}
{"level":"TRACE","message":"[6:58:35.868 PM]: findConfig() called with param: undefined"}
{"level":"DEBUG","message":"[6:58:35.869 PM]: Searching for config in default directories: [object Object],[object Object]"}
{"level":"DEBUG","message":"[6:58:35.871 PM]: Unable to find config in any default directory."}
{"level":"INFO","message":"[6:58:35.871 PM]: No client config detected."}
{"level":"INFO","message":"[6:58:35.871 PM]: No config file path found. Client config will not be used."}
{"level":"INFO","message":"[6:58:35.871 PM]: Easy Logging is disabled as no config has been found"}
{"level":"DEBUG","message":"[6:58:35.873 PM]: 300"}
{"level":"DEBUG","message":"[6:58:35.874 PM]: Contacting SF: /session/v1/login-request?requestId=fe1ce.., (0/7)"}
{"level":"TRACE","message":"[6:58:35.875 PM]: Create and add to cache new agent https://myorg-myaccount.snowflakecomputing.com-keepAlive"}
{"level":"DEBUG","message":"[6:58:35.875 PM]: Proxy settings used in requests: none."}
{"level":"TRACE","message":"[6:58:35.875 PM]: CALL POST with timeout 90000: https://myorg-myaccount.snowflakecomputing.com/session/v1/login-request?requestId=fe1c.."}
{"level":"TRACE","message":"[6:58:36.187 PM]: socket reused = false"}
{"level":"DEBUG","message":"[6:58:36.195 PM]: Reading OCSP cache file. /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"TRACE","message":"[6:58:36.251 PM]: Returning OCSP status for certificate 01972683EA86BBB03D9E57BFBD136A4A from cache"}
{"level":"TRACE","message":"[6:58:36.253 PM]: Returning OCSP status for certificate 0CF5BD062B5602F47AB8502C23CCF066 from cache"}
{"level":"TRACE","message":"[6:58:36.254 PM]: OCSP validation succeeded for myorg-myaccount.snowflakecomputing.com"}
{"level":"DEBUG","message":"[6:58:36.557 PM]: New session with id 188312849635054 initialized"}
{"level":"DEBUG","message":"[6:58:36.558 PM]: Creating new QueryContextCache with capacity 5 for session 188312849635054"}
Successfully connected to Snowflake.
{"level":"DEBUG","message":"[6:58:36.558 PM]: --createStatementPreExec"}
{"level":"DEBUG","message":"[6:58:36.558 PM]: numBinds = 0"}
{"level":"DEBUG","message":"[6:58:36.558 PM]: threshold = 65280"}
{"level":"DEBUG","message":"[6:58:36.558 PM]: RowStatementPreExec"}
{"level":"DEBUG","message":"[6:58:36.559 PM]: context.bindStage=undefined"}
{"level":"TRACE","message":"[6:58:36.559 PM]: Get agent with id: https://myorg-myaccount.snowflakecomputing.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:58:36.559 PM]: CALL POST with timeout 90000: https://myorg-myaccount.snowflakecomputing.com/queries/v1/query-request?requestId=8c781939-fea8-49e9-bba5-7043acb7cbd4"}
{"level":"TRACE","message":"[6:59:09.258 PM]: Mapping columns in resultset (total: 4)"}
{"level":"TRACE","message":"[6:59:09.260 PM]: Finished mapping columns."}
{"level":"TRACE","message":"[6:59:09.260 PM]: Downloading 11 chunks"}
{"level":"DEBUG","message":"[6:59:09.261 PM]: QCC session 188312849635054 - deserializeQueryContext() called: data from server: {\"entries\":[{\"id\":0,\"timestamp\":1728406749162101,\"priority\":0}]}"}
{"level":"DEBUG","message":"[6:59:09.261 PM]: QCC session 188312849635054 - deserializeQueryContextElement `context` field is empty"}
{"level":"DEBUG","message":"[6:59:09.261 PM]: QCC session 188312849635054 - Merging QCE: {\"id\":0,\"timestamp\":1728406749162101,\"priority\":0,\"context\":null}"}
{"level":"TRACE","message":"[6:59:09.261 PM]: QCC session 188312849635054 - New element"}
{"level":"DEBUG","message":"[6:59:09.261 PM]: QCC session 188312849635054 - Adding new element to the cache: {\"id\":0,\"timestamp\":1728406749162101,\"priority\":0,\"context\":null}"}
{"level":"TRACE","message":"[6:59:09.261 PM]: QCC session 188312849635054 - Added QCE: {\"id\":0,\"timestamp\":1728406749162101,\"priority\":0,\"context\":null}"}
{"level":"TRACE","message":"[6:59:09.261 PM]: QCC session 188312849635054 - checkCacheCapacity() called. treeSet size 1, cache capacity 5"}
{"level":"TRACE","message":"[6:59:09.261 PM]: QCC session 188312849635054 - checkCacheCapacity() returns. treeSet size 1, cache capacity 5"}
{"level":"DEBUG","message":"[6:59:09.261 PM]: QCC session 188312849635054 - Cache Entry: id: 0 timestamp: 1728406749162101 priority: 0"}
{"level":"TRACE","message":"[6:59:09.262 PM]: Create and add to cache new agent https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive"}
{"level":"DEBUG","message":"[6:59:09.262 PM]: Proxy settings used in requests: none."}
{"level":"TRACE","message":"[6:59:09.262 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:09.262 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:09.262 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:09.315 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:09.315 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:09.379 PM]: socket reused = false"}
{"level":"TRACE","message":"[6:59:09.385 PM]: Returning OCSP status for certificate 6E7D5479477209BA10746395F488AD7E from cache"}
{"level":"TRACE","message":"[6:59:09.390 PM]: Returning OCSP status for certificate 77BD0D6CDB36F91AEA210FC4F058D30D from cache"}
{"level":"TRACE","message":"[6:59:09.392 PM]: Contact OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"TRACE","message":"[6:59:09.394 PM]: socket reused = false"}
{"level":"TRACE","message":"[6:59:09.401 PM]: Returning OCSP status for certificate 6E7D5479477209BA10746395F488AD7E from cache"}
{"level":"TRACE","message":"[6:59:09.413 PM]: Returning OCSP status for certificate 77BD0D6CDB36F91AEA210FC4F058D30D from cache"}
{"level":"TRACE","message":"[6:59:09.417 PM]: socket reused = false"}
{"level":"TRACE","message":"[6:59:09.426 PM]: Returning OCSP status for certificate 6E7D5479477209BA10746395F488AD7E from cache"}
{"level":"TRACE","message":"[6:59:09.436 PM]: Returning OCSP status for certificate 77BD0D6CDB36F91AEA210FC4F058D30D from cache"}
{"level":"DEBUG","message":"[6:59:09.563 PM]: Finish OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.667 PM]: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess\n    at Object.getAuthorityInfo (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/@techteamer/ocsp/lib/ocsp/utils.js:198:29)\n    at ocspRequestSend (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:215:18)\n    at check (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:221:3)\n    at getOcspResonseAndVerify (/Users/archie/Projects/repro-snowflake-ocsp/"}
{"level":"WARN","message":"[6:59:09.667 PM]: WARNING!!! using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificated Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess"}
{"level":"DEBUG","message":"[6:59:09.667 PM]: /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.676 PM]: Writing OCSP cache file. /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"TRACE","message":"[6:59:09.679 PM]: OCSP validation succeeded for gcpuseast4-838400-stage.storage.googleapis.com"}
{"level":"TRACE","message":"[6:59:09.680 PM]: Contact OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.760 PM]: Finish OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.828 PM]: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess\n    at Object.getAuthorityInfo (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/@techteamer/ocsp/lib/ocsp/utils.js:198:29)\n    at ocspRequestSend (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:215:18)\n    at check (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:221:3)\n    at getOcspResonseAndVerify (/Users/archie/Projects/repro-snowflake-ocsp/"}
{"level":"WARN","message":"[6:59:09.828 PM]: WARNING!!! using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificated Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess"}
{"level":"DEBUG","message":"[6:59:09.828 PM]: /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.834 PM]: Writing OCSP cache file. /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"TRACE","message":"[6:59:09.836 PM]: OCSP validation succeeded for gcpuseast4-838400-stage.storage.googleapis.com"}
{"level":"TRACE","message":"[6:59:09.837 PM]: Contact OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.896 PM]: Finish OCSP Cache Server: http://ocsp.snowflakecomputing.com/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.960 PM]: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess\n    at Object.getAuthorityInfo (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/@techteamer/ocsp/lib/ocsp/utils.js:198:29)\n    at ocspRequestSend (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:215:18)\n    at check (/Users/archie/Projects/repro-snowflake-ocsp/node_modules/snowflake-sdk/lib/agent/check.js:221:3)\n    at getOcspResonseAndVerify (/Users/archie/Projects/repro-snowflake-ocsp/"}
{"level":"WARN","message":"[6:59:09.961 PM]: WARNING!!! using fail-open to connect. Driver is connecting to an HTTPS endpoint without OCSP based Certificated Revocation checking as it could not obtain a valid OCSP Response to use from the CA OCSP responder. Details: Error: 1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess"}
{"level":"DEBUG","message":"[6:59:09.961 PM]: /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"DEBUG","message":"[6:59:09.964 PM]: Writing OCSP cache file. /Users/archie/Library/Caches/Snowflake/ocsp_response_cache.json"}
{"level":"TRACE","message":"[6:59:09.965 PM]: OCSP validation succeeded for gcpuseast4-838400-stage.storage.googleapis.com"}
{"level":"TRACE","message":"[6:59:10.555 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:10 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:37 GMT\",\"etag\":\"\\\"-CMC/seOg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406717489088\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"125099\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=PJztew==, md5=Mzx9xQs"}
{"level":"TRACE","message":"[6:59:10.585 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:10.585 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:10.782 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:10 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:37 GMT\",\"etag\":\"\\\"-CLD/tuOg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406717579184\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"208949\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=b8v10A==, md5=iNMvfHo"}
{"level":"TRACE","message":"[6:59:10.852 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:10.852 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:11.026 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:10 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:37 GMT\",\"etag\":\"\\\"-CLi7yOOg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406717865400\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"418293\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=2gnL5w==, md5=Oetm6YY"}
{"level":"TRACE","message":"[6:59:11.399 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:11.401 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:11.580 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:10 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:38 GMT\",\"etag\":\"\\\"-CM/Z5+Og/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406718377167\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"837127\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=p3Ptcw==, md5=2lwZoDy"}
{"level":"TRACE","message":"[6:59:11.875 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:11.876 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:12.043 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:11 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:39 GMT\",\"etag\":\"\\\"-CMuGp+Sg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406719415115\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"1668627\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=uQ3Tfg==, md5=U3DtNo"}
{"level":"TRACE","message":"[6:59:13.227 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:13.227 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:14.006 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:11 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:41 GMT\",\"etag\":\"\\\"-CKrwp+Wg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406721525802\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"3296463\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=FbF7/Q==, md5=0U1NXt"}
{"level":"TRACE","message":"[6:59:17.962 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:17.967 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}
{"level":"TRACE","message":"[6:59:18.591 PM]: Response headers are: {\"cache-control\":\"no-cache, no-store, max-age=0, must-revalidate\",\"pragma\":\"no-cache\",\"expires\":\"Mon, 01 Jan 1990 00:00:00 GMT\",\"date\":\"Tue, 08 Oct 2024 16:59:12 GMT\",\"last-modified\":\"Tue, 08 Oct 2024 16:58:45 GMT\",\"etag\":\"\\\"-COWPnueg/4gDEAE=\\\"\",\"x-goog-generation\":\"1728406725560293\",\"x-goog-metageneration\":\"1\",\"x-goog-stored-content-encoding\":\"GZIP\",\"x-goog-stored-content-length\":\"6588400\",\"content-type\":\"application/octet-stream\",\"x-goog-hash\":\"crc32c=5twRYA==, md5=j0xXMs"}
{"level":"TRACE","message":"[6:59:32.401 PM]: Get agent with id: https://gcpuseast4-838400-stage.storage.googleapis.com-keepAlive from cache"}
{"level":"TRACE","message":"[6:59:32.402 PM]: CALL GET with timeout 90000: https://gcpuseast4-838400-stage.storage.googleapis.com/results/<results-path>"}

What is your Snowflake account identifier, if any? (Optional)

myorg-myaccount

sfc-gh-dszmolka commented 1 month ago

(edit: edited your original submission and removed account-related and other possibly sensitive information from the log you provided. Which, by the way, (the logs and all the details + repro) is highly appreciated! )

hi and thank you for raising this issue. oid 1.3.6.1.5.5.7.48.1 in certificate Authority Information Access is the oid for OCSP entry, which per this error message, is missing.

So let's verify.

$ export hostname="gcpuseast4-838400-stage.storage.googleapis.com"
$ echo | openssl s_client -showcerts -connect "$hostname":443 -servername "$hostname" 2>/dev/null | awk '/BEGIN/,/END/{ if(/BEGIN/){a++}; out="cert"a".pem"; print >out}'; for cert in cert*.pem; do echo "--> $cert"; openssl x509 -noout -subject -issuer -startdate -enddate -ocsp_uri -in $cert; echo; done
--> cert1.pem
subject=CN = *.storage.googleapis.com
issuer=C = US, O = Google Trust Services, CN = WR2
notBefore=Sep 16 09:29:09 2024 GMT
notAfter=Dec  9 09:29:08 2024 GMT
http://o.pki.goog/wr2

--> cert2.pem
subject=C = US, O = Google Trust Services, CN = WR2
issuer=C = US, O = Google Trust Services LLC, CN = GTS Root R1
notBefore=Dec 13 09:00:00 2023 GMT
notAfter=Feb 20 14:00:00 2029 GMT

--> cert3.pem
subject=C = US, O = Google Trust Services LLC, CN = GTS Root R1
issuer=C = BE, O = GlobalSign nv-sa, OU = Root CA, CN = GlobalSign Root CA
notBefore=Jun 19 00:00:42 2020 GMT
notAfter=Jan 28 00:00:42 2028 GMT
http://ocsp.pki.goog/gsr1

Indeed, the first intermediary CA cert (saved in cert2.pem) does not have a OCSP URI. We already contacted Google a couple of weeks ago, and long story short, they don't want to fix it.

On the long term, we'll adapt all our drivers to how the world is changing and more and more CA's seem to move away from OCSP. On the short term, you can

ignore this warning, as it is a warning and does not influence the actual ability to perform the query or retrieving the query result chunks. With the (default) OCSP FAIL_OPEN behaviour, the connection commences even without OCSP validation.
as you already did, disable certificate validation with insecureConnect: true (which we, as Snowflake, generally don't really recommend to do as a permanent 'solution' but here the cert chain cannot fully be validated anyways, so...)

On the short term, we're already considering some 'workaround' to adapt to this Google decision, like reword the warning, or stop emitting it by default.

So right now, it's expected to behave like this due to the cloud service provider changing their approach.

archiewood commented 1 month ago

Thank you for your response, this is helpful!

If the account identifier is considered private information, it should probably be removed from the bug issue template!

sfc-gh-dszmolka commented 1 month ago

working on it ;) https://github.com/snowflakedb/snowflake-connector-nodejs/pull/927

snowflakedb / snowflake-connector-nodejs

`1.3.6.1.5.5.7.48.1 not found in AuthorityInfoAccess` and OCSP warning due to missing OCSP Responder in Google cert #932

Info