Migrations, superusers, security and least privileges

The one thing I don’t like about migrations is the security, and I’d like to know if anyone is interested in working with me to improve it (or knows an existing way to accomplish the same thing). Here’s the scenario:

  • I always create specific database users that only have CRUD priveleges, and run my web apps with these users

  • I need to log in with a superuser to run migrations and other test functions (like clone_structure)

  • I never want to store a superuser database password, encrypted or not, in any of my database.yml files

  • I don’t want to have to modify database.yml everytime I deploy, type in superuser credentials, migrate and then change them back
    Here’s my ideal scenario:

  • In the database.yml file, I would write :prompt for the password

  • Whenever I run a command that uses that environment, the command line prompts me for the password

  • When I want to migrate to the production database, I can just create a new environment called “migrate_production”, specify the production database and superuser username, and :promt for the password - that way the production site uses the stored password for the limited user, but I can deploy as a superuser.

  • While deploying with Capistrano, I would set the RAILS_ENV to migrate_production and capistrano would prompt me for the password when it came across that part
    This can’t be hard, and I’m willing to put the legwork in but I’d love to know if a) anyone has accomplished this in a more elegant (or less elegant but equally functional) way and b) if anyone would be interested in working with me to create something that would be robust enough to submit to core (plugins are the first option, of course, but I think running databases with least privileges is an important enough subject to be included in core).

I’d appreciate any feedback, suggestions or people willing to help.

Jeff Dean