RFC: script to run sqlalchemy migrations on the db

[Mike McGrath]

On Fri, 8 Aug 2008, Toshio Kuratomi wrote:

> FAS started using the python-migrate package to update its db.  This is a good
> thing for third-parties that want to install their own FAS server as it lets
> us ship the database changes in a way that is easy for those users to apply to
> their own production databases.
>
> However, it doesn't work very well in our particular environment because we're
> a bit more strict about our permissions than the migrate authors envision.  In
> order to perform migrations, you need to have a user that can modify the
> schema for the db.  This is either hte owner of the db or the superuser.  In
> our setup, we create the db with the superuser and then run our web apps with
> another user.  This prevents the normal web app from modifying the db schema.
>

A classic complaint I have between dev's and sysadmin's.  I think what you
have below is good, generally sysadmins don't want to install a bunch of
python libraries on the database for a specific application.

> To work around this I propose writing a script that does this:
> # 1) Create a db user.
> # 2) grant access to all the values in the specified db
> # 3) run the migrate commands to create the manage.py script and run it with
> the new username and password
> # 4) Reassign any new tables to the postgres user
> # 5) Remove the temporary db user
>
> The command line to invoke it would then look like this:
>
> sudo -u postgres migrate-runner -h DBHOST -d DBNAME MIGRATE_REPO
>
> Does this look:
> 1) Doable -- loupgaroublond I'm looking at you to tell me what the migrate
> commands will be and if there's any caveats to this
>
> 2) Secure -- the point of this would be to keep protecting the db superuser
> with a sudo account on db2 and not being able to use it without a shell on
> db2.  If the security of this solution is less than what giving a password to
> a superuser account would be then we might as well do that instead.
>
> If this looks good, I'll work on coding something up.
>

I'd almost have the sysadmins run a command on one of the app servers that
has workflow like this

1) pre upgrade check
2) Prompt the user to run a command on the database server, cut and paste.
3) click OK or agree or something
4) Perform upgrade
5) If possible, have the upgrade script remove the user created in step 2,
otherwise prompt
6) win.

I think those steps work with the steps you outlined above.  I'm curious
what others think?

	-Mike