Best practice for securely backing up RDS Postgres "offsite"?

Question

We're running Postgres on RDS, which is largely great. The big issue with it, however, is to do with AWS' security model, which allows anyone with various permissions to delete everything - your DBs, your backups, the whole lot.

In particular, you can't prevent someone with access to create IAM users and groups from being able to then give a new user more permissions than they themselves have, so either compromised credentials or a disgruntled employee could destroy everything if you rely on RDS' own backups.

EDIT:

Just in case you're wondering what the issue might be, have a quick read of http://www.infoworld.com/article/2608076/data-center/murder-in-the-amazon-cloud.html

So, the "sensible" thing to do seems to be to have a separate AWS account on which you have basically no one having any access, and have a key which can write stuff up to S3 (and possibly read it back if you fancy, though this is probably optional).

This way, you can back things up to an account from which your main AWS admins can't, by accident or design, delete stuff, and then use lifecycle rules to manage it.

Sorry for the long build up - I am literally amazed that people don't seem to have asked/answered this before, as it seems such an obvious thing for almost anyone using RDS (or indeed just AWS) to need to do, but...

How do I backup Postgres in a sensible fashion for this?

Some things to consider:

• Storage space and so on aren't infinite, so ideally don't want to be doing a full pg_dump/gzip/encrypt/upload to S3, which is the obvious solution, as it would mean hundreds of Gb a day going up there which is probably overkill.
• We don't have access to the "core" servers, so can't do differential type backups as would be more "normal"

Would it work to do a pg_dump, then use some sort of diff program to only upload/store the diffs? Since I don't think pg_dump produces things in a specific order, I'm not sure this would work (in the way intended)?

Any other ideas?

I'm aware by the way of the use of manual snapshot sharing - https://aws.amazon.com/blogs/aws/amazon-rds-update-cross-account-snapshot-sharing/ - which works great if you're using unencrypted RDS - but we aren't, for reasons I can't fully explain except that "it sounds good".

Answer 1

I think your way of thinking with a separate machine in AWS which has access to the db is generally the right way to do it, and would just add that the "normal" way of making incremental backups (with any "real" database) is named PITR, for Point-in-time-recovery. If you search for this term and PostgreSQL, you will find tutorials on how to do generic incremental database backups, and it's up to you to implement them using AWS.

Answer 2

I will come at this question from two angles.

Technical Controls

If you can handle a slightly longer Recovery Point Objective (RPO) (maybe once a week), then I actually like your suggestion of using pg_dump. If the database in question is in the hundreds of GB, then backups could be stored locally to an SMB grade NAS. They could also be stored on S3 on a separate account. To save on costs, Amazon Glacier would also be an excellent option. You could even save encrypted tarballs to another cloud service (even Dropbox would work).

Procedural Controls

To your point, someone needs to have full administrative control of the AWS account. There are significant potential civil and criminal penalties for the destruction and theft of data. So, there are some protections from insider threats. However, your post highlights the importance of protecting the root AWS account from outside actors (MFA should be mandatory).

Infrastructure as Code

One thing to consider regarding general AWS best practices is that resources should be defined as code. Code can be stored in under a Version Control System (VCS) and should be backed up locally (100 year DVDs would make a good choice for a physical backup). Therefore, if you are able to back up all of your data locally (getting back to the root of the question), rebuilding your infrastructure can and should be automated. However, this questions highlights the risks of relying solely on AWS for your data storage.