Categories:
System Functions (System Information)

SYSTEM$WHITELIST

This function returns host names and port numbers to add to your firewall’s whitelist so that you can access Snowflake from behind your firewall.

Almost all Snowflake users have a firewall to protect their computers from unauthorized access. By default, your firewall might block your access to Snowflake servers. To update your firewall’s whitelist, you need to know the host names and port numbers of the Snowflake servers, stages, etc. This SYSTEM$WHITELIST() function gives you the hosts and port numbers to add to your firewall’s whitelist.

For more information about whitelisting, see Client Considerations.

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, host, and port.

  • Type: Currently, there are 5 possible types:
    • SNOWFLAKE_DEPLOYMENT indicates that the host name and port number are for the host used to log in to Snowflake.
    • STAGE is the storage location (i.e. AWS S3, Google Cloud Storage, or Microsoft Azure) that stores the file that the driver or connector can read or write.
    • OCSP_RESPONDER is the ocsp responder to contact to verify that the SSL certificate has not been revoked.
    • OCSP_CACHE is an alternative source of OCSP certificate information in case the primary OCSP responder cannot be reached. Most of the up-to-date Snowflake drivers access the OCSP_CACHE rather than connect directly to OCSP_RESPONDER.
    • SNOWSQL_REPO is the endpoint that would be accessed through SnowSQL to perform bootstrap upgrade and download.
  • Host: The full host name, for example: “testaccount5.east-us-2.azure.snowflakecomputing.com”
  • Port: The port number, for example: 443.

Usage Notes

WHITELIST must be specified as a system function, i.e., SYSTEM$WHITELIST(), not just WHITELIST().

Examples

Here’s an example of calling the function:

SELECT SYSTEM$WHITELIST();

The output looks similar to the following (this example output has extra whitespace to make it easier to read):

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

If you’d like tabular output rather than JSON output, you can use the FLATTEN function and PARSE_JSON function to call the function with a query like the following:

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;
+----------------------+-----------------------------------------------+------+
| TYPE                 | HOST                                          | PORT |
|----------------------+-----------------------------------------------|------|
| SNOWFLAKE_DEPLOYMENT | testaccount5.snowflakecomputing.com           |  443 |
| STAGE                | sfc-customer-stage.s3.us-west-2.amazonaws.com |  443 |
| OCSP_CACHE           | ocsp.snowflakecomputing.com                   |   80 |
+----------------------+-----------------------------------------------+------+