[Perf]: Every Snowflake connect from JDBC discovery takes 5-30s (metadata inference)

[tadmas2020]

Performance Issue

Description

During a Snowflake scan, each connect from the discovery Lambda (sources-db-connector) stalls 5-30 seconds per responder. OCSP (certificate revocation checking) cannot be reached over HTTP/80, so the driver waits until it fails open.

The connect still succeeds, so this never surfaces as an error. It does contribute to hitting the Lambda timeout (900s) on scans with many tables.

Current Behavior

snowflake-connector-python checks revocation on every connect. With no TCP/80 egress on the Lambda SG, each responder is waited out until the timeout, a WARNING is logged, and the connect then proceeds (fail-open is the driver default).

Expected Behavior

OCSP is reachable, as it already is via the Glue Connection path (connectorSecurityGroup), and no revocation-check stall occurs.

Steps to Reproduce

• Register a Snowflake data source
• Run a scan
• Inspect the sources-db-connector CloudWatch Logs for OCSP-related WARNINGs and the elapsed time before the connect completes
• Compare against a connect over connectorSecurityGroup (Glue Connection)

Metrics

The existing code comment records a measurement.

measured ValidationLatency of 184s on a Snowflake scan, against a 900s Lambda timeout

5-30 seconds per responder.

Possible Root Cause

COA reaches Snowflake over two paths, and the TCP/80 egress for OCSP exists on only one of them.

SG	Path	OCSP 80
connectorSecurityGroup	Glue Connection / Athena connector	present
lambdaSecurityGroup	discovery Lambda connecting directly over JDBC via the dialects	absent

The Lambda SG explicitly opens 443 for Snowflake and each DB port, and its comment states the direct-connect design outright. Only the OCSP port 80 was missed.

infra/lib/stacks/foundation/network-stack.ts — the connector SG side has the rule, with both the rationale and the control knobs.

// OCSP certificate-revocation checks. OCSP is an HTTP protocol and always
// uses port 80 — there is no HTTPS variant to fold into the 443 rule below.
// snowflake-connector-python checks revocation on every connect; without
// this rule each attempt burns ~5-30s per responder before failing open
// (measured ValidationLatency of 184s on a Snowflake scan, against a 900s
// Lambda timeout). ...
if (this.ctxString("connector_ocsp_egress") !== "false") {
  for (const peer of connectorEgressPeers) {
    this.connectorSecurityGroup.addEgressRule(
      peer,
      ec2.Port.tcp(80),
      `OCSP certificate revocation (HTTP-only protocol) to ${egressScope}`,
    );
  }
}

The Lambda SG only has 443 and the DB ports.

// The discovery Lambda (sources-db-connector) connects DIRECTLY to source
// databases via its driver dialects (see database/connectors/dialects.py),
// not through Glue/Athena — so it needs egress to the supported DB ports.
// Snowflake (443) is already covered above. ...
for (const port of [5432, 3306, 1433, 5439, 1521]) {
  this.lambdaSecurityGroup.addEgressRule(
    ec2.Peer.anyIpv4(),
    ec2.Port.tcp(port),
    `Source database ${port} (direct JDBC discovery)`,
  );
}

Two changes are needed.

1. Add the OCSP TCP/80 egress to lambdaSecurityGroup as well, under the same controls as connectorSecurityGroup (connector_ocsp_egress to toggle it, connector_egress_cidrs to narrow the destinations). Opening 80 unconditionally to any destination would not match the existing precedent in this file.

// Apply the same OCSP rule to the Lambda SG. Destination scope and the on/off
// switch are governed by the same knobs as the connector SG
// (connector_egress_cidrs / connector_ocsp_egress).
if (this.ctxString("connector_ocsp_egress") !== "false") {
  for (const peer of connectorEgressPeers) {
    this.lambdaSecurityGroup.addEgressRule(
      peer,
      ec2.Port.tcp(80),
      `OCSP certificate revocation (HTTP-only protocol) to ${egressScope}`,
    );
  }
}

2. Add a test covering the OCSP rule on the Lambda SG. Today infra/test/foundation/network-stack.test.ts only asserts the DB-port egress for the Lambda SG (Lambda SG has egress to source-DB ports (direct JDBC discovery)), while OCSP 80 is asserted for the connector SG only (Connector SG allows port 80 for OCSP by default / ... drops the OCSP rule when connector_ocsp_egress=false). The coverage is asymmetric.

Context

• Commit ID: Observed on 42b5c6b (tag v0.2.2). network-stack.ts is unchanged on e265573 (tag v0.3.1 = main).
• Environment: ap-northeast-1 deployment
• Opening 80 is about removing latency, not about strengthening revocation checking. Snowflake's own guidance is to run fail-open (the driver default) and monitor the WARNING logs; fail-close would make the responder a single point of failure.
• OCSP responder IPs vary, so pinning destinations by CIDR is not practical. The actual hosts can be listed with SYSTEM$ALLOWLIST(), but they are FQDNs and security groups do not accept FQDNs. This is the same reason the connector SG side defaults to 0.0.0.0/0.

Additional Information / References

• Snowflake OCSP documentation
• Rethinking Certificate Revocation at Snowflake (2026-05) — Snowflake is moving from OCSP to CRL and plans to disable driver-side revocation checking by default, which would eventually make this rule unnecessary.
• Adjacent: [Bug]: Serve runtime security group blocks egress to publicly-accessible JDBC data sources on standard DB ports #187 Serve runtime security group blocks egress to publicly-accessible JDBC data sources on standard DB ports (OPEN) — a different SG and different ports, but the same SG design area. Its Risks section argues against opening DB ports broadly to 0.0.0.0/0 and for limiting to known destinations; this proposal stays under the existing connector_egress_cidrs control for consistency with that view.
• Likely one contributor to the Lambda timeout on scans with many tables (see [Bug]: Discovery hangs for the full Lambda timeout on large/free-text columns — unbounded COUNT(DISTINCT) enum-cardinality probe #131), though it has not been isolated as the primary cause.

[noah-paige]

A fix for this has landed on main: discovery's OCSP egress gap is closed.

The approach differs slightly from what's proposed above — rather than adding the port-80 rule directly to the shared lambdaSecurityGroup, the fix introduces a new, dedicated security group (discoveryOcspSecurityGroup) scoped to just the sources-db-connector Lambda, and attaches it alongside the existing lambdaSecurityGroup. The OCSP rule itself now lives in a shared helper used by both this new SG and the existing connectorSecurityGroup, governed by the same connector_ocsp_egress / connector_egress_cidrs context knobs described in this issue — so the two paths can't drift apart, and every other Lambda on the platform still has no port-80 egress by default.

Test coverage was extended to assert the new SG gets the OCSP rule under both the default-on and connector_ocsp_egress=false cases, and that the shared Lambda SG does not get port 80 — closing the asymmetric-coverage gap called out above.