Snowflake Configuration

The following sections will configure Snowflake to access the API Gateway. The Terraform installation deployed a sample policy that can be used to smoke test the installation.

Login to Snowflake as ACCOUNTADMIN

Ensure that the current user can assume the Account Administrator role. This role is required to create the Snowflake API Integration object.

Create the Snowflake API Integration Object

1. From the Snowflake console worksheet, select the role ACCOUNTADMIN.

2. Paste the following text and replace the two parameters <api_gateway_managed_service> and <api_gateway_protect_service_url> with values recorded in the last installation step of Install Protect Function via Terraform Scripts, then run the following Data Definition Language (DDL) in the console to create API integration object:

   create or replace api integration protegrity_api 
   api_provider = google_api_gateway
   google_audience = '<api_gateway_managed_service>' 
   enabled = true
   api_allowed_prefixes = ('<api_gateway_protect_service_url>/pty/snowflake');
   

   Note

   The name of the object protegrity_api can be replaced with a name of your choice, however the name you choose must be used consistently throughout the installation steps below.

Describe the API Integration Object

We require values generated by the Snowflake integration object to configure the API Gateway Authorization.

To describe API integration objects:

1. Run the following query in the console.

   DESCRIBE API INTEGRATION protegrity_api;
   

2. Record the API_GCP_SERVICE_ACCOUNT value from the resulting query:

   • API GCP Service Account: ___________________

Update API Gateway Authorization Configuration

This step allows the Snowflake service account to invoke Protect API Gateway endpoint.

Update Protect API Gateway Endpoint:

1. Return to Terraform script used to install Protegrity Protect service.

2. Open main.tf and update api_client_service_account_email with the API GCP Service Account recorded in previous step.

3. Run terraform apply.

4. Wait till the process is completed.

Test Connectivity

Perform the following steps to verify whether Snowflake is working correctly with the Protegrity product.

1. Access the Snowflake SQL console.

2. Copy and paste the following snippet into a worksheet.

   CREATE OR REPLACE SECURE EXTERNAL FUNCTION PTY_UNPROTECT_SAMPLE_POLICY(VAL VARCHAR)
       RETURNS VARCHAR(16777216)
       IMMUTABLE
       API_INTEGRATION = PROTEGRITY_API
       HEADERS =(  
       'X-Protegrity-HCoP-Rules'=
       '{"jsonpaths":[{"op_type":"UNPROTECT","data_element":"alpha"}]}'
       ) 
       CONTEXT_HEADERS = (CURRENT_USER,CURRENT_TIMESTAMP,CURRENT_ACCOUNT)
       COMMENT='Unprotects text using an alpha token type.'
       AS '<api_gateway_protect_service_url>/pty/snowflake';
   

3. Replace the placeholder value indicated substituting your API Gateway URL captured in the Terraform outputs (api_gateway_protect_service_url).

4. Run the following protect in the console:

   select pty_unprotect_sample_policy('UtfVk UHgcD!');
   

5. Verify that the string hello world! is returned.

Troubleshooting

Use Cloud Logging to troubleshoot errors.

From your Google Console, navigate to Logging > Logs Explorer

Use the Log Fields panel to filter results by resource type, name, severity, and other criteria. For instance to see the last Cloud Protect Function logs, make the following selections:

RESOURCE TYPE = Cloud Function 
    FUNCTION NAME = pty-protect-{deployment-id}

You can also use the Log Filter Query and run the following query:

resource.type="cloud_function" 
    resource.labels.function_name="pty-protect-"

You can change the time range in the top right corner. If Protegrity policy is configured to generate audit logs, you can use the following query to only view the audit logs:

resource.type="cloud_function" 
  resource.labels.function_name="pty-protect-" 
  jsonPayload.message=~"\"type\":\"audit\""