RFC: script to run sqlalchemy migrations on the db

[Toshio Kuratomi]

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.

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.

-Toshio

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: OpenPGP digital signature
Url : http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20080808/6f137170/attachment.bin