9 ms·

Doing real deletes on user accounts is a surprisingly challenging problem and I'd be willing to bet very few companies do real deletes where all of your data is

Doing real deletes on user accounts is a surprisingly challenging problem and I'd be willing to bet very few companies do real deletes where all of your data is wiped permanently from the company. For legal and financial reasons, companies often need to keep track of historical user activity. If a company states in their investor quarterly report that they had 1M active users, they better be able to prove it in an audit. And in a naive relational database implementation, deleting a user would cascade and delete activity associated with that user. The easiest way around this is to do soft deletes where the data stays in the db, but the flag deactivates the user's account. Looks like the NYT just did a poor implementation of a soft-delete.

antsar 6y ago
> poor implementation Well that's an understatement. NYT has the tech resources to do this a million better ways. > investor quarterly report [...] active users Speaking of "active user" counts, it's convenient that "jsmith1000" is plausibly an active user, whereas "jsmith(state:deleted)" is not. Hm.
mesozoic 6y ago
Pretty sure that breaches GDPR and maybe CCPA. Maybe they can get away with anonomyzing the data but that doesn't sound like what is being done.
- true_religion 6y ago
  Sure, but a local paper like the New York Times is hardly subject to the laws of the EU, so GDPR doesn’t apply.
  - wtfishackernews 6y ago
    If their website is accessible from the EU then they are subject to GDPR.
  - 6gvONxR4sf7o 6y ago
    It does if they take subscribers from the EU (or california) and apply this process to them. It's incredibly straightforward. If you do business in some jurisdiction, then that business is subject to the jurisdiction's laws.
    - mopsi 6y ago
      Not only do they take subscribers, they actively target the European market. When I open nytimes.com, a pop-up offers 0,50€/week digital access.
      - true_religion 6y ago
        Hmm, it seems I was wrong then. I had thought that they didn’t localize to any EU countries, but I guess they have more of a global market than the other papers I am more familiar with.
  - kube-system 6y ago
    The EU says that GDPR applies globally. Some violators might successfully keep their assets out of the reach of EU enforcement, but that's going to be really tough to do for any large business with global operations.
perl4ever 6y ago
I used to work where (not a service for the general public) there was an "is deleted" flag for everything, but every now and then a client would insist that data be really deleted, and depending on who it was and how they asked, we might go and do it, which was a huge hassle and would cause no end of problems down the line. On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. Lately I've faced kind of an inverse situation - I have a system that I can't control where things are permanently deleted once in a while for multiple reasons (rogue users, aging out of old versions) and so as I accumulate information in a little data warehouse for reporting, I decided to implement an "is deleted" flag there. Eventually though, deleting from the source was turned off because it's really not necessary.
- latch 6y ago
  You can use row level security so that forgetting the filter isn't an issue.
  - dorgo 6y ago
    as far as I understand this would only work for users who don't have access to "deleted" rows. Are access rules a good way to handle this? Serious question. My solution would be to have views as "guards" for every table.
    - latch 6y ago
      Views also work. I don't understand what you mean re "access". RLS is what removes access to those rows, that's the point of RLS. In postgresql, anyways, superusers aren't subject to RLS and the table owner, by default, isn't either. But RLS can be enforced for the table owner by a single alter statement.
- dorgo 6y ago
  >On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. My solution would be a view for every table. Are there drawbacks? Other solutions?
clairity 6y ago
the easier way, assuming neither is a primary key, is to convert the field values to UUIDs, which has the added advantage of anonymizing the data. that's disadvantageous if you want to prevent re-signups though, unless you take other measures.
chrisshroba 6y ago
Doesn't doing soft deletes on user data breach GDPR laws regarding deletion of user data when requested?
munificent 6y ago
There is also a user-valuable reason to not do hard deletes. Doing a soft delete prevents another malicious user from immediately reclaiming your now-available ID and pretending to be you.
- pinusc 6y ago
  You can always hard delete all the data _and_ keep track of deleted users so that their usernames can't be reused. Once you have hard delete, this solution is almost trivial and by far the most user-valuable.
  - DevX101 6y ago
    > keep track of deleted users so that their usernames can't be reused This seems to violate GPDR, no? Attacker attempts to create an account (say: victim@gmail.com) on AshleyMadison and is prevented because the server tracked past users. Attacker could them demonstrate victim@gmail.com was at one point a user on AshleyMadison.com
    - RandomBacon 6y ago
      That's not much different than not being able to create an account with victim@gmail.com because victim@gmail.com already has an account. Both instance leak information
    - lostapathy 6y ago
      Verifying the email keeps someone from hijacking the account without leaking that an account formerly existed. At least so long as their email isn't also compromised - in which case they have bigger problems.
    - crdrost 6y ago
      You don't have to track their emails unless you are reusing emails as usernames. Just tracking the username suffices. This is also one of those situations where people often put too much shit in the user table. "we have to delete the user row" -- I mean, you have to delete some of the user row, yes. I like to solve this by proper namespacing. Suppose you instead deliberately have an authUser table which just has what you need for auth -- a UUID to hook into the rest of the system, salts and passwords for direct logins, maybe a nullable date "banned_until" if you want banning; assuming you use crypto bearer tokens rather than an auth tokens table then you also want a column with a date date for "tokens last reset on"; etc. You can put the username in there just fine, that's needed for auth. Maybe you let people log in with email+password and thus you also put their email address in there, also fine. As long as the authUser table does not grow to encompass all of your other business logic you are good. Other tables foreign key to authUser and you delete rows from them and that doesn't upset the foreign key. You leave the row in authUser to indicate that the username is taken. An additional "deleted" field on authUser can be used to block logins and thus the username is taken but they can't log in. As for the email address, even if you insist on a UNIQUE and NOT NULL constraint for it (and I would find this surprising in an age where we log in a lot with social media) you can auto purge by setting it to CONCAT(id, "@purged.example") and then you have a valid email address which is nowhere else used in your auth flow, no personally-identifiable information at all. Heck then you don't even need the boolean flag if you would rather forbid the .example TLD from logging in. So that has worked well for me in the past and it seems to solve those sorts of problems with only a little tweak. The key is that the PII need is to delete the "user row" but that does not have to be the authUser row -- if you separate the two rows out then you can leave the authUser row while still having a table appUser which lives in your application and contains all the cool stuff about this user using that app. It also naturally lends itself to you thinking about a sort of SSO for all of your different applications up-front.
hinkley 6y ago
And something I had to learn the hard way and then teach quite a few people is that hard deletes don’t just turn your tables into Swiss cheese, they also can cause table scans. When you delete a row, every inbound foreign key constraint has to be checked to look for any rows that refer to the deleted row, and most likely you didn’t set up an index for the foreign key, so now you have a table scan. Possibly several. It’s not that much more work these days to set up a partial index on the table instead and add another WHERE clause, you have smaller problems with people accidentally deleting the wrong thing, and you’ve started down the path to audit trails.
- deleted 6y ago
  [deleted]
- nikisweeting 6y ago
  It's been standard practice at all the companies I've worked at to index all foreign key fields, no matter what they're used for, I've yet to run into a situation where it's been more harmful than helpful, but these companies all had <10TB data in SQL so idk if it's good general advice.
vincvinc 6y ago
Soft deletion violates the GDPR. Read article 17: https://gdpr-info.eu/art-17-gdpr/ https://gdpr-info.eu/art-17-gdpr/ And previous HN discussions: https://news.ycombinator.com/item?id=16366050 https://news.ycombinator.com/item?id=16366050
- DevX101 6y ago
  Did a glance through that thread and it didn't seem like there was a strong consensus on how to respect GPDR while maintaining historical data for reporting purposes. Any best practices?
  - jackcosgrove 6y ago
    Having worked in a HIPAA regulated space, I can say that hashing the username, such as the email address, for login purposes can allow for account recovery if the credentials are retained. At the same time the cleartext username and other PII can be stored in an object that is both encrypted at rest for its lifetime, and on top of that has its sensitive fields overwritten upon logical deletion. Account recovery cannot recover non-credential derived PII but that is a small annoyance to the user in order to be compliant and trustworthy. The internal user ID should be used throughout downstream reporting rather than actual PII for the sake of continuity and privacy.
    - ska 6y ago
      Yes this is all quite do-able. It is often much easier to implement from the beginning, than after the fact.
- tomwojcik 6y ago
  Yes and no. PII needs to be removed. The rest of the data needs to be anonymized. Right?
  - Dahoon 6y ago
    But an email is PII so clearly this would breach GDPR.
ars 6y ago
Another option is to add an "archive" table, where deleted records are moved to that table. It can get complex (for example foreign keys need special handling). One option is table partitioning: https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/ https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-ke... You partition the table based on deleted or not, and then query either both tables together, or just the active table.
- jackcosgrove 6y ago
  Partitioning sounds like a great solution, thank you for this.
cryptonector 6y ago
In general you don't want to delete absolutely everything. For example, usernames should not be reused, so you can't "delete" them -- you can tombstone them though, and you should. Besides tombstoning to prevent reuse, you can and should delete as much associated metadata as you're willing to / contractually or legally required, naturally. Even what you can delete can (and will) survive in logs and backups, web archives, screenshots, etc. Deleting things on the Internet is just difficult.
cyberowl 6y ago
Are soft deletes even legal in the context of privacy laws like GDPR? If I’m writing in to delete my data, I don’t really give a crap how hard it is. I want that permanently wiped, so that even if you wanted to you can’t find it again. How that messes up your technical implementation is your problem
greendestiny_re 6y ago
>investor quarterly reports God forbid the needs of the user harm the interest of the investor.
nexuist 6y ago
>If a company states in their investor quarterly report that they had 1M active users, they better be able to prove it in an audit. Is this even legal? I've never heard of a company letting an outside firm go through their database to confirm any sort of statistic like that. Who is doing this auditing?

antsar 6y ago

> poor implementation Well that's an understatement. NYT has the tech resources to do this a million better ways. > investor quarterly report [...] active users Speaking of "active user" counts, it's convenient that "jsmith1000" is plausibly an active user, whereas "jsmith(state:deleted)" is not. Hm.

mesozoic 6y ago

Pretty sure that breaches GDPR and maybe CCPA. Maybe they can get away with anonomyzing the data but that doesn't sound like what is being done.

true_religion 6y ago
Sure, but a local paper like the New York Times is hardly subject to the laws of the EU, so GDPR doesn’t apply.
- wtfishackernews 6y ago
  If their website is accessible from the EU then they are subject to GDPR.
- 6gvONxR4sf7o 6y ago
  It does if they take subscribers from the EU (or california) and apply this process to them. It's incredibly straightforward. If you do business in some jurisdiction, then that business is subject to the jurisdiction's laws.
  - mopsi 6y ago
    Not only do they take subscribers, they actively target the European market. When I open nytimes.com, a pop-up offers 0,50€/week digital access.
    - true_religion 6y ago
      Hmm, it seems I was wrong then. I had thought that they didn’t localize to any EU countries, but I guess they have more of a global market than the other papers I am more familiar with.
- kube-system 6y ago
  The EU says that GDPR applies globally. Some violators might successfully keep their assets out of the reach of EU enforcement, but that's going to be really tough to do for any large business with global operations.

perl4ever 6y ago

I used to work where (not a service for the general public) there was an "is deleted" flag for everything, but every now and then a client would insist that data be really deleted, and depending on who it was and how they asked, we might go and do it, which was a huge hassle and would cause no end of problems down the line. On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. Lately I've faced kind of an inverse situation - I have a system that I can't control where things are permanently deleted once in a while for multiple reasons (rogue users, aging out of old versions) and so as I accumulate information in a little data warehouse for reporting, I decided to implement an "is deleted" flag there. Eventually though, deleting from the source was turned off because it's really not necessary.

latch 6y ago
You can use row level security so that forgetting the filter isn't an issue.
- dorgo 6y ago
  as far as I understand this would only work for users who don't have access to "deleted" rows. Are access rules a good way to handle this? Serious question. My solution would be to have views as "guards" for every table.
  - latch 6y ago
    Views also work. I don't understand what you mean re "access". RLS is what removes access to those rows, that's the point of RLS. In postgresql, anyways, superusers aren't subject to RLS and the table owner, by default, isn't either. But RLS can be enforced for the table owner by a single alter statement.
dorgo 6y ago
>On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. My solution would be a view for every table. Are there drawbacks? Other solutions?

clairity 6y ago

the easier way, assuming neither is a primary key, is to convert the field values to UUIDs, which has the added advantage of anonymizing the data. that's disadvantageous if you want to prevent re-signups though, unless you take other measures.

chrisshroba 6y ago

Doesn't doing soft deletes on user data breach GDPR laws regarding deletion of user data when requested?

munificent 6y ago

There is also a user-valuable reason to not do hard deletes. Doing a soft delete prevents another malicious user from immediately reclaiming your now-available ID and pretending to be you.

pinusc 6y ago
You can always hard delete all the data _and_ keep track of deleted users so that their usernames can't be reused. Once you have hard delete, this solution is almost trivial and by far the most user-valuable.
- DevX101 6y ago
  > keep track of deleted users so that their usernames can't be reused This seems to violate GPDR, no? Attacker attempts to create an account (say: victim@gmail.com) on AshleyMadison and is prevented because the server tracked past users. Attacker could them demonstrate victim@gmail.com was at one point a user on AshleyMadison.com
  - RandomBacon 6y ago
    That's not much different than not being able to create an account with victim@gmail.com because victim@gmail.com already has an account. Both instance leak information
  - lostapathy 6y ago
    Verifying the email keeps someone from hijacking the account without leaking that an account formerly existed. At least so long as their email isn't also compromised - in which case they have bigger problems.
  - crdrost 6y ago
    You don't have to track their emails unless you are reusing emails as usernames. Just tracking the username suffices. This is also one of those situations where people often put too much shit in the user table. "we have to delete the user row" -- I mean, you have to delete some of the user row, yes. I like to solve this by proper namespacing. Suppose you instead deliberately have an authUser table which just has what you need for auth -- a UUID to hook into the rest of the system, salts and passwords for direct logins, maybe a nullable date "banned_until" if you want banning; assuming you use crypto bearer tokens rather than an auth tokens table then you also want a column with a date date for "tokens last reset on"; etc. You can put the username in there just fine, that's needed for auth. Maybe you let people log in with email+password and thus you also put their email address in there, also fine. As long as the authUser table does not grow to encompass all of your other business logic you are good. Other tables foreign key to authUser and you delete rows from them and that doesn't upset the foreign key. You leave the row in authUser to indicate that the username is taken. An additional "deleted" field on authUser can be used to block logins and thus the username is taken but they can't log in. As for the email address, even if you insist on a UNIQUE and NOT NULL constraint for it (and I would find this surprising in an age where we log in a lot with social media) you can auto purge by setting it to CONCAT(id, "@purged.example") and then you have a valid email address which is nowhere else used in your auth flow, no personally-identifiable information at all. Heck then you don't even need the boolean flag if you would rather forbid the .example TLD from logging in. So that has worked well for me in the past and it seems to solve those sorts of problems with only a little tweak. The key is that the PII need is to delete the "user row" but that does not have to be the authUser row -- if you separate the two rows out then you can leave the authUser row while still having a table appUser which lives in your application and contains all the cool stuff about this user using that app. It also naturally lends itself to you thinking about a sort of SSO for all of your different applications up-front.

hinkley 6y ago

And something I had to learn the hard way and then teach quite a few people is that hard deletes don’t just turn your tables into Swiss cheese, they also can cause table scans. When you delete a row, every inbound foreign key constraint has to be checked to look for any rows that refer to the deleted row, and most likely you didn’t set up an index for the foreign key, so now you have a table scan. Possibly several. It’s not that much more work these days to set up a partial index on the table instead and add another WHERE clause, you have smaller problems with people accidentally deleting the wrong thing, and you’ve started down the path to audit trails.

deleted 6y ago
[deleted]
nikisweeting 6y ago
It's been standard practice at all the companies I've worked at to index all foreign key fields, no matter what they're used for, I've yet to run into a situation where it's been more harmful than helpful, but these companies all had <10TB data in SQL so idk if it's good general advice.

vincvinc 6y ago

Soft deletion violates the GDPR. Read article 17: https://gdpr-info.eu/art-17-gdpr/ https://gdpr-info.eu/art-17-gdpr/ And previous HN discussions: https://news.ycombinator.com/item?id=16366050 https://news.ycombinator.com/item?id=16366050

DevX101 6y ago
Did a glance through that thread and it didn't seem like there was a strong consensus on how to respect GPDR while maintaining historical data for reporting purposes. Any best practices?
- jackcosgrove 6y ago
  Having worked in a HIPAA regulated space, I can say that hashing the username, such as the email address, for login purposes can allow for account recovery if the credentials are retained. At the same time the cleartext username and other PII can be stored in an object that is both encrypted at rest for its lifetime, and on top of that has its sensitive fields overwritten upon logical deletion. Account recovery cannot recover non-credential derived PII but that is a small annoyance to the user in order to be compliant and trustworthy. The internal user ID should be used throughout downstream reporting rather than actual PII for the sake of continuity and privacy.
  - ska 6y ago
    Yes this is all quite do-able. It is often much easier to implement from the beginning, than after the fact.
tomwojcik 6y ago
Yes and no. PII needs to be removed. The rest of the data needs to be anonymized. Right?
- Dahoon 6y ago
  But an email is PII so clearly this would breach GDPR.

ars 6y ago

Another option is to add an "archive" table, where deleted records are moved to that table. It can get complex (for example foreign keys need special handling). One option is table partitioning: https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-keys-and-partitioned-tables/ https://www.2ndquadrant.com/en/blog/postgresql-12-foreign-ke... You partition the table based on deleted or not, and then query either both tables together, or just the active table.

jackcosgrove 6y ago
Partitioning sounds like a great solution, thank you for this.

cryptonector 6y ago

In general you don't want to delete absolutely everything. For example, usernames should not be reused, so you can't "delete" them -- you can tombstone them though, and you should. Besides tombstoning to prevent reuse, you can and should delete as much associated metadata as you're willing to / contractually or legally required, naturally. Even what you can delete can (and will) survive in logs and backups, web archives, screenshots, etc. Deleting things on the Internet is just difficult.

cyberowl 6y ago

Are soft deletes even legal in the context of privacy laws like GDPR? If I’m writing in to delete my data, I don’t really give a crap how hard it is. I want that permanently wiped, so that even if you wanted to you can’t find it again. How that messes up your technical implementation is your problem

greendestiny_re 6y ago

>investor quarterly reports God forbid the needs of the user harm the interest of the investor.

nexuist 6y ago

>If a company states in their investor quarterly report that they had 1M active users, they better be able to prove it in an audit. Is this even legal? I've never heard of a company letting an outside firm go through their database to confirm any sort of statistic like that. Who is doing this auditing?