Categories:

System Functions (System Information)

SYSTEM$WHITELIST

Returns hostnames and port numbers to add to your firewall’s whitelist so that you can access Snowflake from behind your firewall. The output of this function can then be passed into the SnowCD.

Typically, Snowflake customers use a firewall to prevent unauthorized access. By default, your firewall might block access to Snowflake. To update your firewall’s whitelist, you need to know the hostnames and port numbers for your Snowflake account, stages, and other other hosts used by Snowflake.

For more details about whitelisting for the Snowflake clients you use, see Hostname Whitelisting.

Syntax

SYSTEM$WHITELIST()

Arguments

None.

Returns

The data type of the returned value is VARIANT. The value is an array of JSON structures. Each JSON structure contains three key/value pairs:

type

Currently, there are 5 possible types:

  • SNOWFLAKE_DEPLOYMENT: Hostname and port number information for your Snowflake account.

  • STAGE: Location (Amazon S3, Google Cloud Storage, or Microsoft Azure) where files that the Snowflake client can read or write are stored.

  • SNOWSQL_REPO: Endpoint accessed by SnowSQL to perform automatic downloads/upgrades.

  • OUT_OF_BAND_TELEMETRY: The hosts to which drivers report metrics and out-of-band incidents such as OCSP issues.

  • OCSP_CACHE: Snowflake-provided alternative source of OCSP certificate information in case the primary OCSP responder cannot be reached. Most of the latest versions of the Snowflake clients access the OCSP cache rather than connecting directly to the OCSP responder.

  • OCSP_RESPONDER: Hostname to contact to verify that the OCSP SSL certificate has not been revoked.

host

Specifies the full hostname for type (e.g. "xy12345.east-us-2.azure.snowflakecomputing.com", "ocsp.snowflakecomputing.com").

port

Specifies the port number for type (e.g. "443", "80").

Usage Notes

  • The function must be called as a system function.

  • The output may include multiple entries for certain types (STAGE, OCSP_RESPONDER, etc.).

Examples

To call the function:

SELECT SYSTEM$WHITELIST();

Sample output:

[
  {"type":"SNOWFLAKE_DEPLOYMENT", "host":"xy12345.snowflakecomputing.com",                 "port":443},
  {"type":"STAGE",                "host":"sfc-customer-stage.s3.us-west-2.amazonaws.com",  "port":443},
  ...
  {"type":"SNOWSQL_REPO",         "host":"sfc-repo.snowflakecomputing.com",                "port":443},
  ...
  {"type":"OCSP_CACHE",           "host":"ocsp.snowflakecomputing.com",                    "port":80}
  {"type":"OCSP_RESPONDER",       "host":"o.ss2.us",                                       "port":80},
  ...
]

In this sample output, note the following:

  • For readability, whitespace and newline characters have been added. In addition, some entries have been omitted.

  • The region ID (us-west-2) in some of the hostnames indicates the account is in the US West region; however, the region ID is not utilized in the hostname for SNOWFLAKE_DEPLOYMENT.

To extract the information into tabular output rather than JSON, use the FLATTEN function in conjunction with the PARSE_JSON function:

SELECT t.VALUE:type::VARCHAR as type,
       t.VALUE:host::VARCHAR as host,
       t.VALUE:port as port
FROM TABLE(FLATTEN(input => PARSE_JSON(SYSTEM$WHITELIST()))) AS t;

Sample output:

+-----------------------+---------------------------------------------------+------+
| TYPE                  | HOST                                              | PORT |
|-----------------------+---------------------------------------------------+------|
| SNOWFLAKE_DEPLOYMENT  | xy12345.snowflakecomputing.com                    | 443  |
| STAGE                 | sfc-customer-stage.s3.us-west-2.amazonaws.com     | 443  |
  ...
| SNOWSQL_REPO          | sfc-repo.snowflakecomputing.com                   | 443  |
  ...
| OCSP_CACHE            | ocsp.snowflakecomputing.com                       | 80   |
| OCSP_RESPONDER        | ocsp.sca1b.amazontrust.com                        | 80   |
  ...
+-----------------------+---------------------------------------------------+------+