Secure PII Data during Deployment – Liquibase

Applies to

Liquibase Secure (Pro)

Conditions:

Using the Liquibase Secure (Pro) AWS Extension

Summary

Liquibase Secure (Pro) AWS Extension integrates several AWS features, which include remote file access, secrets management, and flexible-schema NoSQL database management.

This document shows how to use the Liquibase Secure (Pro) AWS Extension to store PII in an S3 bucket instead of a Git repository and allow Liquibase to deploy scripts directly from the S3 bucket.

Problem Statement

Liquibase enables organizations to speed up their database deployments while enabling good database hygiene. Development teams can self-service their database changes by committing all their database scripts to source code management (SCM) systems. The challenge created by this process is that this automation forces data developers and business analysts to store their data files with Personally Identifiable Information (PII) or Protected Health Information (PHI) on SCM systems that do not meet the security standards their organization has for storing this sensitive data. This solution allows them to store these scripts in an environment with more rigorous security certifications, such as Amazon FedRAMP.

Storing SQL scripts (which contain PIII or PHI) on SCM systems that do not meet the security standards of their company for storing this sensitive data. This solution provides a way for them to store these scripts in an environment with more rigorous security certifications, such as the Federal Risk and Authorization Management Program FedRAMP.

Setup

Prerequisite

You will need to set up your AWS account and give the user programmatic permissions to access files in the AWS S3 bucket.

AWS S3 Setup

We will store our PII data in a CSV file, which can be uploaded to an S3 bucket. Notice the path to the S3 bucket. This will need to be passed to Liquibase in this notation: s3://amalik-s3-extension-demo/customer_data.

Figure 1: AWS S3 bucket showing CSV file with PII data

Liquibase Workspace Setup

In Liquibase, to access files directly from S3, we will need to use the Liquibase Secure (Pro) AWS Extension jar file. This extension is included in Liquibase Secure (Pro) version 4.33.0 or higher, so there is no need to download it separately. If you are on an older version of Liquibase Secure (Pro), then follow the steps in Install the Liquibase Secure (Pro) AWS Extension.

Next, you will need to configure your Liquibase workspace to connect to your AWS S3 bucket. To do this, you will need to set up AWS configuration. This can be done in one of two ways:

Set AWS-specific environment variables:

AWS_REGION=us-east-1
AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
AWS_SESSION_TOKEN=IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZVERYLONGSTRINGEXAMPLE

Setting up a configuration in ~/.aws/credentials:

[default]
aws_access_key_id=AKIAIOSFODNN7EXAMPLE
aws_secret_access_key=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
aws_session_token=IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZ2luX2IQoJb3JpZVERYLONGSTRINGEXAMPLE

Liquibase Changelog Setup

With the above setup, Liquibase is able to access your AWS account, and if correct permissions are given, Liquibase should be able to access files in your AWS S3 bucket.

In order to point to any file in your AWS S3 bucket, use the s3://<bucket-name> notation. This path can be specified in Liquibase's searchPath property. Multiple paths can be combined in a comma-separated string, e.g., .,sql/100_ddl,s3://amalik-s3-extension-demo/customer_data

Option	Syntax
Liquibase properties file (defaults file)	`liquibase.searchPath: <string>`
Liquibase Environment Variables	`LIQUIBASE_SEARCH_PATH=<string>`
Flow file	`globalArgs: { search-path: "<string>" }`
CLI argument	`--search-path=<string>`

Take a look at the sample repository here. If you look at the changelog.xml file, you will see that there are two files referenced in the changelog:

01_customers.sql - This is the DDL script located in the repository in sql/01_ddl directory.
customer.csv - This file is not in the repository. Liquibase finds this file in the S3 bucket because the S3 bucket's path is configured in the search path. The configuration of the search path is shown in Figure 3.

An example of how to set up Liquibase workspace is shown in the runme.sh script.

Figure 3: Setup of AWS and Liquibase environment variables

Deploying PII Data and log-level

When deploying PII data from Liquibase, pay particular attention to the log-level property in Liquibase. Avoid using log-level=FINE. This is the most verbose log level, and it prints to the console the SQL that is being deployed.

Instead, use log-level=INFO which will provide additional informational messages.

Related to