Snowpark Container Services: Additional considerations for services¶

Connecting to Snowflake¶

Snowflake provides the following environment variables for you to configure a Snowflake client in your service code:

• SNOWFLAKE_ACCOUNT: Provides the account locator for the Snowflake account the service is currently running under.

• SNOWFLAKE_HOST: Provides the hostname used to connect to Snowflake.

Snowflake also provides an OAuth token in the container in a file named /snowflake/session/token. When creating a connection, you provide this token to the connector.

When creating a connection to Snowflake from a container, you must use SNOWFLAKE_HOST, SNOWFLAKE_ACCOUNT, and the OAuth token. You cannot use the OAuth token without also using SNOWFLAKE_HOST, and you cannot use the OAuth token outside Snowpark Container Services. For more information, see Using an OAuth token to execute SQL.

For sample code using various Snowflake drivers, see Snowflake Connection Samples.

Examples

• In Tutorial 2 (see main.py), the code reads the environment variables as follows:

  SNOWFLAKE_ACCOUNT = os.getenv('SNOWFLAKE_ACCOUNT')
  SNOWFLAKE_HOST = os.getenv('SNOWFLAKE_HOST')
  

  Copy

  The code passes these variables to a connection creation code for the Snowflake client of choice. The container uses these credentials to create a new session, with the owner role as the primary role, to execute queries. The following example code is the minimum needed to create a Snowflake connection in Python:

  def get_login_token():
    with open('/snowflake/session/token', 'r') as f:
      return f.read()
  
  conn = snowflake.connector.connect(
    host = os.getenv('SNOWFLAKE_HOST'),
    account = os.getenv('SNOWFLAKE_ACCOUNT'),
    token = get_login_token(),
    authenticator = 'oauth'
  )
  

  Copy

• When you use the default host (that is, you don’t include the host argument when creating a connection), connecting to Snowflake using other forms of authentication (such as username and password) is supported. For example, the following connection specifies the user name and password to authenticate:

  conn = snowflake.connector.connect(
    account = os.getenv('SNOWFLAKE_ACCOUNT'),
    user = '<user-name>',
    password = <password>
  )
  

  Copy

  Using a default host requires external access integration with a network rule allowing access from your service to the Snowflake hostname for your account. For example, if your account name is MyAccount, the hostname will be myaccount.snowflakecomputing.com. For more information, see Configuring network egress.

  • Create a network rule that matches your account’s Snowflake API hostname:

    CREATE OR REPLACE NETWORK RULE snowflake_egress_access
      MODE = EGRESS
      TYPE = HOST_PORT
      VALUE_LIST = ('myaccount.snowflakecomputing.com');
    

    Copy

  • Create an integration using the preceding network rule:

    CREATE EXTERNAL ACCESS INTEGRATION snowflake_egress_access_integration
      ALLOWED_NETWORK_RULES = (snowflake_egress_access)
      ENABLED = true;
    

    Copy

Configuring the database and schema context for executing SQL¶

This section explains two concepts:

• The logic Snowflake uses to determine the database and schema in which to create your service.

• The method through which Snowflake conveys this information to your containers, thus enabling the container code to execute SQL in the same database and schema context.

Snowflake uses the service name to determine the database and schema in which to create a service:

• Example 1: In the following CREATE SERVICE and EXECUTE JOB SERVICE commands, the service name does not explicitly specify a database and schema name. Snowflake creates the service and the job service in the current database and schema.

  -- Create a service.
  CREATE SERVICE test_service IN COMPUTE POOL ...
  
  -- Execute a job service.
  EXECUTE JOB SERVICE
    IN COMPUTE POOL tutorial_compute_pool
    NAME = example_job_service ...
  

  Copy

• Example 2: In the following CREATE SERVICE and EXECUTE JOB SERVICE commands, the service name includes a database and schema name. Snowflake creates the service and job service in the specified database (test_db) and schema (test_schema), regardless of the current schema.

  -- Create a service.
  CREATE SERVICE test_db.test_schema.test_service IN COMPUTE POOL ...
  
  -- Execute a job service.
  EXECUTE JOB SERVICE
    IN COMPUTE POOL tutorial_compute_pool
    NAME = test_db.test_schema.example_job_service ...
  

  Copy

When Snowflake starts a service, it provides the database and schema information to the running containers using the following environment variables:

• SNOWFLAKE_DATABASE

• SNOWFLAKE_SCHEMA

Your container code can use environment variables in the connection code to determine which database and schema to use, as shown in this example:

conn = snowflake.connector.connect(
  host = os.getenv('SNOWFLAKE_HOST'),
  account = os.getenv('SNOWFLAKE_ACCOUNT'),
  token = get_login_token(),
  authenticator = 'oauth',
  database = os.getenv('SNOWFLAKE_DATABASE'),
  schema = os.getenv('SNOWFLAKE_SCHEMA')
)

Example

In Tutorial 2, you create a Snowflake job service that connects with Snowflake and executes SQL statements. The following steps summarize how the tutorial code uses the environment variables:

1. In the common setup (see the Common Setup section), you create resources, including a database and a schema. You also set the current database and schema for the session:

   USE DATABASE tutorial_db;
   ...
   USE SCHEMA data_schema;
   

   Copy

2. After you create a job service (by running EXECUTE JOB SERVICE), Snowflake starts the container and sets the following environment variables in the container to the current database and schema of the session:

   • SNOWFLAKE_DATABASE is set to “TUTORIAL_DB”

   • SNOWFLAKE_SCHEMA is set to “DATA_SCHEMA”

3. The job code (see main.py in Tutorial 2) reads these environment variables:

   SNOWFLAKE_DATABASE = os.getenv('SNOWFLAKE_DATABASE')
   SNOWFLAKE_SCHEMA = os.getenv('SNOWFLAKE_SCHEMA')
   

   Copy

4. The job code sets the database and schema as the context in which to execute the SQL statements (run_job() function in main.py):

   {
      "account": SNOWFLAKE_ACCOUNT,
      "host": SNOWFLAKE_HOST,
      "authenticator": "oauth",
      "token": get_login_token(),
      "warehouse": SNOWFLAKE_WAREHOUSE,
      "database": SNOWFLAKE_DATABASE,
      "schema": SNOWFLAKE_SCHEMA
   }
   ...
   

   Copy

   Note

   SNOWFLAKE_ACCOUNT, SNOWFLAKE_HOST, SNOWFLAKE_DATABASE, SNOWFLAKE_SCHEMA are environment variables that Snowflake generates for the application container, but SNOWFLAKE_WAREHOUSE is not (the Tutorial 2 application code created this variable because Snowflake does not pass a warehouse name to a container).

Specifying the warehouse for your container¶

If your service connects to Snowflake to execute a query in a Snowflake warehouse, you have the following options to specify a warehouse:

• Specify a warehouse in your application code. Specify a warehouse as part of the connection configuration when starting a Snowflake session to run queries in your code. For an example, see Tutorial 2.

• Specify a default warehouse when creating a service. Specify the optional QUERY_WAREHOUSE parameter in the CREATE SERVICE (or EXECUTE JOB SERVICE) command to provide a default warehouse. If your application code does not provide a warehouse as part of connection configuration, Snowflake uses the default warehouse. Use the ALTER SERVICE command to change the default warehouse.

If you specify a warehouse using both methods, the warehouse specified in the application code is used.

Using an OAuth token to execute SQL¶

All Snowflake-provided clients support OAuth as a way to authenticate. Service containers also use the OAuth mechanism to authenticate with Snowflake. For example, when a container wants to execute SQL, the container creates a connection to Snowflake, similar to any other Snowflake client:

def get_login_token():
  with open('/snowflake/session/token', 'r') as f:
    return f.read()

conn = snowflake.connector.connect(
  host = os.getenv('SNOWFLAKE_HOST'),
  account = os.getenv('SNOWFLAKE_ACCOUNT'),
  token = get_login_token(),
  authenticator = 'oauth'
)

When you create a service, Snowflake runs the containers and provides an Oauth token for containers to use at the following location within the container: /snowflake/session/token.

Note the following details about this OAuth token:

• You should read the contents of the /snowflake/session/token file immediately before use because the content expires in 10 minutes, and Snowflake refreshes this file every few minutes. After a container connects to Snowflake successfully, the expiration time does not apply to the connection (as with any sessions that users create directly).

• This OAuth token is valid only within the specific Snowflake service. You cannot copy the OAuth token and use it outside the service.

• Using the OAuth token, the containers connect with Snowflake as the service user and use the service’s owner role.

• Using the OAuth token to connect will create a new session. The OAuth token is not associated with any existing SQL session.

  Note

  A significant difference between executing stored procedures and executing a service is that stored procedures run in the same session as the SQL that runs them. But every time a container establishes a new connection, you are creating a new session.

Connecting to Snowflake from inside a container using caller’s rights¶

Containers execute queries by connecting to Snowflake as the service user and using the service’s owner role that grants privileges to the service. In certain application scenarios, you might need to execute queries using the context of the end user rather than the service user. The caller’s right feature is used in this context.

For example, suppose you create a service that exposes a public endpoint to provide a web application that displays a dashboard using data stored in Snowflake. You grant other users in your Snowflake account access to the dashboard (by granting the service role to those users). When any of these end users log in to the dashboard, you want the dashboard to display only the data the user has access to.

However, because containers by default execute queries using service user and service’s owner role, the dashboard shows the data the service’s owner role has access to, regardless of which end user connected to the endpoint. As a result, the data in the user’s dashboard is not limited to what the end user has access to, which allows the user to access data that they should not be allowed to access.

To limit the dashboard to show only data accessible to the logged in user, the application containers need to execute SQL using privileges granted to the end user. You can enable this by using caller’s rights in the application.

ALTER USER my_user SET DEFAULT_SECONDARY_ROLES = ( 'ALL' );

-- Permissions to resolve the table's name.
GRANT CALLER USAGE ON DATABASE <db_name> TO ROLE <service_owner_role>;
GRANT CALLER USAGE ON SCHEMA <schema_name> TO ROLE <service_owner_role>;
-- Permissions to use a warehouse
GRANT CALLER USAGE ON WAREHOUSE <warehouse_name> TO ROLE <service_owner_role>;
-- Permissions to query the table.
GRANT CALLER SELECT ON TABLE T1 TO ROLE <service_owner_role>;

Ingress and web app security¶

You can create a Snowpark Container Services service for web hosting using the public endpoint support (network ingress). For added security, Snowflake employs a proxy service to monitor incoming requests from clients to your service and outgoing responses from your service to the clients. This section explains what the proxy does and how it impacts a service deployed to Snowpark Container Services.

For example:

• The proxy does not forward an incoming HTTP request if the request uses a banned HTTP method.

• The proxy sends a 403 response to the client if the Content-Type header in the response indicates that the response contains an executable.

Additionally, the proxy can also inject new headers and alter existing headers in the request and the response, with your container and data security in mind.

For example, upon receiving a request, your service might send HTML, JavaScript, CSS, and other content for a web page to the client browser in the response. The web page in the browser is part of your service, acting as the user interface. For security reasons, if your service has restrictions (such as a restriction on making network connections to other sites), you might also want the web page for your service to have the same restrictions.

By default, services have limited permissions to access the internet. The browser should also restrict the client app from accessing the internet and potentially sharing data in most cases. If you set up an External Access Integration (EAI) to allow your service to access example.com (see Configuring network egress), the web page for your service should also be able to access example.com through your browser.

The Snowflake proxy applies the same network restrictions on the service and the web page by adding a Content-Security-Policy (CSP) header in the response. By default, the proxy adds a baseline CSP in the response to protect against common security threats. Browser security is a best effort to balance functionality with security, it is a shared responsibility to ensure this baseline is appropriate for your use case. In addition, if your service is configured to use an EAI, the proxy applies the same network rules from the EAI to the CSP for the web page. This CSP enables the web page in the browser to access the same sites that the service can access.

The following sections explain how the Snowflake proxy handles incoming requests for your service and modifies the outgoing responses from your service to the clients.

Network egress using private connectivity¶

Instead of routing network egress via the public internet, you might opt to direct your service’s egress traffic through a private connectivity endpoint.

You first need to create the private connectivity endpoint in your Snowflake account. Then configure a network rule to permit outgoing traffic to use private connectivity. The process for setting up an External Access Integration (EAI) remains the same as described in the preceding section.

For example, if you want to enable your service’s outbound internet access to an Amazon S3 bucket via private connectivity, you do the following:

1. Enable the private link connectivity for the self-maintained endpoint service (Amazon S3). For step-by-step instructions, see AWS Private Link for Amazon S3.

2. Call the SYSTEM$PROVISION_PRIVATELINK_ENDPOINT system function to provision a private connectivity endpoint in your Snowflake VNet. This enables Snowflake to connect to the external service (in this example, Amazon S3) using private connectivity.

   USE ROLE ACCOUNTADMIN;
   
   SELECT SYSTEM$PROVISION_PRIVATELINK_ENDPOINT(
     'com.amazonaws.us-west-2.s3',
     '*.s3.us-west-2.amazonaws.com'
   );
   

   Copy

3. In the cloud provider account, approve the endpoint. In this example, for Amazon AWS, see Accept or reject connection requests in the AWS documentation. Also, to approve the endpoint in Azure, see the Azure documentation.

4. Use the CREATE NETWORK RULE command to create an egress network rule specifying the external destinations that you want to allow access to.

   CREATE OR REPLACE NETWORK RULE private_link_network_rule
     MODE = EGRESS
     TYPE = PRIVATE_HOST_PORT
     VALUE_LIST = ('<bucket-name>.s3.us-west-2.amazonaws.com');
   

   Copy

   The TYPE parameter value is set to PRIVATE_HOST_PORT. It indicates that the network rule allows outgoing network traffic to use private connectivity.

5. The rest of the steps to create an EAI and use it to create a service are the same as explained in the preceding section (see Configuring network egress).

For more information about working with private connectivity endpoints, see the following:

• Manage private connectivity endpoints: AWS

• Manage private connectivity endpoints: Azure

Specifying Snowflake secrets¶

Specify a Snowflake secret by name or reference (reference is applicable only in the Native Application scenario):

• Pass Snowflake secret by name: You can pass a secret name as the snowflakeSecret field value.

  ...
  secrets:
  - snowflakeSecret:
      objectName: '<secret-name>'
    <other info about where in the container to copy the secret>
    ...
  

  Copy

  Note that you can optionally specify <secret-name> directly as the snowflakeSecret value.

• Pass Snowflake secret by reference: When using Snowpark Container Services to create a Native App (an app with containers), the app producer and consumers use different Snowflake accounts. In some contexts an installed Snowflake Native App needs to access existing secret objects in the consumer account that exist outside the APPLICATION object. In this case, developers can use the “secrets by reference” specification syntax to handle credentials as shown:

  containers:
  - name: main
    image: <url>
    secrets:
    - snowflakeSecret:
        objectReference: '<reference-name>'
      <other info about where in the container to copy the secret>
  

  Copy

  Note that the specification uses objectReference instead of the objectName to provide a secret reference name.

Specifying secrets placement inside the container¶

You can tell Snowflake to either place the secrets in the containers as environment variables or write them into local container files.

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: <secret-name>
    secretKeyRef: username | password | secret_string |  'access_token'
    envVarName: '<env-variable-name>'

CREATE SECRET testdb.testschema.my_secret_object
  TYPE = password
  USERNAME = 'snowman'
  PASSWORD = '1234abc';

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: testdb.testschema.my_secret_object
    secretKeyRef: username
    envVarName: LOGIN_USER
  - snowflakeSecret: testdb.testschema.my_secret_object
    secretKeyRef: password
    envVarName: LOGIN_PASSWORD

CREATE SECRET testdb.testschema.my_secret
  TYPE=generic_string
  SECRET_STRING='
       some_magic: config
  ';

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: testdb.testschema.my_secret
    secretKeyRef: secret_string
    envVarName: GENERIC_SECRET

containers:
- name: <name>
  image: <url>
  ...
  secrets:
  - snowflakeSecret: <snowflake-secret-name>
    directoryPath: '<local directory path in the container>'

CREATE SECRET testdb.testschema.my_secret_object
  TYPE = password
  USERNAME = 'snowman'
  PASSWORD = '1234abc';

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: testdb.testschema.my_secret_object
    directoryPath: '/usr/local/creds'

CREATE SECRET testdb.testschema.my_secret
  TYPE=generic_string
  SECRET_STRING='
       some_magic: config
  ';

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: testdb.testschema.my_secret
    directoryPath: '/usr/local/creds'

CREATE SECRET testdb.testschema.oauth_secret
  TYPE = OAUTH2
  OAUTH_REFRESH_TOKEN = '34n;vods4nQsdg09wee4qnfvadH'
  OAUTH_REFRESH_TOKEN_EXPIRY_TIME = '2023-12-31 20:00:00'
  API_AUTHENTICATION = my_integration;

containers:
- name: main
  image: <url>
  secrets:
  - snowflakeSecret: testdb.testschema.oauth_secret
    directoryPath: '/usr/local/creds'