rake task for dumping, restoring and anonymizing user data #1013

dkuku · 2019-01-05T14:42:45Z

Created rake task for anonymizing user data. fixes #987

Description

Also for dumping and restoring development database
Currently to use it you need to dump your data rake db:dump and backup the file db/backend.dump
Then you can run rake db:anonymize_user which changes current user table
Now you can run rake db:dump again to dump anonymized data
to restore the original file move it to db/ and run rake db/restore
Waiting for suggestions to extend this.

Motivation and Context

To be in compliace with current European law we can't get developers access to user data - this rake task annonymizes the user table

How Has This Been Tested?

Tested locally on seeded data - might need some adjustments, I annonymized only the data I currently have access to

alternative solution using shell and temporary database

http://www.michaelkrenz.de/2012/08/05/how-to-anonymize-data-in-a-postgresql-database/

dump data

pg_dump database > original_datadump

create temp database

createdb tempDB

import data to temp

psql tempDB < ./original_datadump

run anonymize script - on the botttom

psql tempDB < ./anonymize_db.sql

dump anonymized data

pg_dump tempDB > anon_dump.sql

delete temporary table

dropdb tempDB

/*delete field content
    update users set encrypted_password = NULL;
    */
    update users set encrypted_password = 'asdf';

/*anonymize emails, lat and lng*/
    update users set email = 'user' || id || '@rundfunk.com';
    update users set latitude = 51 + 0.001 * id;
    update users set longitude = 22 - 0.001 * id;

/*anonymize other data*/
    update city set
    city = translate(message, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZC', 'aaaaaaaaaaaaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAA');

roschaefer

@dkuku looks good! Just the usual nitpickings. I'm gonna try it out on my machine now and send you a an anonymized dump.

One question: Don't you think that keeping the ids will allow for de-anonymization? Maybe we should shuffle the user ids?

backend/lib/tasks/db.rake

roschaefer · 2019-01-09T16:39:29Z

backend/lib/tasks/db.rake

+
+        user.email = "user#{user.id}@rundfunk.com"
+        user.save!
+      end


@dkuku you can probably update all the users in one sql statement: https://apidock.com/rails/ActiveRecord/Base/update_all/class

User.update_all(:password, 'xxxxxx') User.update_all(:latitute, '50 + 0.001 * users.id') # ... etc

roschaefer · 2019-01-09T16:47:13Z

@dkuku there are very similar rake tasks for dumping and restoring already 😲

roschaefer · 2019-01-09T17:00:46Z

@dkuku okay 👍 I ran the task on my machine. The dump task seems to be a duplicate of the dump task from https://github.com/sgruhier/capistrano-db-tasks. Unfortunately their dump task fails:

robert@e480 ~/D/r/backend> bin/rails db:data:dump
rails aborted!
TypeError: no implicit conversion of Pathname into String
/home/robert/.gem/ruby/2.5.1/gems/activerecord-5.1.6/lib/active_record/tasks/postgresql_database_tasks.rb:108:in `system'
/home/robert/.gem/ruby/2.5.1/gems/activerecord-5.1.6/lib/active_record/tasks/postgresql_database_tasks.rb:108:in `run_cmd'
/home/robert/.gem/ruby/2.5.1/gems/chrono_model-0.12.1/lib/active_record/tasks/chronomodel_database_tasks.rb:30:in `data_dump'
/home/robert/.gem/ruby/2.5.1/gems/chrono_model-0.12.1/lib/chrono_model/railtie.rb:31:in `block (2 levels) in <class:Railtie>'
/home/robert/.gem/ruby/2.5.1/gems/railties-5.1.6/lib/rails/commands/rake/rake_command.rb:21:in `block in perform'
/home/robert/.gem/ruby/2.5.1/gems/railties-5.1.6/lib/rails/commands/rake/rake_command.rb:18:in `perform'
/home/robert/.gem/ruby/2.5.1/gems/railties-5.1.6/lib/rails/command.rb:46:in `invoke'
/home/robert/.gem/ruby/2.5.1/gems/railties-5.1.6/lib/rails/commands.rb:16:in `<top (required)>'
bin/rails:4:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:data:dump
(See full trace by running task with --trace)
robert@e480 ~/D/r/backend> ruby -v
ruby 2.5.1p57 (2018-03-29 revision 63029) [x86_64-linux]

Apparently IFAD's chronomodel does not like the db tasks? 😢

@dkuku shall I send you the dump via Slack?

dkuku · 2019-01-09T17:11:27Z

I had no idea how to do that but now I think that the id's can be dumped to an array, shuffled and assigned to the users. What do you think?

…

On Wed, 9 Jan 2019, 17:08 Robert Schäfer ***@***.*** wrote: ***@***.**** requested changes on this pull request. @dkuku <https://github.com/dkuku> looks good! Just the usual nitpickings. I'm gonna try it out on my machine now and send you a an anonymized dump. One question: Don't you think that keeping the ids will allow for de-anonymization? Maybe we should shuffle the user ids? ------------------------------ In backend/lib/tasks/db.rake <#1013 (comment)> : > @@ -0,0 +1,54 @@ +namespace :db do + desc 'replace user sensitive data with placeholders' + task anonymize_user: :environment do + if ENV['RAILS_ENV'] == 'production' + puts 'You do not want to anonymize production data' + else + puts 'Anonymizing user data' + + User.all.each do |user| ⬇️ Suggested change - User.all.each do |user| + User.find_each do |user| ------------------------------ In backend/lib/tasks/db.rake <#1013 (comment)> : > @@ -0,0 +1,54 @@ +namespace :db do + desc 'replace user sensitive data with placeholders' + task anonymize_user: :environment do + if ENV['RAILS_ENV'] == 'production' + puts 'You do not want to anonymize production data' + else + puts 'Anonymizing user data' + + User.all.each do |user| + user.encrypted_password = user.encrypted_password.truncate(8) + user.latitude = 50 + 0.001 * user.id + user.longitude = 10 - 0.001 * user.id + user.city = "city#{user.id}" + + user.email = ***@***.***" ⬇️ Suggested change - user.email = ***@***.***" + user.email = ***@***.***" The domain rundfunk.com exists. The domain http://example.org/ is intended to be used for such purposes. ------------------------------ In backend/lib/tasks/db.rake <#1013 (comment)> : > + desc 'replace user sensitive data with placeholders' + task anonymize_user: :environment do + if ENV['RAILS_ENV'] == 'production' + puts 'You do not want to anonymize production data' + else + puts 'Anonymizing user data' + + User.all.each do |user| + user.encrypted_password = user.encrypted_password.truncate(8) + user.latitude = 50 + 0.001 * user.id + user.longitude = 10 - 0.001 * user.id + user.city = "city#{user.id}" + + user.email = ***@***.***" + user.save! + end @dkuku <https://github.com/dkuku> you can probably update all the users in one sql statement: https://apidock.com/rails/ActiveRecord/Base/update_all/class User.update_all(:password, 'xxxxxx') User.update_all(:latitute, '50 + 0.001 * users.id') # ... etc — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1013 (review)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3L8xHtPEQwBOaFkHcWfelgq3s6IwNAks5vBiImgaJpZM4ZxNS5> .

roschaefer · 2019-01-09T17:50:10Z

@dkuku yes, but all foreign keys would need to be shuffled, too. The correct way to say is "It breaks referential integrity", from here.

I think it does not need to be done now.

dkuku · 2019-01-09T17:57:35Z

@roschaefer you can send it to me via slack.
I'll look at the data shuffling thingy too. I found a gem that might solve this.
https://github.com/sunitparekh/data-anonymization

Co-Authored-By: dkuku <[email protected]>

roschaefer · 2019-01-10T11:08:06Z

backend/lib/tasks/db.rake

-
+      @user_ids = User.ids.shuffle
+      Broadcast.find_each do |broadcast|
+        broadcast.creator_id = @user_ids.sample


Hm, I would not do that because it gets confusing. E.g. @ciremoussadia is working on a PR where we update the user role if you create a broadcast.

I don't know how to do proper anonymization 😆 maybe you can do some research if the user id itself is a means of de-anonymization and if yes, what are the counter-measures?

Maybe it's not necessary after all? 🤷‍♂️

roschaefer · 2019-01-10T11:13:37Z

@dkuku that gem looks OK to me on the first glimpse. Last commit not too old, a couple of stars and focused use case. Does it do sth. to the id? It does not seem so, does it?

dkuku · 2019-01-10T11:17:30Z

when playing with it yesterday I found that we really want to anonymize the impressions table so it's not possible to recover what have you voted for. But the foreign key constrains need to be removed temporary and I need to find where it is stored-probably a join table because I can't update Impression.first.creator_id even if I can see it in the rails console.

…

On Thu, 10 Jan 2019, 11:10 Robert Schäfer ***@***.*** wrote: ***@***.**** commented on this pull request. ------------------------------ In backend/lib/tasks/db.rake <#1013 (comment)> : > @@ -5,16 +5,25 @@ namespace :db do puts 'You do not want to anonymize production data' else puts 'Anonymizing user data' - + @user_ids = User.ids.shuffle + Broadcast.find_each do |broadcast| + broadcast.creator_id = @user_ids.sample I don't know how to do proper anonymization 😆 maybe you can do some research if the user id *itself* is a means of de-anonymization and if yes, what are the counter-measures? Maybe it's not necessary after all? 🤷‍♂️ — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1013 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA3L8_xMjCHVBkhp55QMc0XR1-BFl_mSks5vBx-XgaJpZM4ZxNS5> .

roschaefer · 2019-01-10T11:20:35Z

@dkuku the foreign key is constraint comes from the database migrations. Ugh, good luck in getting rid of them. ~~Maybe postgres allows to dump the data without foreign key constraints?~~ that will not help you because you need to get rid of the constraints earlier

roschaefer · 2019-01-16T18:56:38Z

@dkuku could you please: git fetch and git merge origin/master? Given that origin is the name of this remote.

roschaefer · 2019-01-23T18:38:58Z

We could namespace the rake tasks db:anonymized:dump no?

dkuku added 2 commits January 5, 2019 14:24

rake task for dumping, restoring and anonymizing user data

5449b8f

corrected CI offenses

e6ca634

roschaefer requested changes Jan 9, 2019

View reviewed changes

roschaefer and others added 5 commits January 9, 2019 19:02

Update backend/lib/tasks/db.rake

47a1481

Co-Authored-By: dkuku <[email protected]>

Update backend/lib/tasks/db.rake

baa8e0c

Co-Authored-By: dkuku <[email protected]>

Anonymize Broadcast.creator_id

d7a700b

Remove unused code

0d68f00

Merge upstream

5543e89

roschaefer reviewed Jan 10, 2019

View reviewed changes

roschaefer force-pushed the master branch from f9505ad to acaa587 Compare September 11, 2020 22:13

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

rake task for dumping, restoring and anonymizing user data #1013

rake task for dumping, restoring and anonymizing user data #1013

dkuku commented Jan 5, 2019 •

edited

Loading

roschaefer left a comment

roschaefer Jan 9, 2019

roschaefer commented Jan 9, 2019

roschaefer commented Jan 9, 2019

dkuku commented Jan 9, 2019 via email

roschaefer commented Jan 9, 2019

dkuku commented Jan 9, 2019

roschaefer Jan 10, 2019

roschaefer Jan 10, 2019

roschaefer commented Jan 10, 2019

dkuku commented Jan 10, 2019 via email

roschaefer commented Jan 10, 2019 •

edited

Loading

roschaefer commented Jan 16, 2019

roschaefer commented Jan 23, 2019

rake task for dumping, restoring and anonymizing user data #1013

Are you sure you want to change the base?

rake task for dumping, restoring and anonymizing user data #1013

Conversation

dkuku commented Jan 5, 2019 • edited Loading

Description

Motivation and Context

How Has This Been Tested?

alternative solution using shell and temporary database

dump data

create temp database

import data to temp

run anonymize script - on the botttom

dump anonymized data

delete temporary table

roschaefer left a comment

Choose a reason for hiding this comment

roschaefer Jan 9, 2019

Choose a reason for hiding this comment

roschaefer commented Jan 9, 2019

roschaefer commented Jan 9, 2019

dkuku commented Jan 9, 2019 via email

roschaefer commented Jan 9, 2019

dkuku commented Jan 9, 2019

roschaefer Jan 10, 2019

Choose a reason for hiding this comment

roschaefer Jan 10, 2019

Choose a reason for hiding this comment

roschaefer commented Jan 10, 2019

dkuku commented Jan 10, 2019 via email

roschaefer commented Jan 10, 2019 • edited Loading

roschaefer commented Jan 16, 2019

roschaefer commented Jan 23, 2019

dkuku commented Jan 5, 2019 •

edited

Loading

roschaefer commented Jan 10, 2019 •

edited

Loading