# Sanitizing databases: PostgreSQL and Django


Databases of live websites often contain personally identifiable information (PII)
such as full names, mailing addresses, and phone numbers.
To ensure people reviewing code changes can't access information they shouldn't,
sanitize your databases of any PII that they may contain.

This example goes through the process for a PostgreSQL database using Django.

## Before you begin

You need:

- A project with a [PostgreSQL database](https://docs.upsun.com../../add-services/postgresql.md).
- A command interface installed:
  - If doing it manually, the [Upsun CLI](https://docs.upsun.com../../administration/cli.md).
  - Otherwise, make sure ``pqsl`` is installed in your environment.

This guide is about sanitizing PostgreSQL databases.

This guide doesn't address:

- Sanitizing NoSQL Databases (such as [MongoDB](https://docs.upsun.com../../add-services/mongodb.md))
- Input validation and input sanitization, which both help prevent security vulnerabilities

## Sanitize the database

Make sure that you only sanitize preview environments and **never** the production environment.
Otherwise you may lose most or even all of the relevant data stored in your database.

First, take a [database dump](https://docs.upsun.com../../add-services/postgresql.md#exporting-data) of your preview environment.
This is just a safety precaution.
Production data isn't altered.
To get a database dump, run the following command:
``upsun
 db:dump -e <DEVELOPMENT_ENVIRONMENT_NAME>
``.

You see output like the following:

```sql {}
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
```

 - Change the fields where PII is contained with the [UPDATE](https://mariadb.com/kb/en/update/).
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:

```sql {}
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](https://docs.upsun.com/environments/restore.md) 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 ](https://docs.upsun.com/create-apps/hooks/hooks-comparison.md#deploy-hook):

    .upsun/config.yaml

```yaml {}
applications:
    myapp:

        # ...

        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:

 - Retrieve service credentials from the [service environment variables](https://docs.upsun.com/development/variables.md#service-environment-variables) to use the ``psql`` command interface.
Export these values to a [.environment](https://docs.upsun.com/development/variables/set-variables.md#set-variables-via-script)
or include them directly in the sanitization script.

    .environment

```bash {}
# 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:

```bash {}
touch sanitize.sh && chmod +x sanitize.sh
```

 - Make the script sanitize environments with an [environment type](https://docs.upsun.com/administration/users.md#environment-type-roles)
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

```bash {}
#!/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](https://docs.upsun.com/create-apps/image-properties/mounts.md).
Then add a check for the file as in the following example:

    sanitize.sh

```bash {}
#!/usr/bin/env bash

if [ "$PLATFORM_ENVIRONMENT_TYPE" != production ] && [ ! -f <MOUNT_PATH>/is_sanitized ]; then
    # Sanitize data
    touch <MOUNT_PATH>/is_sanitized
fi
```

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

    .upsun/config.yaml

```yaml {}
applications:
  myapp:
    hooks:
      build: ...
      deploy: |
        python manage.py migrate
        bash sanitize.sh        
```

 - Commit your changes by running the following command:

```bash {}
git add .environment sanitize.sh .upsun/config.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.

## What's next

You learned how to remove sensitive data from a database.

To replace sensitive data that with other meaningful data, you can add a `faker` to the process.
A `faker` is a program that generates fake data that looks real.
Having meaningful PII-free data allows you to keep your current Q&A, external reviews, and other processes.
To add a faker, adapt your sanitizing queries to replace each value that contains PII with a new value generated by the faker.

You might also want to make sure that you [implement input validation](https://cheatsheetseries.owasp.org/cheatsheets/Input_Validation_Cheat_Sheet.md#goals-of-input-validation).

If your database contains a lot of data, consider using the [`REINDEX` statement](https://www.postgresql.org/docs/current/sql-reindex.md) to help improve performance.