BigQuery Configuration

GCP Project BigQuery required permissions

Configuring BigQuery connection requires permissions included in the following predefined IAM roles:

• roles/bigquery.connectionAdmin
• roles/resourcemanager.projectIamAdmin

Additionally the following permissions on the data set are required to configure remote function:

• bigquery.connections.delegate
• bigquery.routines.create
• bigquery.routines.delete
• bigquery.routines.get
• bigquery.routines.list
• bigquery.routines.update
• bigquery.routines.updateTag

Setup the BigQuery Connection

1. Open Cloud Shell Terminal in your GCP Project.

2. Run the following command, replacing <my-project-id>, <location> and <my-connection> with your project id, location of your BigQuery dataset and the id of the connection you are about to create.

   bq mk --connection --display_name='Protegrity Cloud Protect' --connection_type=CLOUD_RESOURCE 
   --project_id=<my-project-id> --location=<location> <my-connection>
   

3. Record the connection id. You will use it in the next steps.

   Cloud Resource Connection ID: ___________________

4. Run the command below to display information about BigQuery connection you created in the previous step.

   bq show --location=<location> --connection  <my-connection>
   

5. Record the serviceAccountId value. This service account was generated for the connection your created in the previous step. It will be used to authenticate BigQuery requests to Cloud Function.

   Cloud Resource Connection Service Account: ___________________

6. Run the following command to associate cloud function/run invoker role to the BigQuery connection created earlier. Replace <cloud-resource-connection-service-account> with service account recorded in the previous step. If protector is deployed in Cloud Functions Gen 2, role should be set to roles/run.invoker. For Cloud Functions Gen 1 use roles/cloudfunctions.invoker.

   gcloud projects add-iam-policy-binding <my-project-id> --member='serviceAccount:<cloud-resource-connection-service-account>' --role='<role>'
   

Test Connectivity

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

1. Access the GCP BigQuery console.

2. Copy and paste the following snippet into a BiqQuery SQL editor.

   CREATE OR REPLACE FUNCTION <dataset>.PTY_UNPROTECT_SAMPLE_POLICY(val STRING) RETURNS
     STRING
     REMOTE WITH CONNECTION `<region>.<cloud-resource-connection-id>`
     OPTIONS (
         endpoint ='https://<region>-<project-id>.cloudfunctions.net/<protect-function-name>',
         user_defined_context = [("data_element", "alpha"),("op_type", "unprotect")]
     );
   

3. Replace the placeholder values with your dataset, project-id, region and cloud-resource-connection-id recorded in previous section.

4. Run the following protect in the console:

   SELECT PTY.PTY_UNPROTECT_SAMPLE_POLICY('UtfVk UHgcD!');
   

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

Troubleshooting

Use Cloud Logging to 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\""