4 ms·

Even though I've given in to using the "automated schema updates", or migrations, approach (Technique 1 in the article), I'm still uncomfortable with the idea o

Even though I've given in to using the "automated schema updates", or migrations, approach (Technique 1 in the article), I'm still uncomfortable with the idea of the application tier having permission/ability to manage database schema and execute DDL. I feel that it removes a key layer of defense-in-depth, and opens up the possibility for bugs and exploited vulnerabilities in application code to have hugely negative impacts. Even ORMs make me nervous for similar reasons, since they essentially enable the application tier to pass into the database any/all queries and DML. But like migrations, I allow ORMs for the convenience they provide during development.

aschatten 8y ago
The live application serving traffic does not need to have DDL permissions. The migration code can live in the same code base/application, but can be executed as a separate task with escalated privileges. Ideally, the same build should be used in every environment and all the configurations such as db endpoint, username and password must be external to the built and fed from the environment. The migration command can be a part of application, but only invoked automatically in dev environment. Most of ORMs allow to configure whether schema initialization happens at the startup or not. During deploy, the migration part can be executed separately in prod environment, before the actual deploy. Different tools provide different "hooks": e.g. heroku has release phase(https://devcenter.heroku.com/articles/release-phase https://devcenter.heroku.com/articles/release-phase), in spinnker you can add a separate stage for db migrations: https://blog.spinnaker.io/deploying-database-migrations-with-spinnaker-and-kubernetes-6e6761f9d28a https://blog.spinnaker.io/deploying-database-migrations-with..., which can use a different db user. In this case, migration functionality is disabled at startup in production environment and runtime db user need not have DDL privilege, because by the time it starts up, migration phase must have been finished.

aschatten 8y ago

The live application serving traffic does not need to have DDL permissions. The migration code can live in the same code base/application, but can be executed as a separate task with escalated privileges. Ideally, the same build should be used in every environment and all the configurations such as db endpoint, username and password must be external to the built and fed from the environment. The migration command can be a part of application, but only invoked automatically in dev environment. Most of ORMs allow to configure whether schema initialization happens at the startup or not. During deploy, the migration part can be executed separately in prod environment, before the actual deploy. Different tools provide different "hooks": e.g. heroku has release phase(https://devcenter.heroku.com/articles/release-phase https://devcenter.heroku.com/articles/release-phase), in spinnker you can add a separate stage for db migrations: https://blog.spinnaker.io/deploying-database-migrations-with-spinnaker-and-kubernetes-6e6761f9d28a https://blog.spinnaker.io/deploying-database-migrations-with..., which can use a different db user. In this case, migration functionality is disabled at startup in production environment and runtime db user need not have DDL privilege, because by the time it starts up, migration phase must have been finished.