When Database Sanitization Locked Real Users Out of Acceptance Testing
At 10:17, minutes into client acceptance testing, a production user entered the password they used every day. The lower environment rejected it. The database copy was clean. The updates had run. The caches had rebuilt. Yet our Drupal database sanitization step had locked the client out. Drush had randomized the copied passwords and rewritten the email addresses. We removed the blanket sanitizer from the post-copy workflow. We kept the maintenance steps and restored the user data from the August 24 copy. Access returned when the environment again matched the testing contract.
TL;DR
A generic sanitizer can defeat acceptance testing by changing the identities testers need. After copying production data, run database updates and cache rebuilds separately. Preserve only the approved identities required by the test, transform unrelated data, and treat retained password hashes as sensitive credentials that require explicit risk acceptance.
The security convention sounded sensible: production data should not survive intact outside production. We implemented it with one command, drush sql:sanitize, after each database copy. The command was easy to review and explain. However, it conflicted with the reason this environment existed.
A lower environment does not become safe because its data passed through a command named sanitize. Safety depends on a defined purpose, constrained exposure, and deliberate treatment of the risks that remain.
Why Drupal database sanitization broke the test
The managed hosting workflow copied the production database into development and staging. A post-copy script then performed three operations:
#!/usr/bin/env bash
set -euo pipefail
cd /var/www/app/web
vendor/bin/drush sql:sanitize -y
vendor/bin/drush updatedb -y
vendor/bin/drush cache:rebuildThe last two commands were maintenance. updatedb applied pending Drupal database updates. cache:rebuild cleared derived state that could be stale after the copy. The first command changed application data.
Drush's default sanitization targets Drupal user records. Here, it randomized user passwords. It also rewrote email addresses into the form user+<uid>@localhost.localdomain. Those changes protected credentials and stopped messages from reaching production addresses. They also removed both routes real users needed for acceptance testing.
The copy was internally consistent. The application started, and automated smoke checks passed. It still failed its purpose because the required test identities no longer worked.
The quiet trap was grouping unlike post-copy commands under the label of cleanup. Database updates and cache rebuilds prepare copied data for deployed code. Blanket sanitization rewrites the data model. Putting them in one hook concealed a policy decision inside routine plumbing.
Start with the testing contract
Acceptance testing had a concrete requirement. Selected production users needed to sign in with known credentials and exercise workflows as themselves. Production-equivalent authentication was indispensable. Authentication, permissions, ownership, historical content, and account-specific state were all part of what the client had to verify. Synthetic accounts could not establish that those copied identities behaved correctly.
I now define a lower environment by its testing contract before choosing its data controls. That makes access part of the deliverable. I also use this approach when I treat access as the first field-engineering deliverable. For this acceptance environment, the contract was:
| Decision | Acceptance environment answer |
|---|---|
| Required identities | Approved client testers using existing identities |
| Required behavior | Authentication, permissions, and realistic content workflows |
| Prohibited exposure | Unapproved access and outbound production communication |
Identity fields are coupled. A username may be harmless alone. However, its password hash determines whether the copied identity remains usable. The email address may support login, password reset, notifications, or several of those paths. Changing one field can disable tests elsewhere.
A blanket command cannot infer those dependencies. It sees columns and applies defaults. The actual contract usually lives in tickets and handover notes. It also lives in the memories of the people called when testing stops.
The smaller post-copy workflow
The fix was intentionally plain. We removed only sql:sanitize. We retained the operations needed to make the copied database compatible with the deployed application:
#!/usr/bin/env bash
set -euo pipefail
cd /var/www/app/web
vendor/bin/drush updatedb -y
vendor/bin/drush cache:rebuildThis separation makes the policy visible. The script no longer presents sanitization as a mechanical phase of each copy. Any targeted transformation added later has to state what it changes and why.
After changing the workflow, we restored the affected user data from the August 24 database copy. Then we asked the testers to try again. Changing the next post-copy hook could not repair credentials already overwritten in the current database.
For repeatable verification, the deployment operator can inspect Drupal's database state. They can also confirm that a known tester account still exists:
cd /var/www/app/web
vendor/bin/drush updatedb:status
vendor/bin/drush user:information acceptance.tester
vendor/bin/drush status --fields=bootstrap,db-status,drupal-versionVerify the password through the application login. Printing or resetting it merely to prove the copy worked creates a separate credential risk.
Important
Preserving credentials is not equivalent to leaving the environment unchanged. Outbound email, public reachability, third-party integrations, and administrative access still need explicit controls.
Options we rejected
We considered keeping the blanket sanitizer and resetting tester passwords afterward. That would create a second credential-distribution process. It would also prevent production-equivalent authentication and leave testers dependent on someone with command-line access. The policy problem would return as recurring support work.
We also considered using only synthetic users. That fits many development tasks. However, it weakens acceptance testing when permissions, ownership, historical content, or account-specific state are under review. A synthetic administrator can prove that a page renders. It cannot prove that a particular customer role sees the correct operations on copied content.
Selective sanitization offers a better long-term boundary. Unrelated fields can be transformed while approved identities remain usable.
UPDATE users_field_data
SET mail = CONCAT('user+', uid, '@example.invalid'),
init = CONCAT('user+', uid, '@example.invalid')
WHERE name NOT IN ('acceptance.tester', 'release.reviewer')
AND uid > 1;This SQL is valid for a standard Drupal user table, but it carries a maintenance cost. User data may also live in profile entities, contributed modules, custom tables, revisions, logs, and external systems. An allowlist can become stale when the tester group changes. This is another case where verification schemas can drift from runtime reality. The query therefore belongs in version control, with an owner and a verification check. It does not belong in an operator's shell history.
Credential risk does not stop at the perimeter
Removing the blanket sanitizer increases the sensitivity of the lower environment. Retained password hashes create a specific failure mode. If someone steals the database, they can attempt offline cracking without interacting with Drupal. A VPN, access logs, and strong administrator authentication reduce the chance of unauthorized access to the running environment. They do not protect hashes after database theft.
That distinction changes the acceptable scope. Preserving the two approved tester hashes in the example differs materially from retaining the full production credential set. The narrowest workable allowlist reduces exposure. It does not eliminate the offline-cracking risk for those accounts. The exception therefore needs explicit approval, limited duration, and an owner.
Exact production hashes belong in acceptance only when the test genuinely requires production-equivalent authentication. Otherwise, safer options include temporary federated access or restoring only narrowly scoped tester accounts. Controlled password resets are another option. Each changes what the test proves. The choice belongs in the testing contract, not in a generic copy script.
The remaining controls still matter. Network restrictions constrain access to the environment. Outbound mail must be blocked or rerouted at the transport layer. Production API credentials must be replaced. Copy permissions, retention periods, administrative membership, and access logs also need named owners and regular review. These measures limit exposure and misuse. None should be presented as mitigation for offline cracking of a stolen hash database.
Email needs its own boundary. Preserving an address for login does not authorize the application to send arbitrary messages to it. I prefer blocking or rerouting delivery in the mail transport. That controls the hazardous behavior directly without corrupting identity fields.
A practical environment rule
| Environment | Required identity fidelity | Permitted transformations | Required safeguards |
|---|---|---|---|
| Development | Synthetic identities are normally sufficient | Replace credentials, email addresses, personal data, and production integration secrets | Isolate integrations and prevent outbound production communication |
| Acceptance | Preserve only approved identities needed to test authentication, permissions, ownership, or account state | Transform unrelated accounts and data; replace production integration secrets | Accept the retained-hash risk explicitly, restrict access, control mail, limit copy retention, and verify the allowlist |
| Staging | Set fidelity from the specific release test, not the environment name | Apply field-level rules that preserve only required behavior | Document exceptions, constrain access, isolate integrations, and review retained data after each copy |
The practical rule for Drupal database sanitization is simple: preserve only the identity fidelity the test must prove. Make each retained credential an explicit security exception, not an accidental consequence of copying production.
FAQ
Why does Drush sql:sanitize prevent users from logging in?
Its default user sanitization randomizes passwords. Existing production credentials therefore no longer match the copied Drupal accounts.
Why are Drupal user email addresses changed during sanitization?
Changing addresses reduces exposure of personal data and prevents lower environments from emailing production users. It can also break email-based login, password resets, and notification testing.
Should production passwords be preserved in a test environment?
Only when the testing contract requires those identities and the additional risk is accepted. Restrict access, isolate integrations, control outbound email, and document which accounts remain usable.
What should run after a Drupal database copy?
Run the database updates and cache rebuilds required by the deployed code. Apply sanitization separately, field by field, according to the environment's purpose.
How should I test a Drupal database sanitization policy?
Verify representative logins, roles, content ownership, email routing, third-party credentials, and access restrictions after every copy. A sanitizer is correct only when the protected data and the required tests both behave as intended.