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
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,
    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
    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
    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
know if a) anyone has accomplished this in a more elegant (or less
but equally functional) way and b) if anyone would be interested in
with me to create something that would be robust enough to submit to
(plugins are the first option, of course, but I think running databases
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