4 ms·

Everyone thinks deletion is so simple and straight forward until you start dealing with the cascading effects and edge cases.

by xwdv 6y ago

jacquesm 6y ago
Yes, especially backups and log files are hard. The databases are relatively easy if they allow for in place overwrites or compaction of tables. Even then you have to be careful. Data is funny that way. It is easy to acquire, easy to lose if you want to keep it and devilishly hard to get rid of for real if that is what you want to do.
- londons_explore 6y ago
  The vast majority of companies I've seen consider issuing "SQL DELETE" is sufficient for GDPR compliance. One day, they will be burned when some investigation finds out that those rows are still sitting in uncompacted tables, or on now-unallocated disk blocks, or on now-remapped SSD sectors. The only way to be sure the data is deleted is to copy all the data you want to keep to a new drive and burn the old one. Anything less and there is a reasonable chance an expert could recover at least some of the deleted info.
  - jacquesm 6y ago
    All it takes is one dataleak[+] to blow that wide open. Secure erase is hard. But what is much less hard is to encrypt data and then to limit the problem to getting rid of the decryption key. This reduces the problem in scope to one single datum rather than a whole chain of possible plaintext copies. Secure erase is a contractual requirement for many relationships, it is interesting that none of the major db vendors as far as I know have a secure delete option. There are lots of tricks and attempts to work around it but no official support for such functionality afaik. And that's before we get into VM snapshots, database snapshots, backup copies, copies that were made by developers or data scientists for test purposes (of course, nobody ever does that) and so on. Nasty little problem. [+] or some employee shooting their mouth off in an online forum.
    - xwdv 6y ago
      God help you if your decryption key is destroyed somehow and you no longer have access to your own data. Fuck that.
      - jacquesm 6y ago
        If you can't keep your keys around in a safe way you shouldn't be in this business to begin with.
    - mschuster91 6y ago
      > But what is much less hard is to encrypt data and then to limit the problem to getting rid of the decryption key. This reduces the problem in scope to one single datum rather than a whole chain of possible plaintext copies. Key issue: you can't do any operations on encrypted data, essentially you're killing off your database. Homomorphic encryption is academic research, not something that is widely available and supported in common open and closed source databases. The best you can get is a database that encrypts the on disk data (Oracle TDE), but that only protects against a server being stolen or hacked on the OS level.
      - jacquesm 6y ago
        I fully expect that we will see application level and system level tools that are compliant with the law pop up any day now, the need is certainly there. Also, for purposes of operations on data it all depends on what the column holds. For instance, you don't actually need access to fields such as names and dates of birth if you have a client ID and that is yours, not the customers and so you could leave that field in plain text. Any operation would then need that client ID but that's workable. You could even say that if you would need that decryption key for anything other than user or controller directed computation that you are probably doing something you shouldn't be doing. In all other cases the context is clear, consent has been obtained and the data can be decrypted if required.
    - jandrewrogers 6y ago
      Secure delete is not implemented in databases because it is extraordinarily expensive, and destroys the performance of all other non-delete operations. When you say “encrypt data”, you are ignoring the fact that it can’t be implemented as “same data structures, just encrypted”. Encryption puts constraints on data structures and data representation that are fundamentally incompatible with and adverse to the design and functioning of most databases in existence, even ignoring the other ugly operational issues with that approach which no one thinks about. It would require radically redesigning the internals of most database engines, which is not really a practical option. I’ve thought a lot about what it would take to design a “delete optimized” database kernel every since GDPR became a thing. In principle it is possible, but I seriously doubt anyone would use a database that is literally orders of magnitude slower and less scalable for everything except delete operations. Would it be acceptable to increase the resource intensity of databases by 10x (and environmental footprint implied) to get “real” deletes? That is the tradeoff here. This has precedent in SQL databases designed for high-assurance applications with ultra-fine access and visibility controls. When the average software engineer understands how they work, it sounds like a great idea for having more secure data and they wonder why it doesn’t seem to exist. The reality is that they do exist but they are so abysmally slow for even elementary things that no one would ever dream of using it unless there is a narrow government requirement.
  - icedchai 6y ago
    They probably know the odds of an "investigation" actually occurring is very low. The odds of one occurring with that level, or probably any level, of technical sophistication is near zero.
    - jacquesm 6y ago
      From regulators: yes, they typically work reactive, until there is some kind of breach you'll never hear from them. From investors, customers and potential acquirers audits are pretty common and becoming more so every day.
- areyousure 6y ago
  Regarding backups: what is your suggestion for backups of databases that contain individual rows that might need to be deleted?
  - jacquesm 6y ago
    You have several options, none of them pretty. The first is to load, update and rewrite the backup. Time consuming, error prone and you may fuck up the backup that you need. The second starts way further back: encrypt sensitive fields at rest, drop the keys when the user requests a deletion. That way you never even have to touch the backups in order to have the right end result.
    - areyousure 6y ago
      So you have per-row or perhaps per-user keys? Do you back them up?
      - jacquesm 6y ago
        Not currently running a user visible service. Fortunately. The one I did run was started in 1998 and had turned into a very large pile of super insecure PHP and was shut down. If I were to start a user visible service today I would think really long and hard about whether or not the kind of superstructure required would be offset by the potential gains. The web is no longer a place for amateur projects with a focus on the happy path, you need to do it all 'just so' or you can expect to be compromised. And doing it right isn't simple nor is it cheap.
        areyousure 6y ago
        Sorry, what I meant was to clarify your proposal, not to ask what you literally do now. I'm wondering, more or less, whether the key management is another case of backups and deletion being hard.
        jacquesm 6y ago

Everyone thinks deletion is so simple and straight forward until you start dealing with the cascading effects and edge cases.

by xwdv 6y ago

Everyone thinks deletion is so simple and straight forward until you start dealing with the cascading effects and edge cases.

jacquesm 6y ago

Yes, especially backups and log files are hard. The databases are relatively easy if they allow for in place overwrites or compaction of tables. Even then you have to be careful. Data is funny that way. It is easy to acquire, easy to lose if you want to keep it and devilishly hard to get rid of for real if that is what you want to do.

londons_explore 6y ago
The vast majority of companies I've seen consider issuing "SQL DELETE" is sufficient for GDPR compliance. One day, they will be burned when some investigation finds out that those rows are still sitting in uncompacted tables, or on now-unallocated disk blocks, or on now-remapped SSD sectors. The only way to be sure the data is deleted is to copy all the data you want to keep to a new drive and burn the old one. Anything less and there is a reasonable chance an expert could recover at least some of the deleted info.
- jacquesm 6y ago
  All it takes is one dataleak[+] to blow that wide open. Secure erase is hard. But what is much less hard is to encrypt data and then to limit the problem to getting rid of the decryption key. This reduces the problem in scope to one single datum rather than a whole chain of possible plaintext copies. Secure erase is a contractual requirement for many relationships, it is interesting that none of the major db vendors as far as I know have a secure delete option. There are lots of tricks and attempts to work around it but no official support for such functionality afaik. And that's before we get into VM snapshots, database snapshots, backup copies, copies that were made by developers or data scientists for test purposes (of course, nobody ever does that) and so on. Nasty little problem. [+] or some employee shooting their mouth off in an online forum.
  - xwdv 6y ago
    God help you if your decryption key is destroyed somehow and you no longer have access to your own data. Fuck that.
    - jacquesm 6y ago
      If you can't keep your keys around in a safe way you shouldn't be in this business to begin with.
  - mschuster91 6y ago
    > But what is much less hard is to encrypt data and then to limit the problem to getting rid of the decryption key. This reduces the problem in scope to one single datum rather than a whole chain of possible plaintext copies. Key issue: you can't do any operations on encrypted data, essentially you're killing off your database. Homomorphic encryption is academic research, not something that is widely available and supported in common open and closed source databases. The best you can get is a database that encrypts the on disk data (Oracle TDE), but that only protects against a server being stolen or hacked on the OS level.
    - jacquesm 6y ago
      I fully expect that we will see application level and system level tools that are compliant with the law pop up any day now, the need is certainly there. Also, for purposes of operations on data it all depends on what the column holds. For instance, you don't actually need access to fields such as names and dates of birth if you have a client ID and that is yours, not the customers and so you could leave that field in plain text. Any operation would then need that client ID but that's workable. You could even say that if you would need that decryption key for anything other than user or controller directed computation that you are probably doing something you shouldn't be doing. In all other cases the context is clear, consent has been obtained and the data can be decrypted if required.
  - jandrewrogers 6y ago
    Secure delete is not implemented in databases because it is extraordinarily expensive, and destroys the performance of all other non-delete operations. When you say “encrypt data”, you are ignoring the fact that it can’t be implemented as “same data structures, just encrypted”. Encryption puts constraints on data structures and data representation that are fundamentally incompatible with and adverse to the design and functioning of most databases in existence, even ignoring the other ugly operational issues with that approach which no one thinks about. It would require radically redesigning the internals of most database engines, which is not really a practical option. I’ve thought a lot about what it would take to design a “delete optimized” database kernel every since GDPR became a thing. In principle it is possible, but I seriously doubt anyone would use a database that is literally orders of magnitude slower and less scalable for everything except delete operations. Would it be acceptable to increase the resource intensity of databases by 10x (and environmental footprint implied) to get “real” deletes? That is the tradeoff here. This has precedent in SQL databases designed for high-assurance applications with ultra-fine access and visibility controls. When the average software engineer understands how they work, it sounds like a great idea for having more secure data and they wonder why it doesn’t seem to exist. The reality is that they do exist but they are so abysmally slow for even elementary things that no one would ever dream of using it unless there is a narrow government requirement.
- icedchai 6y ago
  They probably know the odds of an "investigation" actually occurring is very low. The odds of one occurring with that level, or probably any level, of technical sophistication is near zero.
  - jacquesm 6y ago
    From regulators: yes, they typically work reactive, until there is some kind of breach you'll never hear from them. From investors, customers and potential acquirers audits are pretty common and becoming more so every day.
areyousure 6y ago
Regarding backups: what is your suggestion for backups of databases that contain individual rows that might need to be deleted?
- jacquesm 6y ago
  You have several options, none of them pretty. The first is to load, update and rewrite the backup. Time consuming, error prone and you may fuck up the backup that you need. The second starts way further back: encrypt sensitive fields at rest, drop the keys when the user requests a deletion. That way you never even have to touch the backups in order to have the right end result.
  - areyousure 6y ago
    So you have per-row or perhaps per-user keys? Do you back them up?
    - jacquesm 6y ago
      Not currently running a user visible service. Fortunately. The one I did run was started in 1998 and had turned into a very large pile of super insecure PHP and was shut down. If I were to start a user visible service today I would think really long and hard about whether or not the kind of superstructure required would be offset by the potential gains. The web is no longer a place for amateur projects with a focus on the happy path, you need to do it all 'just so' or you can expect to be compromised. And doing it right isn't simple nor is it cheap.
      - areyousure 6y ago
        Sorry, what I meant was to clarify your proposal, not to ask what you literally do now. I'm wondering, more or less, whether the key management is another case of backups and deletion being hard.
        jacquesm 6y ago