

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.


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".

  • 1
    Hm... "give a new user more permissions than they themselves have" this sounds like a total security no-go. Can you back this claim somehow? – alex Sep 2 '16 at 10:50
  • Well, yes, rtfm :) See the bottom of docs.aws.amazon.com/IAM/latest/UserGuide/… "Allow All IAM Actions (Admin Access)" – Pete Storey Sep 2 '16 at 15:53
  • 1
    Well, just don't grant the account Admin to DBAs--what's wrong with that? – alex Sep 4 '16 at 5:03
  • It's not DBAs - it's absolutely anybody. Somebody, somewhere must have access to add permissions to IAM in some way, even if it's the CEO with the root account details (and magically no one else has them). Realistically, people need to be able to do things with IAM, and if they have access to add permissions, then they can add any permission to anyone, even if they don't have that permission themselves. It would make more sense to be able to restrict such that you could give access that you have yourself to others as well, without having to give "god" powers to anyone.. – Pete Storey Sep 4 '16 at 21:35
  • So Admin Access to AWS is similar to root access in a Unix-like system. – ypercubeᵀᴹ Jun 17 '17 at 10:59

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.

  • Thanks. You can't do any PITR type stuff yourself when running on AWS because you have no access to the servers themselves and it does it automatically for you. Which is lovely, except that someone with the right access can delete both the servers themselves, and the backups. So this question is about a duplicate, disaster recovery type backup, rather than a PITR type one which you'd use more day to day. – Pete Storey Sep 15 '16 at 22:05
  • Sorry to hear that about AWS. I'll watch this question in case someone has an answer! – Ivan Voras Sep 16 '16 at 11:47
  • The above comments apply to RDS, not AWS in general. Of course, running your own installations on EC2 instances give you have access to the servers, can do PiTR, and so on. – dezso Sep 1 '17 at 12:49

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.

Your Answer

