Sanitizing databases: PostgreSQL and Django

Assumptions:

• users is the table where all of your PII is stored in the staging development database.
• staging is an exact copy of your production database.

1. Connect to the staging database by running platform sql -e staging.

2. Display all fields from your users table, to select which ones need to be redacted. Run the following query:

   main=> SELECT * FROM users;

   You see output like the following:

   id   |                user_email               |     display_name
   -----+-----------------------------------------+-----------------------
   3501 | daniel02@yourcompany.com                | Jason Brown
   3502 | ismith@kim.com                          | Sandra Griffin
   3503 | olee@coleman-rodriguez.com              | Miss Christine Morgan

3. Change the fields where PII is contained with the UPDATE statement. For example, to change the display name of users with an email address not in your company’s domain to a random value, run the following query:

   UPDATE users
   SET display_name==substring(md5(display_name||'$PLATFORM_PROJECT_ENTROPY') for 8);
   WHERE email NOT LIKE '%@yourcompany%'

   Adapt and run that query for all fields that you need to sanitize. If you modify fields that you shouldn’t alter, you can restore them from the dump you took in step 1.

   You can create a script to automate the sanitization process to be run automatically on each new deployment. Once you have a working script, add your script to sanitize the database to a deploy hook:

   .platform.app.yaml

   hooks:
       deploy: |
   
           # ...
   
           cd /app/public
           if [ "$PLATFORM_ENVIRONMENT_TYPE" = production ]; then
               # Do whatever you want on the production site.
           else
               # The sanitization of the database should happen here (since it's non-production)
               sanitize_the_database.sh
           fi

Assumptions:

• users is the table where all of your PII is stored in the staging development database.
• database is the relationship name for the PostgreSQL service.

Set up a script by following these steps:

1. Retrieve service credentials from the PLATFORM_RELATIONSHIPS environment variable to use the psql command interface. Export these values to a .environment file or include them directly in the sanitization script.

   .environment

   # Pull credentials from the PLATFORM_RELATIONSHIPS environment variable.
   DB_USER=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode | jq -r '.database[0].username')
   DB_HOST=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode | jq -r '.database[0].host')
   DB_PORT=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode | jq -r '.database[0].port')
   DB_PASS=$(echo $PLATFORM_RELATIONSHIPS | base64 --decode | jq -r '.database[0].password')

2. Create an executable sanitizing script by running the following command:

   touch sanitize.sh && chmod +x sanitize.sh

3. Make the script sanitize environments with an environment type other than production.

   The following example runs only in preview environments and sanitizes the display_name and email columns of the users table. Adjust the details to fit your data.

   sanitize.sh

   #!/usr/bin/env bash
   
   if [ "$PLATFORM_ENVIRONMENT_TYPE" != production ]; then
       # Sanitize data
       PGPASSWORD=$DB_PASS psql -c "UPDATE users SET display_name=substring(md5(display_name||'$PLATFORM_PROJECT_ENTROPY') for 8);" -U $DB_USER -h $DB_HOST -p $DB_PORT
       PGPASSWORD=$DB_PASS psql -c "UPDATE users SET email=substring(md5(email||'$PLATFORM_PROJECT_ENTROPY') for 8);" -U $DB_USER -h $DB_HOST -p $DB_PORT
   fi

   To sanitize only on the initial deploy and not all future deploys, on sanitization create a file on a mount. Then add a check for the file as in the following example:

   sanitize.sh

   #!/usr/bin/env bash
   
   if [ "$PLATFORM_ENVIRONMENT_TYPE" != production ] && [ ! -f MOUNT_PATH/is_sanitized ]; then
       # Sanitize data
       touch MOUNT_PATH/is_sanitized
   fi

4. Update the deploy hook to run your script on each deploy.

   .platform.app.yaml

   hooks:
     build: ...
     deploy: |
       python manage.py migrate
       bash sanitize.sh    

5. Commit your changes by running the following command:

   git add .environment sanitize.sh .platform.app.yaml&& git commit -m "Add sanitization."

   Push the changes to staging and verify that environment’s database was sanitized. Once merged to production, all data from future preview environments are sanitized on environment creation.