Firewall Updater

This solution was developed in the Data Platfrom Team and contributed to the CLOE Terraform Solutions.

Description

The Firewall Updater will create a network policy on predefined Snowflake accounts that whitelists given IPs.

This solution is meant to be used in combination with the Azure Service Tag API.

Considered IPs:

1. The IP of the agent running the automation. This is mandatory in Snowflake to prevent a customer-lockout from Network Policy changes.

2. Base IPs that must be whitelisted on all Snowflake Accounts.s

3. Service Tag IPs, i.e. IPs from the combination of services and regions (e.g. DataFactory.WestEurope).

4. To allow the account owner to extend the allowed IPs independently and flexibly, exceptions can be entered on the network policy MANAGED_NETWORK_POLICY_EXTENSION of the snowflake account.

Adding additional Base IPs

Base IPs must be added to the projects Azure Storage Table IPConfiguration with the partition key BaseIP. These IPs will be considered in the automations next run.

Deployment

:info: The following steps should be done within the supplied DevContainer.

The Snowflake Firewall Updater infrastructure is deployed using Terraform. To prepare the deployment, first fill out the config Yaml file located at terraform/config/<env>.yml directory, where env refers to the project environment, e.g., dev, uat, or prd. See the section about the yaml config file for details. Fill the terraform backend config file terraform/config/<env>.tfbackend, where env refers to the project environment, e.g., dev, uat, or prd. See the section about the backend config file for details.

:info: You can have separate config files for each environment. Make sure to use also separate state files in your backend config, for example by changing the the key of the state file.

Use the Terminal to navigate to the terraform directory (cd snowflake/FirewallUpdater/terraform). Login using the Azure CLI (az login --use-device-code; follow the instructions given by the Azure CLI).

Deploy the infrastructure using the deploy.sh bash script (bash deploy.sh --env <env>, where env is the environment you want to deploy to, e.g., dev, uat, or prd). The script will run terraform and display a deployment plan. Verify that the plan matches your configuration. The script will prompt you if the proposed plan should be applied. Answer with y to roll out the proposed plan.

YAML Config File

subscription_id: "<SUBSCRIPTION_ID>" # Azure Subscription Id. Must exist before deployment.
resource_group_name: "<RG_NAME>" # Azure Resource Group Name where the Firewall Updater is deployed. Must exist before deployment.
service_tag_api_url: "https://<SERVICE_TAG_API>.azurewebsites.net/api/list" # URL of the Service Tag API. Must exist before deployment.

#######################################
#    INFRASTRUCTURE CONFIGURATION     #
#######################################
storage_account:
  name: "<STORAGE_ACCOUNT_NAME>" # Enter the name of the storage account for the Firewall Updater.
  account_tier: "Standard" # Storage account tier. Leave at 'Standard' unless required otherwise.
  account_replication_type: "LRS" # Storage account replication. Leave at 'LRS' unless required otherwise.
  shared_access_key_enabled: true # Allow or Disallow key based access to storage account.
  tables: # Names of tables to be deployed. Keep as is unless you know what you are doing.
    - AccountConfiguration
    - IpConfiguration

function_app:
  name: "<FUNCTION_APP_NAME>" # Enter the name of the Firewall Updater function app.
  plan:
    name: "<APP_SERVICE_PLAN_NAME>" # Enter the name of the App Service Plan hosting the function app.
    sku: "<APP_SERVICE_PLAN_SKU>" # Enter the SKU of the App Service Plan. See https://azure.microsoft.com/en-us/pricing/details/app-service/linux/
  python_version: "3.11" # Used python version. Keep as is unless you know what you are doing.
  version: "~4" # Version of the function app. Keep as is unless you know what you are doing.
  app_insights_name: "<APP_INSIGHTS_NAME>" # Enter the name of the application insights used for logging.

#######################################
#       SNOWFLAKE CONFIGURATION       #
#######################################
snowflake_config:
  CLOE_SNOWFLAKE_USER: "<SNOWFLAKE_USER>" # Name of the Snowflake User used by the Firewall Updater.
  CLOE_SNOWFLAKE_PASSWORD: "@Microsoft.KeyVault(SecretUri=<SECRET_URI>)" # Password of the Snowflake User. Use a Key Vault Reference here.
  CLOE_SNOWFLAKE_WAREHOUSE: "<SNOWFLAKE_WAREHOUSE>" # Warehouse used by the Firewall Updater.
  CLOW_SNOWFLAKE_ROLE: "<SNOWFLAKE_ROLE>" # Role used in the Firewall Updater.

#######################################
#   FIREWALL UPDATER CONFIGURATION    #
#######################################
account_config: # List of Configurations for the Firewall Updater App.
  - account: "<SNOWFLAKE_ACCOUNT>" # Snowflake Account where the Firewall Updater should run.
    service_tag: "<SERVICE_TAG>" # Name of the Service Tag that should be added.
    region: "<AZURE_REGION>" # Azure Region of the Service Tag. Not all Service Tags are regional.

See below for more details about the Snowflake and Firewall Updater configuration sections.

Terraform Backend Config File

The Backend Config file contains information where the Terraform State is stored.

• subscription_id: Azure Subscription Id
• resource_group_name: Resource Group Name where the State is stored. This does not need to be the same as the Firewall Updater RG.
• storage_account_name: Storage Account used to store the Terraform state.
• container_name: Container name on the Storage Account where the Terraform state is stored.
• key: Name of the state file.

Azure RBAC Role Assignments

The Function App Managed Identity requires the following roles:

• Storage Blob Data Contributor: On the Function App Storage Account
• Storage Table Data Reader: On the Function App Storage Account
• Key Vault Secrets Officer: On the Key Vault containing the Firewall Updater configuration. Alternatively, Access Policies can be set for the Function App Managed Identity.

Adding a Snowflake Account to the Automation

Updating the Configuration

Add the configuration of the new account to the projects Azure Storage Table AccountConfiguration. The AccountConfiguration table is populated via Terraform using the account_config field in the config.yml.

Example:

account_config:
  - account: "foo.west-europe.azure"
    service_tag: "PowerBI"
    region: ""
  - account: "foo.west-europe.azure"
    service_tag: "AzureDevOps"
    region: "westeurope,northeurope,northeurope2"
  - account: "bar.west-europe.azure"
    service_tag: "SqlManagement"
    region: ""
  - account: "baz.west-europe.azure"
    service_tag: ""
    region: ""

Examplatory configuration in AccountConfiguration for the accounts foo, bar & baz on west-europe azure:

Account	ServiceTag	Region
foo.west-europe.azure	PowerBI
foo.west-europe.azure	AzureDevOps	westeurope,northeurope,northeurope2
bar.west-europe.azure	SqlManagement
baz.west-europe.azure

Accepted ServiceTag and Region values can be taken from the service tag api's /capabilities endpoint. Accounts without ServiceTag & Region information will still be considered for the other types of whitelisted IPs.

Preparation on the Snowflake Account

• Create a technical user in Snowflake:
• Create the user MY_SNOWFLAKE_USER with the PUBLIC role on the Snowflake account.
• The preparation on the account must be performed by an Account Admin USE ROLE ACCOUNTADMIN;
• Create the role with minimum grants for the necessary tasks:
• CREATE ROLE network_editor;
• GRANT ATTACH POLICY,CREATE NETWORK POLICY on ACCOUNT to network_editor;
• GRANT ROLE network_editor TO ROLE ACCOUNTADMIN
• Create an allow all exception for the technical user, to prevent a lockout and transfer the ownership, to allow technical user to run CREATE OR REPLACE NETWORK POLICY ALLOW_ALL ALLOWED_IP_LIST = ('0.0.0.0/0') This will guarantee the policy to be in place:
• CREATE OR REPLACE NETWORK POLICY ALLOW_ALL ALLOWED_IP_LIST = ('0.0.0.0/0');
• GRANT OWNERSHIP ON NETWORK POLICY ALLOW_ALL TO ROLE network_editor;
• Create the network policy for additional IPs to be whitelisted:
• CREATE OR REPLACE NETWORK POLICY MANAGED_NETWORK_POLICY_EXTENSION ALLOWED_IP_LIST = ('<your current ip>'); To create a network policy, the users current IP must be in it.
• GRANT OWNERSHIP ON NETWORK POLICY MANAGED_NETWORK_POLICY_EXTENSION TO ROLE network_editor;
• Alter the technical user and grant the network_editor role to it:
• ALTER USER "MY_SNOWFLAKE_USER" SET NETWORK_POLICY = ALLOW_ALL;
• GRANT ROLE network_editor TO USER "MY_SNOWFLAKE_USER";

Authentication to Snowflake

The Firewall Updater uses the CLOE Snowflake Connector to authenticate towards Snowflake. The configuration parameters for the Snowflake Connector are read from the environment variables set on the Firewall Updater app.

The environment variables are set via Terraform using the snowflake_config field in the config.yml. Secrets, e.g., for the Snowflake password, can be read from a keyvault using the following syntax: "@Microsoft.KeyVault(SecretUri=<secret_uri>)".

Example:

snowflake_config:
  CLOE_SNOWFLAKE_USER: "MY_USER"
  CLOE_SNOWFLAKE_PASSWORD: "@Microsoft.KeyVault(SecretUri=https://my-key-vault.vault.azure.net/secrets/snowflake-password-firewall-updater)"
  CLOE_SNOWFLAKE_WAREHOUSE: "MY_WAREHOUSE"
  CLOW_SNOWFLAKE_ROLE: "MY_NETWORK_ROLE"

Adding Additional IPs

Additional IPs can be added to the network policy MANAGED_NETWORK_POLICY_EXTENSION. Those will be added to the network policy MANAGED_NETWORK_POLICY. This allows the snowflake accountadmin to extend the automation in a flexible manner.