New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Improved DB+filesystem backups #440

Closed
philosophicles opened this Issue Jul 16, 2018 · 17 comments

Comments

Projects
None yet
4 participants
@philosophicles
Copy link
Contributor

philosophicles commented Jul 16, 2018

Leaving this one intentionally vague as a placeholder; this came up in an email discussion back in February (@hoyes musing). Not sure whether discussing it in more detail publicly is OK.

We have backups but backups could be better, in terms of location, redundancy and longevity.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 17, 2018

NB: I'm being careful throughout this issue to differentiate database backups from filesystem backups (eg. uploaded images).

Ideally what we want is some form of versioned rotated offsite backup repository. So eg.

  1. Hourly DB backups for the last three days
  2. Daily DB backups for the three week
  3. Weekly DB backups for the three month
  4. Monthly DB backups for the last year

In terms of the 'best' way to do this I would suggest putting them in a S3 bucket on Amazon AWS. Now obviously that has a cost associated with it. Bearing in mind that DB backups aren't that big size-wise and S3 storage is cheap, I would be happy to personally absorb that as a 'donation' to Camdram as such. Maybe this is something societies donating via #298 could cover long-term.

Filesystem backups are much more complicated to version, larger in size and something we should definitely also think about. @alexbrett I'm not sure if we are snapshotting the system disks at the hypervisor level but we definitely need to regularly backup at least everything under /var/www/camdram/shared somewhere offsite.

@GKFX

This comment has been minimized.

Copy link
Contributor

GKFX commented Jul 23, 2018

One issue with putting a full DB dump on AWS is that we haven't got consent in the privacy policy to share personal data with third parties. This could probably be overcome by encrypting it though.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 23, 2018

Oh yes absolutely - sorry for not mentioning that earlier! In my opinion a backup that isn't encrypted is asking for trouble (and breaking GDPR as you say)...

Obviously this depends a lot on the backup methodology/software but I tend to encrypt a lot of my personal backups for my PGP key. Could do something similar here ie. pipe the output of tar or mysqldump through GPG? Filesystem backup software tends to either use PGP/GPG (eg. Duplicity) or its own implementation based on a symmetric key (eg. Restic).

@GKFX

This comment has been minimized.

Copy link
Contributor

GKFX commented Jul 23, 2018

That sounds very reasonable. Although would possibly be worth compressing it first to save you a few quid.
Also I would suggest generating and using a Camdram key instead and distributing it to the rest of us at some point, so that if you spill tea on your laptop, we don’t lose all the backups 😉

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 23, 2018

Definitely! So there are two approaches to that as far as I know:

  1. Backups are encrypted to a 'Camdram Administrators' public key. The passphrase to decrypt the corresponding private key is stored in a file which itself is individually encrypted for each of us and distributed. To restore a backup we decrypt the passphrase file and then decrypt the backup using the private key for the 'Camdram Administrators' key.
  2. Backups are encrypted collectively for all Camdram admin key IDs.

Method 1 is a lot more complex, but has the advantage that a retired admin cannot decrypt backups once they leave (current admins change the password for the 'Camdram Administrators' key and then reencrypt and redistribute).

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 23, 2018

I've used this script successfully before:

#!/bin/bash

DATE=$(date "+%Y%m%d%H%M%S")

umask 177

for DBNAME in "${SQL_DATABASES[@]}"
do
  # assume table format is InnoDB so we can backup in a single transaction without blocking read/writes
  mysqldump --defaults-file=$SQL_DEFAULTS --single-transaction --skip-lock-tables $DBNAME | \
  bzip2 | gpg --batch --compress-algo none -e -r $GPGKEY -o $BACKUP_DESTINATION/camdram.$DBNAME.$DATE.sql.bz2.gpg
done
@hoyes

This comment has been minimized.

Copy link
Member

hoyes commented Jul 23, 2018

This all sounds great and no strong opinions here so feel free to go ahead with whatever strategy you think is best. 'Tis much needed.

For images, I'm wondering if we could get away with just storing them in a single versioned S3 bucket (https://docs.aws.amazon.com/AmazonS3/latest/dev/Versioning.html). If we switched to using incrementing IDs as the filename (which is probably a good idea anyway) it should mean we never overwrite anything anyway, but the versioning thing would give an extra level of protection against a random script accidentally doing something bad.

We currently have about 250 Mb of images, which I think would come to <$1 a year (if I've done my maths right) for a single snapshot. I'm happy to contribute a little something to S3 costs too for now, but yea should try and figure something out for #298.

@philosophicles

This comment has been minimized.

Copy link
Contributor

philosophicles commented Jul 25, 2018

Method 1 is a lot more complex, but has the advantage that a retired admin cannot decrypt backups once they leave (current admins change the password for the 'Camdram Administrators' key and then reencrypt and redistribute).

I am absolutely not a crypto expert, but what is to stop an admin obtaining the actual 'Camdram Adminstrators' private key while they have access to the passphrase, then storing the actual private key somehow on their own so they can still use it after retiring from Camdram?

This is extreme edge-case security thinking; I highly doubt that any Camdram admin would ever want to do anything Bad after they retire from Camdram. But I figured I'd ask as maybe I'll learn something from the answer ;-)

@CHTJonas CHTJonas changed the title Improved DB backup Improved DB+filesystem backups Jul 25, 2018

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 25, 2018

Improved DB backups are now happening. This is a on/offsite arrangement with local copies deleted as they get old only if they have been mirror to the cloud (where they are stored permanently):

  • The db-backup user's crontab runs /db-backups/backup.sh at regular intervals with the appropriate arguments;
  • If successful, /db-backups/housekeeping.sh is then run which mirrors to AWS;
  • If the mirror was successful, files that are appropriately old are locally deleted.
  • 'Special' backups are not aged or deleted locally.

All local backups are located under:

drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 13:59 /db-backups/daily/
drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 13:59 /db-backups/hourly/
drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 14:00 /db-backups/monthly/
drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 14:01 /db-backups/special/
drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 14:00 /db-backups/weekly/
drwxr-x--- 2 db-backup db-backup 4.0K Jul 25 14:00 /db-backups/yearly/

Edit: the above is no longer a true reflection of what happens on Tiberius.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 25, 2018

what is to stop an admin obtaining the actual 'Camdram Adminstrators' private key while they have access to the passphrase, then storing the actual private key somehow on their own so they can still use it after retiring from Camdram

Yeah so this is definitely a loophole, and one I can't immediately seem to resolve however I think about it 🤔. I originally read about this method somewhere online but now I can't seem to find it. In any case if an administrator takes a copy of a backup and copy of the private key + passphrase they can do whatever they like. Short of decrypting all past backups and then re-encrypting afterwards I can't see a way around this problem.

From the search I did find that GitLab encrypt their DB backups using a shared company-wide PGP key, the password for which is stored in their 1Password vault (source). We could do something similar and have the password reside in a Camdram KeePassXC vault in a Google Team Drive? (I'm sure other free password vaults are out there but this is the one that I happen to use. Happy if someone want to propose something else that's free 😄)

@hoyes I was thinking of using Restic to backup all the images. It's versioning and deduplicating, but would mean a separate symmetric key/password as it doesn't use PGP. Personally I use it for my VPS and really like it, but again if anyone has any suggestions here feel free to shout!

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 25, 2018

Or, as you say, we do transition away from md5 image names and switch to numeric IDs and just mirror the folder without encryption (since all images are publicly available anyway).

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 25, 2018

I should add that I have some AWS credentials and API keys to share with you all so maybe a password vault would be a good idea (apologies for triple posting...)

@GKFX

This comment has been minimized.

Copy link
Contributor

GKFX commented Jul 25, 2018

Would probably be easier for you to encrypt them with our GPG keys and then just email them to webteam.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jul 25, 2018

Ideally some kind of database would be preferable as new keys will invariably need to be added/old keys rotated and this would make it so much easier than having to redistribute an encrypted text file to everyone.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Aug 3, 2018

I've left the current system of DB backups running for a while and whilst I think it's working great, it's using in excess of 20,000 PUT/GET requests per month which is, quite frankly, ridiculous (and means we're being charged excessively).

I'm going to change the script so that we don't 'sync' the entire local repository, rather just upload the latest backup.

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Aug 3, 2018

I've just written a script at /root/cron/fs-backup.sh that handles filesystem backups. Currently it makes a TARball of everything under /var/www/camdram/production/shared, compresses it, encrypts it and uploads it to S3. This is called by the root crontab at half-past midnight everyday. We might want to revisit this in the near future and look at storage usage/projected costs.

@CHTJonas CHTJonas self-assigned this Aug 3, 2018

@CHTJonas

This comment has been minimized.

Copy link
Member

CHTJonas commented Jan 10, 2019

This has been working well for a while and I've tested doing a full restore on multiple occasions myself, as has @GKFX. Credentials for S3 are in the Keybase vault so closing now.

@CHTJonas CHTJonas closed this Jan 10, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment