RFC: script to run sqlalchemy migrations on the db

[Yaakov Nemoy]

On Mon, Aug 11, 2008 at 4:31 PM, Mike McGrath <mmcgrath at redhat.com> wrote:
> 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.

I see then.  My recommendation is to have an outside wrapper that just
takes random db url stuff, including a superuser username and
password, creates a new superuser, passes the new user to migrate.py
and lets migrate.py take over from there.  It's doable, I just don't
see what security we gain, over having certain dedicated users (namely
toshio and/or ricky) who are the only ones who can run migrate.py,
using the superuser password.

-Yaakov