Integrations
      Back to home
      1. Help center
      2. Integrations

      Redshift Integration

      Integrate with AWS Redshift to handle Privacy requests and perform automated content classification on your data

      About the Amazon Redshift Integration 

      What it does: 

      • Performs content scanning on clusters in AWS Redshift to detect and map the types of data stored inside it.
      • Redshift integration will scan all authorized clusters for the pre-created specific db user and perform data classification on all accessible tables.
      • Automate Copy and Delete requests for your Redshift tables.

      Before setting up this integration:

      • Be sure to add Amazon Redshift to your Inventory. To learn how to add systems to your Inventory, click here.
      • Make sure your MineOS plan supports automatic integrations.
      • Make sure your provided DB User for scanning already exists as part of Redshift.
      • For Privacy Requests handling support - make sure the user has permissions for SELECT and DELETE on the desired Redshift database, and tables in it.

      How to set up

      On AWS - first, create a role for MineOS to assume

      1. Log into your AWS account
      2. Go to IAM -> Roles -> Create Role
      3. Under Trusted entity type  choose AWS account
      4. Choose Another AWS account and enter the account ID 595379680669
      5. Under Options, mark Require External ID and choose your external ID secret.
        Make sure to remember your external ID for connecting the integration in MineOS later on.

      6. Click Next
      7. Skip the Add Permissions page as we will attach them to the role later 
      8. Click Next
      9. Add your Role Name, Description
      10. Under IAM -> Roles choose the create Role
      11. Under Permissions Policy click Add Permissions -> Create Inline Policy 
      12. Click Json on the upper right side and paste the following policy
      13. Click Next
      14. Name the policy and Create Policy

      Make sure to fill in your AccountID and DbUser name inside the JSON.
      The current JSON uses a wild flag for regions and clusters, please make sure to keep those for us to scan all clusters.

      {
          "Version": "2012-10-17",
          "Statement": [
              {
                  "Sid": "DescribeClusters",
                  "Effect": "Allow",
                  "Action": [
                      "redshift:DescribeClusters"
                  ],
                  "Resource": [
                      "*"
                  ]
              },
              {
                  "Sid": "GetClusterCredentials",
                  "Effect": "Allow",
                  "Action": [
                      "redshift:GetClusterCredentials"
                  ],
                  "Resource": [
                      "arn:aws:redshift:*:{YouAccountID}:dbuser:*/{YouDbUser}"
                  ]
              }
          ]
      }

      Make sure MineOS has access to your Redshift clusters

      MineOS IPs should be whitelisted in your VPC security group of choice inbound rules. Connecting via VPN tunneling is also a MineOS available feature if your clusters aren't publicly accessible

      MineOS IPs
      - 34.77.7.236
      - 35.187.97.141
      - 34.76.247.90
      - 34.140.71.114

      On MineOS: 

      1. Head to your Data Inventory and select Redshift
      2. Scroll down to the component titled Request handling
      3. Select Scan this source using Data Classifier
      4. Select Integration as the handling style.
      5. Paste your External ID, Account ID, Role Name, DB User, and Region of choice.
      6.  If successful, click Test & save to enable the integration. 

      A connection is considered successful when at least one database has been discovered and successfully accessed.


      DSR Queries

      To manage your DSR handling actions, you need to define the queries that will run on the Database for each possible action:

      Note: The queries you use won't be validated! You should run them in your Redshift account to make sure they are running as expected


      Query used for search records & delete validation

      The query will be used for showing how many records were found as well as showing a sample (preview) of the data. Also, it will be used as a validation for the delete action.

      Example Query:

      SELECT name,phone,address FROM "dbName"."schema"."table" WHERE email =  '{{endUserEmail}}';

      * Preview will return the count of the number of records returned in the query response

      * Preview will show the first 3 values from the query response, it supports strings and long types

      * The 'endUserEmail' variable is mandatory

       

      Copy Query

      The Copy query will be used for the Copy action. Copy action is running on the ticket processing page in ticket of type Copy when clicking on Generate Copy

      Example Query:

      SELECT * FROM "dbName"."schema"."table" WHERE email = {% raw %} '{{endUserEmail}}';

      * Copy will show all records returned in the query response

      * The 'endUserEmail' variable is mandatory

       

      Delete Query

      The Delete query will be used for the Delete action. Delete action is running on the ticket processing page in ticket of type Deletion when clicking on Delete from X sources

      Example Query:

      DELETE FROM "dbName"."schema"."table" WHERE email = '{{endUserEmail}}';

      * The 'endUserEmail' variable is mandatory.

      Paste the details and queries in the Request handling tab in the correct inputs and click Save.

       

       

      Talk to us if you need any help with integrations via our chat or at portal@saymine.com, and we'll be happy to assist!🙂