Finding PII in the sample PostgreSQL database

What the PII Finder does

In this tutorial, the XDM PII Finder will be used to analyze the sample database.

PII means personally identifiable information. Typical examples are names, dates of birth, email addresses, phone numbers, or other values that can identify a person directly or indirectly.

A PII Finder task analyzes selected tables in one database. It reads data from the selected tables and checks how many values in each column match one or more configured matchers. A Matcher defines the criteria that XDM uses to recognize a specific type of data. The PII Finder does not decide whether a column must be masked from a legal or organizational point of view. It helps you find columns that should be reviewed before you define masking rules. In the sample environment, the table production.employees contains employee data. This makes it a useful example for learning how to identify personal data before masking it in a later tutorial.

How matchers work

A Matcher is an XDM object that checks the values of a column. It returns how many cells match the configured criteria.

There are two basic matcher strategies:

Dictionary matchers

A dictionary matcher compares values with entries from a dictionary. A dictionary is a text file containing known values, for example first names, last names, cities, or countries.

Regular expression matchers

A regular expression matcher compares values with a regular expression. This is useful for values that follow a recognizable structure, such as email addresses, phone numbers, postal codes, or identification numbers.

XDM applies the selected matchers to the analyzed columns. If enough values in a column match a matcher, the column is reported as a possible hit.

Dictionary matchers

For this tutorial, download the existing International dictionary matchers for first names and last names. Dictionary matchers are useful when the possible values are known in advance. For example, first names and last names can often be detected by comparing column values with lists of known names. In the sample database, the table production.employees contains the columns first_name and last_name. These columns are good candidates for dictionary-based matching.

Only dictionary matchers supplied with XDM can be used as dictionary matchers. User-defined matchers cannot use the dictionary matcher type, and uploaded file objects cannot be assigned as custom dictionaries for a matcher. To define a user-defined matcher, use the regular expression matcher type.

Regular expression matchers

A regular expression is a search pattern for text. It defines which character combinations are accepted as a match.

Regular expressions are useful when data has a typical structure. For example, email addresses usually contain an @ character and a domain name. Phone numbers often contain digits, spaces, hyphens, or parentheses.

This tutorial creates two custom regular expression matchers:

  • one matcher for email addresses,

  • one matcher for phone numbers.

These matchers are intentionally simple. They are suitable for explaining the concept, but they may need to be adapted for real project data.

Creating custom matchers

In this section, two custom matchers are created. Both matchers use regular expressions.

Create an email matcher

Create a new matcher for email addresses.

  1. Open the list of matchers on the left side Panel under Masking

  2. Click + Create.

  3. Enter the following values:

    Name

    Tutorial email address

    Description

    Detects simple email address values for the PII Finder tutorial.

    Type

    Regular expression

    Country

    International

    Minimum length

    5

    Match Parts

    Unchecked

    Content

    		 
    [a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?

  4. Click Save Changes.

The regular expression checks for email address-like values with the following structure:

  • a local part before the @ character,

  • an @ character,

  • and a dot-separated domain name.

The local part can contain lowercase letters, digits, and common special characters that are often used in email addresses. It can also contain dot-separated sections, for example first.last or user.name.

The domain part must be a dot-separated domain name, such as example.com or example.org.

This expression intentionally covers only common email address formats.

The regular expression matcher in XDM is case-insensitive. Therefore, the expression can also match uppercase and mixed-case email addresses.

Creating a phone number matcher

Create a second matcher for phone numbers.

  1. Open the list of matchers again.

  2. Click + Create.

  3. Enter the following values:

    Name

    Tutorial phone number

    Description

    Detects simple phone number values for the PII Finder tutorial.

    Type

    Regular expression

    Country

    Generic

    Minimum length

    5

    Match Parts

    Unchecked

    Content

    		 
    ^(?=.*(?:tel:|\+|-|/))(?:tel:\s*)?\+?\s*(?=(?:.*[0-9]){5,})(?![0-9]{4}-[0-9]{2}-[0-9]{2}$)[0-9][0-9\s/-]*$

  4. Click Save Changes.

The regular expression checks for a simple phone number structure:

  • an optional leading plus sign,

  • a digit at the beginning,

  • followed by digits, spaces, parentheses, slashes, or hyphens.

The matcher is intentionally not exhaustive. It detects many typical phone number formats, but it does not guarantee that every phone number is found. For example, numbers that only use spaces as separators may not match this expression.

This is a broad pattern. It may also match values that are not phone numbers. For real project data, adjust the expression to the expected phone number format in your source system.

Creating the PII Finder task template

Now create the PII Finder task template.

  1. In the left sidebar, click the menu category Tasks to expand the Tasks menu.

  2. Click the menu item Task Templates.

  3. Open the section Analyze and click on PII Finder task templates.

  4. Click + Create.

  5. Enter the following value:

    Name

    Find PII in production employees

  6. Click Create and edit.

  7. Select the source connection: sample production database.

  8. In the upper panel click on Finder Settings.

  9. Select the matchers that should be used by the task template:

    • the available dictionary matcher for first names,

    • the available dictionary matcher for last names,

    • Tutorial email address,

    • Tutorial phone number.

  10. In the Finder Settings section, configure how the PII Finder analyzes the selected data.

    Option

    Value

    Use absolute count

    Enabled

    Sample size

    0

    Probability

    20

    The Sample size defines how many rows per table are analyzed. The default Sample size is 10000. If the sample size is smaller than the number of rows in the table, only that number of rows is analyzed. In this tutorial, the sample size is set to 0 so that the complete production.employees table is scanned.

    The Use absolute count option changes the report output from percentages to absolute hit counts. This makes the result easier to verify in the sample database, because the production.employees table contains 300024 rows. If this option is disabled the result will be shown in percentage.

    The Probability value is the minimum percentage of matching values required for a result to be reported when percentage-based reporting is used. For this tutorial, the value can remain 20.

  11. Click Save Changes.

The Task Template now defines which database connection is analyzed and which matchers are used.

Selecting the tables to analyze

The PII Finder must know which tables it should analyze. This is configured with selection rules.

For this tutorial, analyze only the employees table in the production schema.

  1. Open the expandable panel Rules at the bottom of the Task Template page.

  2. Select the tab Selection Rules.

  3. Click + Create

  4. select Sample production database

  5. Enter the following values:

    Active

    Checked

    Selection type

    Pattern

    Object type

    Table

    Schema pattern

    production

    Name pattern

    employees

  6. Click Save Changes.

The selection rule selects the table production.employees.

To analyze all tables in the production schema, use % as the name pattern. The percent sign is a wildcard and matches any sequence of characters.

Creating and executing the PII Finder task

Create a task and execute it.

  1. Open the expandable panel Tasks at the bottom of the task template page.

  2. Click + Create.

  3. Enter the following value:

    Name

    Find PII in production employees

  4. Click Create and edit.

  5. Optional: Enter a description for the task.

  6. Click Save Changes.

  7. Click Execute.

  8. In the execution dialog, click Execute and view.

The task runs asynchronously in the background.

A PII Finder task has two stages:

Stage 1

XDM collects the structure information for the selected tables. Tables and columns excluded by exclude rules are not part of the selection set.

Stage 2

XDM reads data from the selected tables and compares the values with the selected matchers.

When the task has finished, the task execution status should be COMPLETE.

Reviewing the result

Open the report for Stage 2 and review the analysis result. The report shows which columns matched the selected matchers.

The data used in this tutorial is controlled and well known. Therefore, the expected numbers are high and very precise.

In real project data, the result is usually less exact. Values may use different formats, contain spelling variants, include incomplete data, or be stored together with other information in the same column. For this reason, matcher results should always be reviewed and, if necessary, the regular expressions should be adjusted to the actual source data.

Expected findings for this tutorial are:

  • production.employees.first_name = 300024 rows.

  • production.employees.last_name = 300024 rows.

  • The custom email matcher = 300024 rows.

  • The custom phone number = 239791 rows.

A missing result does not always mean that a column is not sensitive. It can also mean that no selected matcher matches the values in that column, or that the configured threshold was not reached.

Next step

The PII Finder helps you identify columns that may contain personal data. In the next step we will mask first and last name. Therefore, modification methods and a modification set to mask personal data in the employees table will be created.

Previous section: Native Table Copy Tasks | Next section: Modification Sets and Modification Methods