SYSTEM$ALLOWLIST¶

Syntax¶

SYSTEM$ALLOWLIST()

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

Snowflake supports the following types:

SNOWFLAKE_DEPLOYMENT

Hostname and port number information for your Snowflake account.

SNOWFLAKE_DEPLOYMENT_REGIONLESS

Hostname and port number information for your organization.

For more information, see Account identifiers.

STAGE

Location (i.e. 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.

CLIENT_FAILOVER

Hostname and port for the connection URL for Client Redirect. Note that each row in the query output that specifies this value refers to either the primary connection or the secondary connection depending on how the connection URLs were configured.

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_CACHE_REGIONLESS

Snowflake-provided alternative source of OCSP certificate information for your organization. Most of the latest versions of the Snowflake clients access the OCSP cache rather than connecting directly to the OCSP responder.

OCSP_CLIENT_FAILOVER

Snowflake-provided alternative source of OCSP certificate information for Client Redirect.

DUO_SECURITY

The hostname for the Duo Security service that is used with Multi-factor authentication (MFA) while authenticating to Snowflake.

OCSP_RESPONDER

Hostname to contact to verify that the OCSP TLS certificate has not been revoked.

Note that this value is not necessary when configuring private connectivity to the Snowflake service ; follow the instructions in the corresponding topic to select the OCSP value to add to your allowlist.

SNOWSIGHT_DEPLOYMENT_REGIONLESS

Hostname and port number for your organization to access Snowsight.

For more information, see Account identifiers and Snowsight: The Snowflake web interface.

SNOWSIGHT_DEPLOYMENT

Hostname and port to access Snowsight for your Snowflake account.

host

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

port

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

Usage notes¶

• The output might include multiple entries for certain types (e.g. STAGE, OCSP_RESPONDER).

• Occasionally, Snowflake cannot resolve the socket connection from the client that calls the function, and the statement that calls the function fails with one of the following error messages:

  SYSTEM$ALLOWLIST: Fail to get SSL context
  SYSTEM$ALLOWLIST: SSLContext init failed
  SYSTEM$ALLOWLIST: Could not find host in OCSP dumping
  SYSTEM$ALLOWLIST: Peer unverified
  SYSTEM$ALLOWLIST: Connection failure
  

  Additionally, Snowflake returns an empty list for the OCSP fields in the function output. To troubleshoot, you can wait a few minutes and rerun the statement if the network connection is transient. If the issue persists, contact Snowflake Support.

Examples¶

To call the function:

SELECT SYSTEM$ALLOWLIST();

Copy

Sample output:

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

Copy

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$ALLOWLIST()))) AS t;

Copy

Sample output:

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

Copy