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|7 years ago
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), in spinnker you can add a separate stage for db migrations: 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.