PostgreSQL Integration

Automating content discovery and Privacy Request fulfillment by integrating MineOS with PostgreSQL

This integration allows you to:

• Automate content classification to detect data types stored in your Postgres tables.
• Automate Copy and/or Delete requests for your Postgres tables.

Before you start

• Make sure your MineOS plan supports integrations.
• Make sure you have the required information to your Postgres database (e.g. hostname, credentials, query etc).

Setting up

To connect the Postgres integration, follow these steps:

1. On the left sidebar, click Data Inventory and then Data Sources
2. Click on Add data source
3. Select Postgres from the catalog, then enter the Postgres page from your data sources list
4. In the RequestHandling tab, check the Handle this data source in privacy requests checkbox and choose the Integration handling style.
6. Enter your Postgres Database Connection String which can include:
   1. User ID
   2. User Password
   3. Server's IP / Hostname
   4. Database Name
   For example, In the following format:
   
   "Host=33.111.123.222;Database=dbName;Username=userName;Password=myPassword;"
7. Note: If using MineOS VPN to connect with an internal database, be sure to add the database's IP as a route in the VPN settings page.

Setting up Content Discovery

Before using Content Discovery for your database, make sure to grant the Postgres user the required permissions (you can read about granting privileges here):

• SELECT

Postgres integration will scan any databases and tables it has access to, so you can grant permissions to all tables or some. 

When running the scan, MineOS will scan all your databases and tables it has access to, and analyze rows and columns with our PII Processing Engine. Scan results will appear under "Data Classifier" in the side menu, and any detected data types will be associated with the Postgres system.

Setting up Privacy Requests

To handle privacy requests, you need to define the way in which different actions will be handled by the database. The available actions are:

• Delete - Deleting/anonymizing user data.
• Copy - Extracting user data.
• Preview - optional action used for previewing data when using an interactive flow

At least one of Delete or Copy is mandatory, while Preview is optional and can be left empty.

Implementing Actions

There are generally 2 ways to implement actions:

1. SQL Queries - directly inputting queries inside MineOS while making use of available variables, such as endUserEmail. You can also make use of transactions and multi-statement queries. This will require you to grant enough permissions to MineOS to execute the query you typed.
2. Executing Stored Procedures - Executing a stored procedure which is defined inside your Postgres database. This is the preferred method as it allows you to restrict MineOS permissions only to executing a specific stored procedure, as well as keeping your business logic inside your database.

Example - Preview Query

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 table1 WHERE email = {% raw %} '{{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

Example - 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 table1 WHERE email = {% raw %} '{{endUserEmail}}';

* Copy will show all records returned in the query response

* The 'endUserEmail' variable is mandatory

Example - 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 table1 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.

What's next?

Read more about the deletion process using integrations here.

Read more about the get a copy process using integrations here.

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!🙂