Add option to sanitize db on export or restore using drush sql-sanitize and/or paranoia sanitize whitelist

I'd need that functionality, too. Where in the process/code could we start to help implementing this?

I would also be really interested in this functionality... has any further thought been done on this?

For reference: http://drupalscout.com/knowledge-base/creating-sanitized-drupal-database...

Ping on this :)

It would be good to provide a hook that requests some metadata about tables to indicate if they should not be backed up for dev sites (e.g. that contain sensitive data). Then bm could know not to include them in bacups.

Also something similar to delete variable names to delete from variable tables.

Drush provides the sql-sanitize command which operates on the current database. Is there any way to accomplish a similar result while the data is being dumped out of mysql?

Drush sql-sanitize command can be used to sanitize the database after it is restored.

But I think that what you really need is a sanitized dump file, that is, the option in the backup operation for generating a dump file with sanitized usernames, hashed passwords and emails.

This would require extra code in the _dump_table_data_sql_to_file() function to specifically handle the 'users' table, obfuscating the sensitive data before dumping it to the file. Furthermore, this will not be compatible with the backup CLI option (that is, using directly mysqldump command).

Perhaps it is out of scope.

If you are required to give a dump file without sensitive data, my advice is to clone your production database, run 'drush sql-sanitize' over the clone, and backup the sanitized cloned database.

Note that the 'drush sql-sanitize' command has an available hook to include in the sanitize operation any other table with sensitive data from your database.

If backup and migrate adds support for this it would be ideal if hook_drush_sql_sync_sanitize() was invoked. It would be annoying if contrib modules had to implement a different sanitize hook for backup and migrate as well as for drush.

Stumbled upon this looking for a script to sanitize a database dump. WRT #6, I think it would be a good idea if the hook used the same signature as the drush hook. It might even say so in the documentation. I don't think it's a great idea to "hijack" another component's api, though. It would be very nice if it were trivial to implement either hook by invoking the other and passing through the data. Using the *same* hook is probably one step too far.

This sounds like a really complex functionality. Wouldn't there be an easier way to secure the backup destination location? If this is really a desired feature, it would be nice to have some more current responses, and we should re-categorize this as a D8 request since all the current development is happening in 8.x-4.x-dev right now.

Closing after more than two weeks with no activity.

I disagreed with closing this at the time but didn't feel passionate enough about it.

Given #2920311: Check backup directory permissions / web accessibility (D7) it seems worth reopening this issue.

Thanks for the post but that issue is for D7, not D8 are you saying you want this to work on D8?

Hi @greggles,

Yes I agree with you on this and I didn’t knew such a feature request existed in the first place, I’d like to see this on D8 too and we’ll add it to the Roadmap.

It should eventually be on both branches, shouldn't it? Not sure how the maintainers care to get that done so I assumed more modern was better.

@greggles

As I understand it features for D7 version are frozen now.

Can you elaborate on what you want this to do exactly? The more detailed the better.

Thanks
Daniel

Sure, title now reflects what it should do.

There might be some overlap between this and discussions elsewhere about GDPR (#2848974: Privacy Concerns as GDPR Compliance).

@Damien you are reffering to comment #9 from the related thread above correct ?

Correct.

I'd like this, and I'd like it for D7 where there must surely be more B&M downloads than for D8 (?). A pity if features for the more popular version are frozen.

A few comments:
1. Backup and migrate is a useful way of sharing a copy of a site when new devs are brought in, and sometimes that copy must be sanitized of user data and API keys.
2. Securing backups is easy to say, and more overhead to do (depending of course on the level of security) so I don't accept the logic that there is no point in protecting the fools who do not secure their backups adequately; besides, although I assume NodeSquirrel is un-hackable, I'd still rather use it with a sanitized db than an unsanitized one.
3. the issue that sending private data to NodeSquirrel might breach European data rules.

The risks of insecure backups turned out to be even more sensitive for those who use simple_password_reset module and apply only security updates, because unless you also apply non-security updates it dumps plain text passwords in the database https://www.drupal.org/node/2367529. However, that is really off-topic, just something I was burnt with recently.

@John_B thanks for your comments. You might not be aware that the original creator of Backup and Migrate has left the project at present, and development resources are extremely limited right now. That is why features for D7 are frozen because nearly all the available work is being put toward a stable D8 release. Also, NodeSquirrel isn't supported in D8 nor are there any plans to make it supported in D8 of which I am aware, since NodeSquirrel was also created by the original creator of Backup and Migrate. For that reason, it is unlikely that anything related to NodeSquirrel would be considered as part of the work plan for development toward the stable D8 release.

Thanks for your other comments on secure storage. Again, this is probably a feature that will be put on the back burner for a while due to limited development resources. If you are able and interested in helping to code for this module, please be in touch with @Alex Andrascu, @DamienMcKenna or @ikit-claw, all of whom have commented in this thread and whom are active in current development.

I started a generic approach that can be used with any tool:
* machbarmacher/gdpr-dump: A drop-in replacement for mysqldump that optionally sanitizes DB fields for better GDPR conformity.
* Discussion: Plan: Find a way to sanitize a DB-Dump before transferring it (GDPR) · Issue #3509 · drush-ops/drush

@axel.rutz nice ! Let us know if you need any support from the B&M module. We're happy to chip in if we can.

@alex: Cool you like it!

For an API to recognize the sensitive DB tables and columns, see the related issue.

My vision is that B&M can simply use it via a checkbox and minimal, so the effort needed here is minimal.

OTOH this strongly needs more heads, hearts and hands to work on, as i pumped quite some effort into building the prototype and won't be sanely able to go on like this. So feel free to comment on the drush issue.

There are more discussions about GDPR here https://www.drupal.org/project/drupal_gdpr_team

Moving 8.x-4.x issues to 5.0.x because the older branch is no longer supported.