- Manually
- Using a script with Django and `psql`
Assumptions:
usersis the table where all of your PII is stored in thestagingdevelopment database.databaseis the relationship name for the PostgreSQL service.
-
Retrieve service credentials from the service environment variables to use the
psqlcommand interface. Export these values to a.environmentfile or include them directly in the sanitization script..environmentCopyAsk AI# Pull credentials from the service environment variables. export DB_USER="${DATABASE_USERNAME}" export DB_HOST="${DATABASE_HOST}" export DB_PORT="${DATABASE_PORT}" export DB_PASS="${DATABASE_PASSWORD}" -
Create an executable sanitizing script by running the following command:
CopyAsk AI
touch sanitize.sh && chmod +x sanitize.sh -
Make the script sanitize environments with an environment type
other than
production. The following example runs only in preview environments and sanitizes thedisplay_nameandemailcolumns of theuserstable. Adjust the details to fit your data.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.shCopyAsk AI#!/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 fisanitize.shCopyAsk AI#!/usr/bin/env bash if [ "$PLATFORM_ENVIRONMENT_TYPE" != production ] && [ ! -f <VariableBlock name="MOUNT_PATH" />/is_sanitized ]; then # Sanitize data touch <VariableBlock name="MOUNT_PATH" />/is_sanitized fi -
Update the deploy hook to run your script on each deploy.
.upsun/config.yamlCopyAsk AI
applications: myapp: hooks: build: ... deploy: | python manage.py migrate bash sanitize.sh -
Commit your changes by running the following command:
Push the changes toCopyAsk AI
git add .environment sanitize.sh .upsun/config.yaml&& git commit -m "Add sanitization."stagingand verify that environment’s database was sanitized. Once merged to production, all data from future preview environments are sanitized on environment creation.
What’s next
If your database contains a lot of data, consider using theREINDEX statement to help improve performance.