RFC: script to run sqlalchemy migrations on the db

[Mike McGrath]

On Mon, 11 Aug 2008, Yaakov Nemoy wrote:

> 2008/8/9 Toshio Kuratomi <a.badger at gmail.com>:
> > 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.
>
> I don't see how this is any more secure than just either granting some
> user sudo or creating a long term admin DB role just for the FAS DB
> that is well protected.
>

You must be a developer ;-)

> As I see it:
>
> 1) the FAS run time itself has not been security audited and vetted,
> therefore the least damage it can do to the DB the better.

Even if it had...  You shouldn't consider it 'secure'.  We've done plenty
of audits.  AFAIK there is no industry standard for vetting.

> 2) Most of the admins, or rather the people in charge of upgrading FAS
> on our servers have been security audited and vetted through a system
> of mutual respect in a meritocracy.  Why do we need a temporary
> superuser account every time we upgrade?

Those users don't have db accounts, fas does.  And we don't want to give
the fas user more rights then it needs.

> 3) If we store the long term superuser account for FAS somewhere so
> upgrade scripts can be done automatically, then all I think we need is
> some SELinux / file perm policy that prevents FAS from accessing those
> files itself.
>

If the upgrade script can create a temporary user and get rid of it.  Why
risk having that account used during a non-upgrade time.  I'm fine with
using SElinux as a backup to primary security policies.  But using SELinux
as a primary security of some kind.  No thanks, we've had to disable it in
the past for various reasons before we were able to re-enable it even in
permissive mode.

	-Mike