Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Not sure whether this is out of scope for this module ...
When moving a site's database from live to staging/local, it would be handy to have an option to sanitize the db. I'm mostly thinking about the option to change the usernames/passwords of the site's users, in order to avoid eg. mass e-mail accidents, and security reasons.
Is this something you would consider adding to this module?
Comments
Comment #1
danielnolde CreditAttribution: danielnolde commentedI'd need that functionality, too. Where in the process/code could we start to help implementing this?
Comment #1.0
brunodboClarifying description.
Comment #2
JordanMagnuson CreditAttribution: JordanMagnuson commentedI 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...
Comment #3
gregglesPing 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.
Comment #4
markdorisonDrush 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?
Comment #5
interdruper CreditAttribution: interdruper commentedDrush 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.
Comment #6
mstrelan CreditAttribution: mstrelan commentedIf 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.
Comment #7
eelkeblokStumbled 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.
Comment #8
couturier CreditAttribution: couturier as a volunteer commentedThis 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.
Comment #9
couturier CreditAttribution: couturier as a volunteer commentedClosing after more than two weeks with no activity.
Comment #10
gregglesI 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.
Comment #11
ikit-claw CreditAttribution: ikit-claw as a volunteer commentedThanks for the post but that issue is for D7, not D8 are you saying you want this to work on D8?
Comment #12
Alex Andrascu CreditAttribution: Alex Andrascu commentedHi @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.
Comment #13
gregglesIt 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.
Comment #14
ikit-claw CreditAttribution: ikit-claw as a volunteer commented@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
Comment #15
gregglesSure, title now reflects what it should do.
Comment #16
DamienMcKennaThere might be some overlap between this and discussions elsewhere about GDPR (#2848974: Privacy Concerns as GDPR Compliance).
Comment #17
Alex Andrascu CreditAttribution: Alex Andrascu commented@Damien you are reffering to comment #9 from the related thread above correct ?
Comment #18
DamienMcKennaCorrect.
Comment #19
John_B CreditAttribution: John_B commentedI'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.
Comment #20
couturier CreditAttribution: couturier as a volunteer commented@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.
Comment #21
geek-merlinI 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
Comment #22
Alex Andrascu CreditAttribution: Alex Andrascu commented@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.
Comment #23
geek-merlin@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.
Comment #24
mgiffordThere are more discussions about GDPR here https://www.drupal.org/project/drupal_gdpr_team
Comment #25
mgiffordComment #26
DamienMcKennaMoving 8.x-4.x issues to 5.0.x because the older branch is no longer supported.