help with users and postgres

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

help with users and postgres

Davide Cittaro
Dear all, 
Our local galaxy instance uses apache authentication to log users. This said, some users have no "User Name" and some other have a wrong "User name" (probably because they have been added before the apache integration). I just wonder if there's a way to modify the user names directly in the postgres database (I'm pretty sure that is possible...).

Thanks

d
/*
Davide Cittaro, PhD

Cogentech - Consortium for Genomic Technologies
via adamello, 16
20139 Milano
Italy

tel.: +39(02)574303007
*/





_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev
Reply | Threaded
Open this post in threaded view
|

Re: help with users and postgres

Hans-Rudolf Hotz
Hi Davide

We were in a similar situation when we switched to external
authentication. Although, only one user was affected and we were using
MySQL.

I could fix it by changing the contents of the "galaxy_user" with a few
sql statements. BUT, be careful! you can do a lot of damage to the database.

Regards, Hans




On 01/25/2011 02:40 PM, Davide Cittaro wrote:

> Dear all,
> Our local galaxy instance uses apache authentication to log users. This said, some users have no "User Name" and some other have a wrong "User name" (probably because they have been added before the apache integration). I just wonder if there's a way to modify the user names directly in the postgres database (I'm pretty sure that is possible...).
>
> Thanks
>
> d
> /*
> Davide Cittaro, PhD
>
> Cogentech - Consortium for Genomic Technologies
> via adamello, 16
> 20139 Milano
> Italy
>
> tel.: +39(02)574303007
> e-mail: [hidden email]
> */
>
>
>
>
>
>
>
>
> _______________________________________________
> galaxy-dev mailing list
> [hidden email]
> http://lists.bx.psu.edu/listinfo/galaxy-dev
_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev
Reply | Threaded
Open this post in threaded view
|

Re: help with users and postgres

Davide Cittaro

On Jan 25, 2011, at 3:40 PM, Hans-Rudolf Hotz wrote:

Hi Davide

We were in a similar situation when we switched to external authentication. Although, only one user was affected and we were using MySQL.

I could fix it by changing the contents of the "galaxy_user" with a few sql statements. BUT, be careful! you can do a lot of damage to the database.


Could you post the sql statement? I guess that should work in the same way!
Thanks
d

Regards, Hans




On 01/25/2011 02:40 PM, Davide Cittaro wrote:
Dear all,
Our local galaxy instance uses apache authentication to log users. This said, some users have no "User Name" and some other have a wrong "User name" (probably because they have been added before the apache integration). I just wonder if there's a way to modify the user names directly in the postgres database (I'm pretty sure that is possible...).

Thanks

d
/*
Davide Cittaro, PhD

Cogentech - Consortium for Genomic Technologies
via adamello, 16
20139 Milano
Italy

tel.: +39(02)574303007
e-mail: [hidden email]
*/








_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev

/*
Davide Cittaro, PhD

Cogentech - Consortium for Genomic Technologies
via adamello, 16
20139 Milano
Italy

tel.: +39(02)574303007
*/





_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev
Reply | Threaded
Open this post in threaded view
|

Re: help with users and postgres

Hans-Rudolf Hotz


On 01/25/2011 03:44 PM, Davide Cittaro wrote:

>
> On Jan 25, 2011, at 3:40 PM, Hans-Rudolf Hotz wrote:
>
>> Hi Davide
>>
>> We were in a similar situation when we switched to external authentication. Although, only one user was affected and we were using MySQL.
>>
>> I could fix it by changing the contents of the "galaxy_user" with a few sql statements. BUT, be careful! you can do a lot of damage to the database.
>>
>
> Could you post the sql statement? I guess that should work in the same way!
> Thanks
> d

Have a look at the "galaxy_user" table to identify the 'broken' row, ie
the wrong "e-mail".


and then you can execute:

    update galaxy_user set email = "[hidden email]" where email =
    "[hidden email]";



you might have to change the "name" in the "role" table accordingly.....


Once again be careful with what you are doing, and don't blame me if you
create a mess   ;)



Hans

>> Regards, Hans
>>
>>
>>
>>
>> On 01/25/2011 02:40 PM, Davide Cittaro wrote:
>>> Dear all,
>>> Our local galaxy instance uses apache authentication to log users. This said, some users have no "User Name" and some other have a wrong "User name" (probably because they have been added before the apache integration). I just wonder if there's a way to modify the user names directly in the postgres database (I'm pretty sure that is possible...).
>>>
>>> Thanks
>>>
>>> d
>>> /*
>>> Davide Cittaro, PhD
>>>
>>> Cogentech - Consortium for Genomic Technologies
>>> via adamello, 16
>>> 20139 Milano
>>> Italy
>>>
>>> tel.: +39(02)574303007
>>> e-mail: [hidden email]
>>> */
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> galaxy-dev mailing list
>>> [hidden email]
>>> http://lists.bx.psu.edu/listinfo/galaxy-dev
>
> /*
> Davide Cittaro, PhD
>
> Cogentech - Consortium for Genomic Technologies
> via adamello, 16
> 20139 Milano
> Italy
>
> tel.: +39(02)574303007
> e-mail: [hidden email]
> */
>
>
>
>
>
_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev
Reply | Threaded
Open this post in threaded view
|

Re: help with users and postgres

Assaf Gordon-2
On 01/25/2011 10:25 AM, Hans-Rudolf Hotz wrote:
>> On Jan 25, 2011, at 3:40 PM, Hans-Rudolf Hotz wrote:
> [...]
> Once again be careful with what you are doing, and don't blame me if you
> create a mess ;)

-1-
PgAdmin ( http://www.pgadmin.org/ ) is a GUI application to manage Postgres databases - it might be a friendlier option than running direct SQL queries with 'psql'.

-2-
Before directly manipulation your DB, you can/should back it up with the following command:
$ pg_dump -U USER DB > galaxy_db.sql

If something does go wrong, you can at least revert the data to a valid state.

-3-
I use the following script to replicate the production database to the development database, and then it's easier to experiment with any SQL command you want without affecting the production server:
=======
#!/bin/sh

DATE=$(date "+%Y_%m_%d_%H%M%S")

FILE="galaxy_db_prod_to_devel_${DATE}.sql.gz"

DEST=/home/gordon/projects/galaxy_db_backups/
FILE="${DEST}${FILE}"

echo "Dumping Prod-DB to:"
echo "  $FILE"
echo "(Enter Galaxyprod Password)"
pg_dump -c -U galaxyprod galaxyprod | sed 's/galaxyprod/galaxydevel/g' | gzip > "$FILE" || exit 1

echo
echo "Dropping and re-creating Galaxy-Devel database."
echo "press CTRL-C to abort or ENTER to continue."
read
echo "(Enter GalaxyDevel Password)":
zcat "$FILE" | psql -U galaxydevel galaxydevel || exit 1
======================

The "pg_dump -c" will add the SQL commands to drop and re-create the tables, so all tables in "galaxydevel" database are an exact copy.

-gordon
_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev
Reply | Threaded
Open this post in threaded view
|

Re: help with users and postgres

Davide Cittaro
In reply to this post by Hans-Rudolf Hotz

On Jan 25, 2011, at 4:25 PM, Hans-Rudolf Hotz wrote:
Have a look at the "galaxy_user" table to identify the 'broken' row, ie the wrong "e-mail".


and then you can execute:

  update galaxy_user set email = "[hidden email]" where email =
  "[hidden email]";

update galaxy_user set username = 'foo' where email = 'foo@bar'

worked

thanks

d

/*
Davide Cittaro, PhD

Cogentech - Consortium for Genomic Technologies
via adamello, 16
20139 Milano
Italy

tel.: +39(02)574303007
*/





_______________________________________________
galaxy-dev mailing list
[hidden email]
http://lists.bx.psu.edu/listinfo/galaxy-dev