11

Private information is confidential. However for developers to debug errors they need a copy of your data that's close enough to production to effectively debug. How can we redact, strip or remove confidential data from CiviCRM?

Two options come to mind. I could spend time creating anonymous test data that covers all the possible variants of our complex hierarchical contact relationship structure. The other option would be to go through a copy of production and anonymise it by overwriting private data with random strings. But we are talking here about some 9000 contacts. Working with API calls to anonymise could take hours, not really an option for a fast workflow.

Anyone with experience or ideas could share his thoughts on this subject?

  • I will poke my colleague who did some work on anonymising solutions – petednz - fuzion Mar 27 '17 at 19:26
  • see what happens when he gets poked! – petednz - fuzion Mar 28 '17 at 4:55
20

Some thoughts

Anonymizing data is hard work.

Eg: Would it be OK if your developer could de-anonymize your contacts by identifying from geolocated data? What about identifying by "metadata", if suburb data is left in? Does your developer need a DB with activities from inbound email left in? Has the situation improved in the decade since WIRED Why 'Anonymous' data sometimes isn't, or gotten worse? (It got worse!)

As you anticipated, generating real-looking fake data is much slower than garbage data, and that's one tradeoff. The other is that a CRM has data in places you might not think to look, so you are safer treating the data as "mostly anonymized" even when you're done. (See that Wired article!)

One approach, which may also speed up your testing, is to remove data unless you know it's needed (and only anonymize what you didn't already remove).

  • Not testing mailing reports? Truncate those huge mailing tables.
  • Not testing custom data? Great, that stuff is hard to anonymize! Drop it!
  • Not testing performance? Cull your test DB down to 500 contacts before anonymizing.

What you choose depends on how you need to use it, and what's OK for your testing. As you say, for debugging developers need a "near exact" copy of the live data ... but the more a test data set matches your site's profile, the less anonymous it will be.

Some starting points

  • anonymize extension (xurizaemon/org.civicrm.contrib.anonymize) aims to anonymize a DB. Offers two strategies: Faker (slow, generates real-looking names like "King, Lesley") and SQL (fast, generates names like "Tdfv, Fnedftd"). With recent updates, this is probably your best bet - but you'll be an early user, so please direct feedback to Github!
  • Michael McAndrew's POP library is the most featureful IMO, its goal seems to be to populate a site with fake data rather than anonymize an existing DB. See the blog post Populate your CiviCRM site with fake data. A similar example using nodejs is civi-charlatan.
  • anonymiser (systopia/de.systopia.anonymiser) is another CiviCRM anonymiser extension under recent development as of June 2018. I haven't checked this out (and Sean and I didn't find it when we started work on anonymizer).
  • redaction_tool (johnff/redaction_tool) is another tool to redact contact data (WIP)
  • civicrm-fake-data (on github) is a ruby script to replace existing data with fake data.
  • civicrmanonymisation is an SQL approach to anonymising a DB.
  • encryptDB.php was in CiviCRM core, but it was a poor starting point; MD5 is not encryption, so what changes it does make are reversible. It also targeted a limited number of fields, so your DB would still contain identifying data even if not deanonymised ...
  • civicrm_obfuscate_db.php (based on encryptDB.php above) is another script I wrote several years back. It generates a set of SQL commands to obfuscate your DB. It's SQL-based so relatively fast, generates incomprehensible names ("bggerns srrssbb"), and like all the above options is not a complete solution.
  • Another option would be to construct a test environment with test only data that simulates all situations you need to test, and keep expanding it as you identify new bugs. Let's be realistic, few CiviCRM sites have budget for that 😜

To recap: none of the above are complete working solutions for what you want, and the solution you need depends on your context. But please do contribute your experiences to the community - most of the above have a Github issue queue you could submit improvements / feedback to!

One particular sticking point for several of the above seems to be CiviCRM's "Database Logging" tables, which by default use a format which complicates sanitisation. If you're testing or using any of the above, you should definitely review whether those tables are being de-identified completely in the process.

  • 6
    <stands up and starts clapping> – Allen Hutchison Mar 28 '17 at 4:04
  • CRM-20346 just got closed. – petednz - fuzion Mar 28 '17 at 5:15
  • 2
    Chris Burgess, so much effort went into this answer. Really thankful for that! I will surely study the suggested options and give feedback to, in a way, "pay back" your effort. – ñull Mar 28 '17 at 10:35
  • Thanks! You aren't "nully", previously of the FSF are you? Guessing not, but if so, I'd love to pick your brains on something. – Chris Burgess Mar 28 '17 at 11:18
  • 2
    A word of warning - make sure that outgoing email is disabled! You don't want any of your CiviRules etc. being triggered and sending out emails. I saw a colleague who had CiviCRM WordPress Profile Sync installed, which meant that changing the email address in Civi changed the email in WordPress - which sent out a "your email address has been changed" message to everyone who had a login... – Jon G - Megaphone Tech Sep 25 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.