7 ms·

That single sentence contains multitudes: * Production should be immutable * No one doing dev in a dev environment should have such trivial access to prod *

That single sentence contains multitudes: * Production should be immutable * No one doing dev in a dev environment should have such trivial access to prod * Are there still good reasons for a migration to drop all tables? I guess it's for the dev environment to etch-a-sketch to a known state? Yikes.

anonzzzies 3y ago
> No one doing dev in a dev environment should have such trivial access to prod It’s the new and hip ‘cloud’! Probably using planetscale or something like that, which (last I checked, maybe it changed but wasn’t on), doesn’t even have ip protections outside the mysql user settings (while bad, would’ve protected them). > Are there still good reasons for a migration to drop all tables? We haven’t found any.
- willsmith72 3y ago
  really? i've definitely done it before on my local as a quicker alternative to cleaning up the docker container/volume, doesn't seem that bad ofc i'd think differently if i was also putting write-permission prod credentials into my machine, but luckily i haven't been in many places doing that
- rwieruch 3y ago
  PlanetScale has Safe Migrations which you can enabled for your production DB (branch). Wondering though whether this will protect against everything mentioned here. https://planetscale.com/docs/concepts/safe-migrations https://planetscale.com/docs/concepts/safe-migrations
  - samlambert 3y ago
    Safe Migrations would prevent this completely. PlanetScale also allows you to restore multiple backups in parallel.
- viiralvx 3y ago
  I'd suggest reading up on what some of these new database providers are doing to help prevent or fix mistakes like this. Since you mentioned PlanetScale, I'll use them as an example. 1) PlanetScale has IP ACLs, which locks down passwords to specific IP addresses. [1] Additionally, with TailScale or another VPN solution, locking down based on IP isn't necessary foolproof. 2) They also have Safe Migrations. When enabled, it prevents DDL from being run directly on a database. [2] Additionally, using deploy requests for zero-downtime schema migrations also allows you to use reverts, which will revert the migration. [3] [1] https://planetscale.com/blog/introducing-ip-restrictions https://planetscale.com/blog/introducing-ip-restrictions [2] https://planetscale.com/docs/concepts/safe-migrations https://planetscale.com/docs/concepts/safe-migrations [3] https://planetscale.com/blog/behind-the-scenes-how-schema-reverts-work https://planetscale.com/blog/behind-the-scenes-how-schema-re...
tianzhou 3y ago
We have been working on bytebase (https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase) for 3+ years to address this. With a change review workflow, environment propagations, and try not to disturb the dev flow if possible.
briffle 3y ago
In addition, Every centralized storage, and every cloud provider lets you take a snapshot of a database's disks. We can restore a 10TB disk in about 12 minutes. its much faster to snapshot, do migration, then if necessary, drop disk and remake from snapshot. (and then replay the replay any other WAL changes up to the exact second you want with a tool like barman, wall-e, pg_backreset, etc. Postgresql backups are critical for disaster recovery, but the restores are so very, very slow, they should be a last resort.

anonzzzies 3y ago

> No one doing dev in a dev environment should have such trivial access to prod It’s the new and hip ‘cloud’! Probably using planetscale or something like that, which (last I checked, maybe it changed but wasn’t on), doesn’t even have ip protections outside the mysql user settings (while bad, would’ve protected them). > Are there still good reasons for a migration to drop all tables? We haven’t found any.

willsmith72 3y ago
really? i've definitely done it before on my local as a quicker alternative to cleaning up the docker container/volume, doesn't seem that bad ofc i'd think differently if i was also putting write-permission prod credentials into my machine, but luckily i haven't been in many places doing that
rwieruch 3y ago
PlanetScale has Safe Migrations which you can enabled for your production DB (branch). Wondering though whether this will protect against everything mentioned here. https://planetscale.com/docs/concepts/safe-migrations https://planetscale.com/docs/concepts/safe-migrations
- samlambert 3y ago
  Safe Migrations would prevent this completely. PlanetScale also allows you to restore multiple backups in parallel.
viiralvx 3y ago
I'd suggest reading up on what some of these new database providers are doing to help prevent or fix mistakes like this. Since you mentioned PlanetScale, I'll use them as an example. 1) PlanetScale has IP ACLs, which locks down passwords to specific IP addresses. [1] Additionally, with TailScale or another VPN solution, locking down based on IP isn't necessary foolproof. 2) They also have Safe Migrations. When enabled, it prevents DDL from being run directly on a database. [2] Additionally, using deploy requests for zero-downtime schema migrations also allows you to use reverts, which will revert the migration. [3] [1] https://planetscale.com/blog/introducing-ip-restrictions https://planetscale.com/blog/introducing-ip-restrictions [2] https://planetscale.com/docs/concepts/safe-migrations https://planetscale.com/docs/concepts/safe-migrations [3] https://planetscale.com/blog/behind-the-scenes-how-schema-reverts-work https://planetscale.com/blog/behind-the-scenes-how-schema-re...

tianzhou 3y ago

We have been working on bytebase (https://github.com/bytebase/bytebase https://github.com/bytebase/bytebase) for 3+ years to address this. With a change review workflow, environment propagations, and try not to disturb the dev flow if possible.

briffle 3y ago

In addition, Every centralized storage, and every cloud provider lets you take a snapshot of a database's disks. We can restore a 10TB disk in about 12 minutes. its much faster to snapshot, do migration, then if necessary, drop disk and remake from snapshot. (and then replay the replay any other WAL changes up to the exact second you want with a tool like barman, wall-e, pg_backreset, etc. Postgresql backups are critical for disaster recovery, but the restores are so very, very slow, they should be a last resort.