5 ms·

Kudos for a thorough and transparent writeup, and (by the looks of things) understanding that processes fail rather than individuals. That said, I have to admi

Kudos for a thorough and transparent writeup, and (by the looks of things) understanding that processes fail rather than individuals. That said, I have to admit to having at least three eye-bulge WTF moments while reading this. I'm also surprised that there isn't a Remediation step of "firewall the development machines away from the production database". (And isn't the change to database_cleaner to make it throw when run against remote databases by default a serious break of API compatibility? What if someone's depending on that behavior?)

hardwaresofton 9y ago
At the very least, different credentials in the database for each environment would make sense. I prefer thorough E2E testing to mocked large-component integration testing (i.e. spinning up a db vs mocking it out) so I can totally understand needing to run tests against a database but yeah reading this was a collar-pulling "yikes" moment for me. Their remediation steps are good though, for a start... They might want to look into spinning up completely new databases programattically for tests rather than connecting to one...
- pech0rin 9y ago
  Postgres has credentials in the URL, so I am sure they already had different credentials, but the URL was set.
  - joelcornett 9y ago
    This seems like it fits the narrative. Still, WTF? A quick google tells me that PostgreSQL supports SSL cert-based authN. The prod environment (and possibly an Ops host) should be the only ones with the right certs to connect to the prod DB.
    - dalore 9y ago
      Creating an ssl connection to the postgres server might add extra connection time (Yes could be prevented by persistent connections but that's not always feasible, especially in a cloud based environment). So it's not the best solution. Postgres pg_hba.conf does support access by ip address easily enough. For access to the production databases the ip addresses should be limited to those that need access. Or if not in postgres, a firewall could limit it. So if a developer accidentally tried to wipe it, they wouldn't be allowed to connect. If they actually needed to connect, they would first have to add their ip address (or use a bastion host).
      - lvh 9y ago
        The GP was presumably talking about TLS with mutual auth. There’s no excuse for not having TLS at all enabled, but mTLS is a great extra security feature. The cost of mTLS (performance wise) is negligible, and if you can’t keep a persistent database connection up (because cloud?!) but also can’t do TLS because perf then I don’t know what to tell you. The only performance issue TLS has is that it isn’t used enough.
        foobarbazetc 9y ago
        As someone who has helped many, many companies with Postgres I can tell you that nobody runs PG over TLS in production if everything’s talking over private links. There’s zero reason to do this.
      - benmmurphy 9y ago
        if you are running PG like a KV store (simple lookups/simple updates/inserts) where most of the dataset fits in memory then it quickly becomes CPU bound and changes in PG configuration/access patterns can have large effects. for example i've seen running pgbouncer on the same host drop performance by about 33%, and switching from simple protocol to extended protocol single use prepared statements drop performance by 33%.
deleted 9y ago
[deleted]
mk89 9y ago
Indeed! What a great way to see failure: what went wrong, how to improve without blaming it on the poor guy. I wish all companies were like this - very forward thinking! Funny thing is... this morning on my commute I was watching https://www.oreilly.com/ideas/developing-a-chaos-architecture-mindset https://www.oreilly.com/ideas/developing-a-chaos-architectur... - which I recommend to anyone!
- twic 9y ago
  I think it is now widely understood that best practice is to blame processes, not people. However, i do have a niggling worry that letting people off the hook risks stunting their personal growth. If you can make mistakes and not bear any responsibility for it, what is driving you to get better at doing your job?
  - oblio 9y ago
    In any well run engineering org there is still the grapevine in operation. You really don't want to be the butt of all the "Joe the prod database killer" jokes.
  - IAmEveryone 9y ago
    I think when the circumstances actually show someone was grossly negligent, it's very hard to convincingly pretend that only processes are to blame. Remember that the current thinking is working against thousands of years of a cultural pattern assigning responsibility. It's also interesting how many seem to be using this idea meant to be applied to internal processes and individuals and applying it to the companies themselves. The most significant example was Gitlab: they had something like four backup mechanisms for their database, yet three(!) of them had not been working for months, with nobody noticing. Then, someone actually dropped the production database. The last working backup system also had some problems I can't remember right now. Yet in the comments here, there was barely a hint of criticism of Gitlab. Instead, they were lauded for not lynching the poor guy who made the last of what must have been dozens of terrible decisions leading to the incident. Travis' incident here does actually seem like the sort of freak accident that one can't rule out completely, at least not at a company of their size. But forgiveness might not always be warranted.
MattRogish 9y ago
What is interesting is the DB cleaner gem actually does check for `RAILS_ENV=production` and a `DATABASE_URL` that is remote: https://github.com/DatabaseCleaner/database_cleaner#safeguards https://github.com/DatabaseCleaner/database_cleaner#safeguar... It's possible they disabled the localhost check at some point. Edit: It does now, after Travis folk fixed it. :D And, Rails (which I assume they're using) typically loads DB credentials from a `database.yml` file for a particular `RAILS_ENV` (local dev, local automated testing, prod). By setting the `DATABASE_URL` env var, they are overriding that (this is fine/expected behavior for app deployment as far as Rails is concerned; it's comparatively less common for localdev). So that bypassed the `RAILS_ENV=prod` check that might also have caught this (e.g. `RAILS_ENV` was undefined or `development`). So I'd really have loved it if they dug into some of the UX factors to this issue: * Why do they allow remote access to their production database? (Is it too hard to get a local database copy? "For debugging purposes"? Why was the eng in prod the day before? Was it unintentional? Should we revisit those assumptions? Should we resolve whatever issue that caused us to shortcut by allowing remote, direct prod db access?) ̶ ̶W̶h̶y̶ ̶D̶B̶ ̶c̶l̶e̶a̶n̶e̶r̶ ̶a̶l̶l̶o̶w̶e̶d̶ ̶n̶o̶n̶-̶l̶o̶c̶a̶l̶ ̶(̶o̶l̶d̶e̶r̶ ̶v̶e̶r̶s̶i̶o̶n̶?̶ ̶m̶i̶s̶c̶o̶n̶f̶i̶g̶u̶r̶e̶d̶?̶ ̶e̶t̶c̶.̶)̶ Why, for localdev, they override database.yml and use env vars (which I've found is more cumbersome to use and not as common). Yeah, in production you should use RAILS_ENV/DATABASE_URL/etc. - so are they attempting to have prod parity? Why or why not? * Why are folks routinely logging in (for debugging purposes) with a DB user that is, effectively, root? I bet that user can `drop table` too. Should folks have a limited access account and then jump through a hoop or two for root? It sounds like they want to "debug" prod by running some local rails tools (rails c?) with DATABASE_URL set to the prod db. Is that the "best" way to do it? heroku etc. actually open a rails console in the environment and not locally.
- codahale 9y ago
  Those safeguards were added by Travis CI folks as one of their remediation action items: https://github.com/DatabaseCleaner/database_cleaner/pull/521 https://github.com/DatabaseCleaner/database_cleaner/pull/521
  - MattRogish 9y ago

hardwaresofton 9y ago

At the very least, different credentials in the database for each environment would make sense. I prefer thorough E2E testing to mocked large-component integration testing (i.e. spinning up a db vs mocking it out) so I can totally understand needing to run tests against a database but yeah reading this was a collar-pulling "yikes" moment for me. Their remediation steps are good though, for a start... They might want to look into spinning up completely new databases programattically for tests rather than connecting to one...

pech0rin 9y ago
Postgres has credentials in the URL, so I am sure they already had different credentials, but the URL was set.
- joelcornett 9y ago
  This seems like it fits the narrative. Still, WTF? A quick google tells me that PostgreSQL supports SSL cert-based authN. The prod environment (and possibly an Ops host) should be the only ones with the right certs to connect to the prod DB.
  - dalore 9y ago
    Creating an ssl connection to the postgres server might add extra connection time (Yes could be prevented by persistent connections but that's not always feasible, especially in a cloud based environment). So it's not the best solution. Postgres pg_hba.conf does support access by ip address easily enough. For access to the production databases the ip addresses should be limited to those that need access. Or if not in postgres, a firewall could limit it. So if a developer accidentally tried to wipe it, they wouldn't be allowed to connect. If they actually needed to connect, they would first have to add their ip address (or use a bastion host).
    - lvh 9y ago
      The GP was presumably talking about TLS with mutual auth. There’s no excuse for not having TLS at all enabled, but mTLS is a great extra security feature. The cost of mTLS (performance wise) is negligible, and if you can’t keep a persistent database connection up (because cloud?!) but also can’t do TLS because perf then I don’t know what to tell you. The only performance issue TLS has is that it isn’t used enough.
      - foobarbazetc 9y ago
        As someone who has helped many, many companies with Postgres I can tell you that nobody runs PG over TLS in production if everything’s talking over private links. There’s zero reason to do this.
    - benmmurphy 9y ago
      if you are running PG like a KV store (simple lookups/simple updates/inserts) where most of the dataset fits in memory then it quickly becomes CPU bound and changes in PG configuration/access patterns can have large effects. for example i've seen running pgbouncer on the same host drop performance by about 33%, and switching from simple protocol to extended protocol single use prepared statements drop performance by 33%.

deleted 9y ago

[deleted]

mk89 9y ago

Indeed! What a great way to see failure: what went wrong, how to improve without blaming it on the poor guy. I wish all companies were like this - very forward thinking! Funny thing is... this morning on my commute I was watching https://www.oreilly.com/ideas/developing-a-chaos-architecture-mindset https://www.oreilly.com/ideas/developing-a-chaos-architectur... - which I recommend to anyone!

twic 9y ago
I think it is now widely understood that best practice is to blame processes, not people. However, i do have a niggling worry that letting people off the hook risks stunting their personal growth. If you can make mistakes and not bear any responsibility for it, what is driving you to get better at doing your job?
- oblio 9y ago
  In any well run engineering org there is still the grapevine in operation. You really don't want to be the butt of all the "Joe the prod database killer" jokes.
- IAmEveryone 9y ago
  I think when the circumstances actually show someone was grossly negligent, it's very hard to convincingly pretend that only processes are to blame. Remember that the current thinking is working against thousands of years of a cultural pattern assigning responsibility. It's also interesting how many seem to be using this idea meant to be applied to internal processes and individuals and applying it to the companies themselves. The most significant example was Gitlab: they had something like four backup mechanisms for their database, yet three(!) of them had not been working for months, with nobody noticing. Then, someone actually dropped the production database. The last working backup system also had some problems I can't remember right now. Yet in the comments here, there was barely a hint of criticism of Gitlab. Instead, they were lauded for not lynching the poor guy who made the last of what must have been dozens of terrible decisions leading to the incident. Travis' incident here does actually seem like the sort of freak accident that one can't rule out completely, at least not at a company of their size. But forgiveness might not always be warranted.

MattRogish 9y ago

What is interesting is the DB cleaner gem actually does check for `RAILS_ENV=production` and a `DATABASE_URL` that is remote: https://github.com/DatabaseCleaner/database_cleaner#safeguards https://github.com/DatabaseCleaner/database_cleaner#safeguar... It's possible they disabled the localhost check at some point. Edit: It does now, after Travis folk fixed it. :D And, Rails (which I assume they're using) typically loads DB credentials from a `database.yml` file for a particular `RAILS_ENV` (local dev, local automated testing, prod). By setting the `DATABASE_URL` env var, they are overriding that (this is fine/expected behavior for app deployment as far as Rails is concerned; it's comparatively less common for localdev). So that bypassed the `RAILS_ENV=prod` check that might also have caught this (e.g. `RAILS_ENV` was undefined or `development`). So I'd really have loved it if they dug into some of the UX factors to this issue: * Why do they allow remote access to their production database? (Is it too hard to get a local database copy? "For debugging purposes"? Why was the eng in prod the day before? Was it unintentional? Should we revisit those assumptions? Should we resolve whatever issue that caused us to shortcut by allowing remote, direct prod db access?) ̶ ̶W̶h̶y̶ ̶D̶B̶ ̶c̶l̶e̶a̶n̶e̶r̶ ̶a̶l̶l̶o̶w̶e̶d̶ ̶n̶o̶n̶-̶l̶o̶c̶a̶l̶ ̶(̶o̶l̶d̶e̶r̶ ̶v̶e̶r̶s̶i̶o̶n̶?̶ ̶m̶i̶s̶c̶o̶n̶f̶i̶g̶u̶r̶e̶d̶?̶ ̶e̶t̶c̶.̶)̶ Why, for localdev, they override database.yml and use env vars (which I've found is more cumbersome to use and not as common). Yeah, in production you should use RAILS_ENV/DATABASE_URL/etc. - so are they attempting to have prod parity? Why or why not? * Why are folks routinely logging in (for debugging purposes) with a DB user that is, effectively, root? I bet that user can `drop table` too. Should folks have a limited access account and then jump through a hoop or two for root? It sounds like they want to "debug" prod by running some local rails tools (rails c?) with DATABASE_URL set to the prod db. Is that the "best" way to do it? heroku etc. actually open a rails console in the environment and not locally.

codahale 9y ago
Those safeguards were added by Travis CI folks as one of their remediation action items: https://github.com/DatabaseCleaner/database_cleaner/pull/521 https://github.com/DatabaseCleaner/database_cleaner/pull/521
- MattRogish 9y ago