Articles / Privacy

841 words

  • backups
  • databases
  • gdpr
  • erasure
  • go
  • postgresql

Restoring a Backup Brings Back the Users You Deleted

Someone asks you to erase their account. You run the DELETE, the row is gone, and you tell them it is done. Three weeks later the database dies, you restore last night's backup, and that person is back, email address and all.

Nothing malfunctioned. The backup did exactly what backups do. The problem is that nobody wrote down that the deletion happened, so the restore had no way to know.

A DELETE does not remove the data from anywhere but the live table

The row can survive in a surprising number of places even before you count backups. In Postgres, for example:

  • the dead tuple stays on the page until VACUUM reclaims the space, and reclaiming is not the same as overwriting;
  • the WAL contains the original insert and any updates;
  • every pg_dump or base backup taken before the delete contains the full row;
  • replicas and read-only copies have their own history;
  • log files and exports may hold the same values in plain text.

You can scrub the live table perfectly and still hold the data in a dozen copies. The live database is only the easiest copy to fix.

Rewriting old backups is the wrong fight

The tempting answer is to open every old backup, remove the person, and re-save it. Do not. It is slow, it breaks checksums and any signatures, and with deduplicating tools such as restic or borg it is not really possible without rewriting whole repositories.

It also fails the real test, which is that you must never have to trust a procedure you do not exercise. Editing archives by hand under pressure is how you corrupt the only copy you have.

The ICO's guidance on erasure has historically accepted that data in backups can be put "beyond use" until the backup is overwritten, rather than being destroyed on the spot. That is regulator guidance, not legislation, and what is reasonable depends on your circumstances, so check the current text. The engineering point stands either way: if the data will come back on restore, you need a mechanism that removes it again.

Keep a deletion ledger outside the database

The mechanism is boring. Every time you erase someone, append their internal ID to a ledger. After any restore, before the application sees a single request, replay the ledger and delete those IDs again.

Two details matter:

  • The ledger must not live in the database you are restoring. If it does, an old backup carries an old ledger and forgets the recent erasures, which is precisely the case you care about.
  • Store the opaque internal ID, not the email or name. A ledger full of personal data would defeat its own purpose.

A plain append-only file or a tiny separate database, backed up on its own, is enough. The ledger grows by a few bytes per erasure.

Replaying it in Go

The replay is one transaction. Here the ledger has been loaded into a temporary table, and foreign keys with ON DELETE CASCADE take care of dependent rows.

package restore

import (
	"context"
	"database/sql"
	"fmt"
)

// ReplayErasures re-deletes every ID in ids from the users table.
// Run it after a restore and before the app accepts traffic.
func ReplayErasures(ctx context.Context, db *sql.DB, ids []int64) (int64, error) {
	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return 0, fmt.Errorf("begin: %w", err)
	}
	defer tx.Rollback()

	var total int64
	for _, id := range ids {
		res, err := tx.ExecContext(ctx, `DELETE FROM users WHERE id = $1`, id)
		if err != nil {
			return 0, fmt.Errorf("delete user %d: %w", id, err)
		}
		n, err := res.RowsAffected()
		if err != nil {
			return 0, fmt.Errorf("rows affected: %w", err)
		}
		total += n
	}
	if err := tx.Commit(); err != nil {
		return 0, fmt.Errorf("commit: %w", err)
	}
	return total, nil
}

Log the returned count. After a restore it should be non-zero if anyone was erased since the backup, and seeing it is a cheap confirmation that the step really ran.

Quick detour: why a count of zero is also informative

If you restore a backup taken moments ago, the count will be zero because nothing needed re-deleting. That is fine. But a zero after restoring a month-old backup, when you know people were erased in that month, means the ledger is empty, missing or pointed at the wrong store. Treat that as a failure, not a quiet success.

The sturdier option: encrypt per person and throw the key away

The ledger relies on you remembering to replay it. Crypto-shredding removes that dependency. Encrypt each person's personal fields with a key unique to them, keep the keys in a separate small store, and erase the person by deleting their key.

Every copy of the ciphertext, in every backup, becomes unreadable at once. There is nothing to replay and nothing to forget.

It has costs. You need working key management, and queries cannot filter on encrypted columns. The key store also needs its own backups, and those must not keep a deleted key forever; short retention on that one small store is far easier to live with than short retention on everything. If you have not built this before, the envelope-encryption pattern is the natural starting point.

Which one to pick

  • Small system, few erasures: the ledger. It is an afternoon of work.
  • Lots of personal data, long backup retention: crypto-shredding, possibly alongside the ledger.
  • Either way: shorten retention for backups that hold personal data, because every extra month is another copy you have to account for.

Then test it the unglamorous way. Erase a test user, restore an older backup into a scratch database, run your replay, and check the user is gone. A restore procedure that has never been run against this scenario is a hope, not a procedure.

← All articles