Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We deleted the production database by accident

keepthescore.co

Post reply on HN
Print view View original

61–70 of 456 posts

Re: We deleted the production database by accident

#61

Post by PeterisP »

muststopmyths wrote: post #7

>Note that host is hardcoded to localhost. This means it should never connect to any machine other than the developer machine. We’re too tired to figure it out right now. The gremlins won this time. Obviously, somehow the script ran on the database host. some practices I've followed in the past to keep this kind of thing from happening: * A script that deletes all the data can never be deployed to production. * scrip…

One aspect that can help with this is separate roles/accounts for dangerous privileges.

I.e. if Alice is your senior DBA who would have full access to everything including deleting the main production database, then it does not mean that the user 'alice' should have the permission to execute 'drop database production' - if that needs to be done, she can temporarily escalate the permissions to do that (e.g. a separate account, or separate role added to the account and removed afterwards, etc).

Arguably, if your DB structure changes generally are deployed with some automated tools, then the everyday permissions of senior DBA/developer accounts in the production environment(s) should be read-only for diagnostics. If you need a structural change, make a migration and deploy it properly; if you need an urgent ad-hoc fix to data for some reason (which you hopefully shouldn't need to do very often), then do that temporary privilege elevation thing; perhaps it's just "symbolic" but it can't be done accidentally.

Re: We deleted the production database by accident

#62

Post by asddubs »

jerf wrote: post #37
amluto wrote: post #13

Earlier quoted context omitted.

I have a little metadata table in production that has a field that says “this is a production database”. The delete-everything script reads that flag via a SQL query that will error out of it’s set in the same transaction as the deletion. To prevent the flag from getting cleared in production, the production software stack will refuse to run if the “production” flag is not set.

This is also one place where defense-in-depth is useful. "Has production flag" OR "name contains 'prod'" OR "hostname contains 'prod'" OR "one of my interfaces is in the production IP range" OR etc. etc. You really can't have too many clauses there. Unfortunately, the "wipe & recreate database" script, while dangerous, is very useful; it's a core part of most of my automated testing because automated testing wipes &…

one silly last resort measure I did on a project a while back was having a IS_STAGING file somewhere, only existing on localhost, and every request it would check if the hostname is that of the live site, and if so, delete that file. the file itself wasn't enough to make the server think it's in staging mode, but it was the only thing in the chain that if it were to go wrong, would fix itself automatically almost immediately (and log an error)

Re: We deleted the production database by accident

#63

Post by asddubs »

kerng wrote: post #24
sdepablos wrote: post #3

VPN to production and same hostname for dev and prod?

...and same credentials apparently also - there are lots of things that could have prevented something like this.

could also be that they just have an if staging/else somewhere for the credentials (but of course this isn't a counterpoint to the root of your point - the script wiping the database shouldn't be using anything that does something like this, and you probably shouldn't do it at all)

Re: We deleted the production database by accident

#64

Post by asddubs »

jlgaddis wrote: post #58
k__ wrote: post #34

Earlier quoted context omitted.

Keep going, I'm writing this down!

Take the "drop database" bit (on the production database) away from your developers, too. As well as pretty much every other privilege they don't legitimately need to use on a daily basis -- which, for prod, should be most of them (quite possibly including "delete"). If or when they really need to delete a ton of rows all in one go, they can be given a (temporary) set of credentials that they can use to do that, once…

that's true, I totally forgot about SQL permissions, there's so many failsafes for this

Re: We deleted the production database by accident

#65

Post by astura »

jlgaddis wrote: post #58
k__ wrote: post #34

Earlier quoted context omitted.

Keep going, I'm writing this down!

Take the "drop database" bit (on the production database) away from your developers, too. As well as pretty much every other privilege they don't legitimately need to use on a daily basis -- which, for prod, should be most of them (quite possibly including "delete"). If or when they really need to delete a ton of rows all in one go, they can be given a (temporary) set of credentials that they can use to do that, once…

As a developer at all the jobs I've had, I've never even had access to the production database, full stop - only the server admins did. If I needed something from prod I'd go through them. I don't even consider it inconvenient.

Re: We deleted the production database by accident

#66

Post by koolba »

yjftsjthsd-h wrote: post #52
Negitivefrags wrote: post #33

If you are using postgres, configure it to keep the WAL logs for at least 24 hours. They could have used point-in-time recovery to not lose any data from this at all.

If you can do this, then yes by all means do it, but that has significant impact on disk usage.

It doesn’t have to be local. In fact it shouldn’t be local anyway as backups accessible to be deleted from the source aren’t real backups any way.

You can the restore from a recent base backup and roll forward the WAL to just before the snafu.

Re: We deleted the production database by accident

#68

Post by caconym_ »

codegladiator wrote: post #48

> Computers are just too complex and there are days when the complexity gremlins win. > However, we will figure out what went wrong and ensure that that particular error doesn’t happen again. How can you say statement 2 just after statement 1 ? Isn't statement 1 just plain acceptance of defeat ? And looking at all the replies here, is this a feel good thread for the mistakes you made ?

In context, statement 1 regards proactively eliminating all bugs and risks. Statement 2 regards understanding the root cause of this particular incident and reactively fixing it so it won’t happen again.

Acknowledging statement 1 doesn’t mean giving up—it simply means being clear and realistic about the nature and scale of the problem we’re facing when we try to build complex software systems. In the face of that we can give up, or we can just do the best we can, and it sounds more like these people are doing the latter.

Re: We deleted the production database by accident

#69

Post by fideloper »

RDS is so very worth paying for this type of issue (in many cases, obviously $60 to multiple thousands a month isn’t great for everything).

Otherwise having a binlog based backup (or WAL, I guess, but i don’t know PG that well) is critical.

The key point there is they provide point in time recovery possibilities (and even the ability to rewrite history).

Re: We deleted the production database by accident

#70

Post by 6nf »

suzzer99 wrote: post #55

We have something similar with AWS Cognito. If a user signs up but doesn't go through with the verification process, there's no setting to say "remove them after X days". So we have to run a batch job. If I screw up one parameter, instead of deleting only unconfirmed users, I could delete all users. I have two redundant checks, first when the query is run to get the unconfirmed users, and then again checking the user…

Soft deletes reduces the scariness
Post reply on HN