FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > Redhat > Fedora Infrastructure

 
 
LinkBack Thread Tools
 
Old 08-09-2008, 02:17 AM
Toshio Kuratomi
 
Default RFC: script to run sqlalchemy migrations on the db

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

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-09-2008, 06:05 PM
Mike McGrath
 
Default RFC: script to run sqlalchemy migrations on the db

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

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-10-2008, 03:17 PM
Toshio Kuratomi
 
Default RFC: script to run sqlalchemy migrations on the db

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

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-11-2008, 07:55 AM
"Yaakov Nemoy"
 
Default RFC: script to run sqlalchemy migrations on the db

2008/8/9 Toshio Kuratomi <a.badger@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.

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.
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?
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.


As for feasibility, I don't think the migrate scripts themselves can
create new users on the fly, nor do I think that's where we want to do
it. We probably want to create per project wrappers that get called
instead of manage.py. Have we spoken to upstream about this yet too?

Or maybe I just need to wake up more, and I'll get it.

-Yaakov

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-11-2008, 02:31 PM
Mike McGrath
 
Default RFC: script to run sqlalchemy migrations on the db

On Mon, 11 Aug 2008, Yaakov Nemoy wrote:

> 2008/8/9 Toshio Kuratomi <a.badger@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

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-11-2008, 03:30 PM
"Yaakov Nemoy"
 
Default RFC: script to run sqlalchemy migrations on the db

On Mon, Aug 11, 2008 at 4:31 PM, Mike McGrath <mmcgrath@redhat.com> wrote:
> On Mon, 11 Aug 2008, Yaakov Nemoy wrote:
>
>> 2008/8/9 Toshio Kuratomi <a.badger@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

_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 
Old 08-11-2008, 04:43 PM
Toshio Kuratomi
 
Default RFC: script to run sqlalchemy migrations on the db

Toshio Kuratomi wrote:


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


[One more step needed here:]
pg_temp_user: updating table ownership to postgres... success
[This detects all the tables in the fas2 db and reassigns ownership
from the fas2temp user to the database superuser.]



pg_temp_user: removing fas2temp... success
Successfully upgraded database

-Toshio




_______________________________________________
Fedora-infrastructure-list mailing list
Fedora-infrastructure-list@redhat.com
https://www.redhat.com/mailman/listinfo/fedora-infrastructure-list
 

Thread Tools




All times are GMT. The time now is 01:01 AM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright 2007 - 2008, www.linux-archive.org