From a9d98d4b529e6dd4a4f978dd8facd4bf5a20861e Mon Sep 17 00:00:00 2001
From: Sheogorath
Date: Sat, 21 Mar 2020 16:07:14 +0100
Subject: Add fix for missing deletion of notes on user-deletion request

Depending on how the system was setup, this bug lead to keep user's data
around even after a successful deletion of user'S account. This patch
will make sure the missing database constraints are implemented and
missed out deletions are executed.

This bug was introduced to insufficent testing after implementing the
feature initially. It was well tested, using the app process itself, but
the migrations where missed out. I'm currently not sure, if there was
also a change in how sequelize handles cassaded deletion, since I'm
unter the impression that before switching to sequelize 5, this feature
has worked. But I haven't verified this.

No matter what, the cleanup process is rather straight forward and will
be invoked on migration, but can also be done manually using the new
`bin/cleanup` script.

This change will result in a release 1.6.1.

Signed-off-by: Sheogorath <sheogorath@shivering-isles.com>
---
 bin/cleanup                                        | 56 ++++++++++++++++++++
 .../20200321153000-fix-account-deletion.js         | 61 ++++++++++++++++++++++
 2 files changed, 117 insertions(+)
 create mode 100755 bin/cleanup
 create mode 100644 lib/migrations/20200321153000-fix-account-deletion.js

diff --git a/bin/cleanup b/bin/cleanup
new file mode 100755
index 00000000..e2776bb7
--- /dev/null
+++ b/bin/cleanup
@@ -0,0 +1,56 @@
+#!/usr/bin/env node
+
+const logger = require("../lib/logger");
+const models = require('../lib/models')
+
+logger.info("Cleaning up notes that should already have been removed. Sorry.")
+
+async function cleanup() {
+  await models.Note.findAll({
+    include: [{
+      model: models.User,
+      as: 'owner'
+    }]
+  }).then(async function(notes) {
+    for(let i =0, noteCount = notes.length; i< noteCount; i++) {
+      const item = notes[i]
+      if(item.ownerId != null && !item.owner) {
+        await models.Note.destroy({
+          where: {
+            id: item.id
+        }})
+        await models.Revision.destroy({
+          where: {
+            noteId: item.id
+          }
+        })
+        await models.Author.destroy({
+          where: {
+            noteId: item.id
+          }
+        })
+        logger.info(`Deleted note ${item.id} from user ${item.ownerId}`)
+      }
+    }
+  })
+  await models.Author.findAll({
+    include: [{
+      model: models.User,
+      as: 'user'
+    }]
+  }).then(async function(authors) {
+    for(let i =0, authorCount = authors.length; i< authorCount; i++) {
+      const item = authors[i]
+      if(item.userId != null && !item.user) {
+        await models.Author.destroy({
+          where: {
+            id: item.id
+        }})
+        logger.info(`Deleted authorship ${item.id} from user ${item.userId}`)
+      }
+    }
+  })
+  process.exit(0)
+}
+
+cleanup()
diff --git a/lib/migrations/20200321153000-fix-account-deletion.js b/lib/migrations/20200321153000-fix-account-deletion.js
new file mode 100644
index 00000000..e794e993
--- /dev/null
+++ b/lib/migrations/20200321153000-fix-account-deletion.js
@@ -0,0 +1,61 @@
+'use strict'
+const { spawnSync } = require('child_process')
+const path = require('path')
+module.exports = {
+  up: function (queryInterface, Sequelize) {
+    const cleanup = spawnSync('./bin/cleanup', { cwd: path.resolve(__dirname, '../../') })
+    if (cleanup.status !== 0) {
+      throw new Error('Unable to cleanup')
+    }
+    return queryInterface.addConstraint('Notes', ['ownerId'], {
+      type: 'foreign key',
+      name: 'Notes_owner_fkey',
+      references: {
+        table: 'Users',
+        field: 'id'
+      },
+      onDelete: 'cascade'
+    }).then(function () {
+      return queryInterface.addConstraint('Revisions', ['noteId'], {
+        type: 'foreign key',
+        name: 'Revisions_note_fkey',
+        references: {
+          table: 'Notes',
+          field: 'id'
+        },
+        onDelete: 'cascade'
+      })
+    }).then(function () {
+      return queryInterface.addConstraint('Authors', ['noteId'], {
+        type: 'foreign key',
+        name: 'Author_note_fkey',
+        references: {
+          table: 'Notes',
+          field: 'id'
+        },
+        onDelete: 'cascade'
+      })
+    }).then(function () {
+      return queryInterface.addConstraint('Authors', ['userId'], {
+        type: 'foreign key',
+        name: 'Author_user_fkey',
+        references: {
+          table: 'Users',
+          field: 'id'
+        },
+        onDelete: 'cascade'
+      })
+    })
+  },
+
+  down: function (queryInterface, Sequelize) {
+    return queryInterface.removeConstraint('Notes', 'Notes_owner_fkey')
+      .then(function () {
+        return queryInterface.removeConstraint('Revisions', 'Revisions_note_fkey')
+      }).then(function () {
+        return queryInterface.removeConstraint('Authors', 'Author_note_fkey')
+      }).then(function () {
+        return queryInterface.removeConstraint('Authors', 'Author_user_fkey')
+      })
+  }
+}
-- 
cgit v1.2.3