Codimd: Deleting a note should really delete the note in database

[dekonnection]

Hi,

Currently, it seems like deleting a note just marks the note as deleted (by adding the deletion date in the deletedAt field), but keep the note in the database.

Since all notes are stored in plaintext, I know that the database administrator is able to see all the active notes, so user privacy is limited, but I think that a deleted note shouldn't be available, like, ever.

Demonstration :

mysql> select * from Notes where content like '%mysecretnote%';
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+-----------+--------------------------------------+--------------------------------------+
| id                                   | shortid   | alias | permission | viewcount | title    | content                                | authorship                                                                  | lastchangeAt        | savedAt             | createdAt           | updatedAt           | deletedAt | ownerId                              | lastchangeuserId                     |
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+-----------+--------------------------------------+--------------------------------------+
| 8b530b18-0796-4833-a213-81189cadef20 | SJR4bZuKz | NULL  | private    |         0 | Untitled | mysecretnote that I don't want anymore | [["8c158b3e-ce0b-4550-8347-4f533f50aaba",0,38,1521123644145,1521123662902]] | 2018-03-15 14:21:03 | 2018-03-15 14:20:38 | 2018-03-15 14:20:38 | 2018-03-15 14:21:03 | NULL      | 8c158b3e-ce0b-4550-8347-4f533f50aaba | 8c158b3e-ce0b-4550-8347-4f533f50aaba |
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+-----------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

After deletion :

mysql> select * from Notes where content like '%mysecretnote%';
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------+--------------------------------------+
| id                                   | shortid   | alias | permission | viewcount | title    | content                                | authorship                                                                  | lastchangeAt        | savedAt             | createdAt           | updatedAt           | deletedAt           | ownerId                              | lastchangeuserId                     |
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------+--------------------------------------+
| 8b530b18-0796-4833-a213-81189cadef20 | SJR4bZuKz | NULL  | private    |         0 | Untitled | mysecretnote that I don't want anymore | [["8c158b3e-ce0b-4550-8347-4f533f50aaba",0,38,1521123644145,1521123662902]] | 2018-03-15 14:21:03 | 2018-03-15 14:20:38 | 2018-03-15 14:20:38 | 2018-03-15 14:21:03 | 2018-03-15 14:21:18 | 8c158b3e-ce0b-4550-8347-4f533f50aaba | 8c158b3e-ce0b-4550-8347-4f533f50aaba |
+--------------------------------------+-----------+-------+------------+-----------+----------+----------------------------------------+-----------------------------------------------------------------------------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------+--------------------------------------+
1 row in set (0.01 sec)

Thanks for your work :)

[jackycute]

Hi @dekonnection
That's part of current design, it's called soft delete.
In normal ORM query, these notes won't show up unless you specified paranoid: false.

[dekonnection]

While understanding the design, I'm still a little bit concerned, since after deleting a note, the content is secretly kept and the user don't have control on it anymore.

I think that providing a "secure delete" setting (when activated, deletion really drops the note) could be a good thing.
Or maybe automatically erasing the content field before marking the note as deleted ?

[ccoenen]

Yes, we should have some kind of process that will delete notes after some kind of grace period. We should _also_ scrub related tables like revisons.

[ccoenen]

That said: if you're not trusting the server administrator, you should keep in mind: they could have backups (or other ways of reconstructing the note) even if it was deleted.

From a user's perspective i would not distinguish between different types of deletions, this will just confuse people. I would keep soft-delete as default and offer some means of "undo" for some period of time. Right now i'm thinking 1 hour, but this might not be a perfect fit for everyone, either.

[dekonnection]

@ccoenen I agree with everything you said :+1:

The idea of a "recycle bin" is great, along with a configurable grace period.

Concerning your (valid) point about _past_ backups or reconstruction means, I also agree, but I think it's still not a justification to voluntarily keep the full note in database, subject to _future_ backups :)

[SISheogorath]

Well, from a technical point of view, I can say the following:
https://sequelize.readthedocs.io/en/v3/docs/models-definition/#configuration

It is possible to delete notes. The question is can we really make it configurable without a migration?

And if we do, will it delete all previously deleted notes? Someone want to write a PoC and see how it works?

[ccoenen]

I don't see why we would need a migration at all? The way I see it, we need two pieces of code:

• Garbage-Collector (i.e. select everything with deletedAt < now() - 1h -> hard-delete those)
• Restore-Interface (i.e. some way of saying "OH SH*T I WASN'T SUPPOSED TO DELETE THAT JUST YET" which would unset the deletedAt field, i.e. restoring it to a regular note.

It's debatable how the garbage-collector should be implemented, personally, I could totally live with a separate script (like the user management script) that one has to run via cron. But this also adds another moving part.

[SISheogorath]

This was added with #830 we only need to add some later clean up.

[lephix]

Hi @dekonnection
That's part of current design, it's called soft delete.
In normal ORM query, these notes won't show up unless you specified paranoid: false.

@jackycute

I am using latest image hackmdio/hackmd in Docker. When the note is delete by owner, the note is physically delete in table notes, not a soft delete.

[jackycute]

Hi @lephix
Soft deletion was changed in PR #830 which was for GDPR compliant.

[lephix]

@jackycute
I am not familiar with GDPR. But for enterprise, I think data need be tracked. Maybe CodiMD could provide a configuration for the "soft deletion". By default it could be disabled.
I don't know it would violate GDPR or not?

[Lin-Buo-Ren]

@jackycute

Hi @lephix
Soft deletion was changed in PR #830 which was for GDPR compliant.

Does that imply this issue is resolved? Or some UI changes is still necessary?