4 ms·

>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

>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. * scripts that alter the DB rename tables/columns rather than dropping them (you write a matching rollback script ), for at least one schema upgrade cycle. you can always restore from backups, but this can make rollbacks quick when you spot a problem at deployment time. * the number of people with access to the database in prod is severely restricted. I suppose this is obvious, so I'm curious how the particular chain of events in TFA happened.

amluto 6y ago
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.
- at_a_remove 6y ago
  Nice. Very nice.
- jerf 6y ago
  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 & recreates a lot.
  - asddubs 6y ago
    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)
- asimjalis 6y ago
  I would flip the logic. If database does not have flag that says it is non-production assume it is production.
  - amluto 6y ago
    It's a BOOLEAN NOT NULL. I don't recall off the top of my head whether TRUE means production or TRUE means testing.
mcpherrinm 6y ago
The blog mentions it's a managed DigitalOcean database, so the script likely wasn't run on the host itself. More likely, I'd suspect, is something like an SSH tunnel with port forwarding was running, perhaps as part of another script.
jlgaddis 6y ago
> the number of people with access to the database in prod is severely restricted And of those people, there should be an even fewer number with the "drop database" privilege on prod. Also, from a first glance, it looks like using different database names and (especially!) credentials between the dev and prod environments would be a good idea too.
PeterisP 6y ago
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.
StavrosK 6y ago
Someone SSHed to production and forwarded the database port to the local machine to run a report, then forgot about the connection and ran the deletion script locally.
- cutemonster 6y ago
  That has happened? Or was it a thought about what could have happened elsewhere?
  - StavrosK 6y ago
    Oh, no, that's my guess as to what happened here.

amluto 6y ago

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.

at_a_remove 6y ago
Nice. Very nice.
jerf 6y ago
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 & recreates a lot.
- asddubs 6y ago
  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)
asimjalis 6y ago
I would flip the logic. If database does not have flag that says it is non-production assume it is production.
- amluto 6y ago
  It's a BOOLEAN NOT NULL. I don't recall off the top of my head whether TRUE means production or TRUE means testing.

mcpherrinm 6y ago

The blog mentions it's a managed DigitalOcean database, so the script likely wasn't run on the host itself. More likely, I'd suspect, is something like an SSH tunnel with port forwarding was running, perhaps as part of another script.

jlgaddis 6y ago

> the number of people with access to the database in prod is severely restricted And of those people, there should be an even fewer number with the "drop database" privilege on prod. Also, from a first glance, it looks like using different database names and (especially!) credentials between the dev and prod environments would be a good idea too.

PeterisP 6y ago

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.

StavrosK 6y ago

Someone SSHed to production and forwarded the database port to the local machine to run a report, then forgot about the connection and ran the deletion script locally.

cutemonster 6y ago
That has happened? Or was it a thought about what could have happened elsewhere?
- StavrosK 6y ago
  Oh, no, that's my guess as to what happened here.