RFC: script to run sqlalchemy migrations on the db

[Toshio Kuratomi]

Mike McGrath wrote:
> On Fri, 8 Aug 2008, Toshio Kuratomi wrote:
>> # 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?

The additions sound reasonable::

app2 $ migrate-runner -h db2 -d fas2 /usr/share/fas/database
This script must create a temporary db user, fas2temp on db2.
That user will have permission to modify anything in the fas2 database.
If you stop this script in the middle of running you will want to remove 
the created user from the db.
To continue, enter your password for sudo on db2:

Running: ssh db2 pg_temp_user --verbose --create fas2
pg_temp_user: checking for db fas2... yes
   [sudo -u postgres psql select from pg_users where name = 'fas2temp']
pg_temp_user: checking for existing fas2temp... no
    [if yes, then abort and have the admin remove the account, check for 
other issues, etc]
pg_temp_user: generating password... success
pg_temp_user: create fas2temp... success
    [sudo -u postgres cat temppasswdfile | sudo -u postgres createuser 
fas2temp -P -E && sudo -u postgres rm temppasswdfile || sudo -u postgres 
rm temppasswdfile]
pg_temp_user: setting fas2temp permissions on fas2
    [echo "grant all on fas2 to fas2temp" | sudo -u postgres psql fas2]
    [print fas2temp passwd to stdout which migrate-runner captures]
Received password for fas2temp
Running migrate
    [various script invocations that loupgaroublond helps me create]
Running: ssh db2 pg_temp_user --verbose --remove fas2
pg_temp_user: checking for db fas2... yes
pg_temp_user: checking for existing fas2temp... yes
pg_temp_user: removing fas2temp... success
Successfully upgraded database

-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://listman.redhat.com/archives/fedora-infrastructure-list/attachments/20080810/919f6079/attachment.sig>