[OBM Admin] user id handling change

[Bán Miklós]

Hi All,

sorry, the hash algorithm I introduced few days ago wasn't the best
solution, because of two reasons:

- the crypt hash is not necessarily URL friendly
- I think an universal hash-Id would be better for the users which means
  the user can use the same hash id on every server.

So, I've changed the algorithm:

UPDATE users SET "user"=md5(encrypt(email::bytea,'obm','des'))

To use this you must change the "user" columns length in the users table
from 20 to 32

Also a made a trigger function to this hash generation automatically:

obm_useridhash:
BEGIN
    SELECT md5(encrypt(NEW.email::bytea,'obm','des')) INTO new."user";
    RETURN NEW;
END

I made these changes on the following servers:
openbiomaps.org
de2.openbiomaps.org
elte.openbiomaps.org
senckenberg
milvus new

Only the gekko users need to do this steps for themselves!

cheers, Miki


On Wed, 23 Nov 2016 10:55:53 +0100
Bán Miklós <banm at vocs.unideb.hu> wrote:

> Hi All,
> 
> I've realised a weakness with user's id handling. I used the real sql
> id in GET requests which wasn't a wise thing.
> 
> Therefore I changed the code to use an encrypted string instead of it.
> All requests like this:
> http://openbiomaps.org/projects/dinpi/index.php?userprofile&id=1
> 
> changed to something like this:
> 
> http://openbiomaps.org/projects/dinpi/index.php?userprofile&id=be19Wwd4IslHg
> 
> Only one modification necessary in the biomaps SQL database:
> 
> UPDATE users SET "user"= crypt(id::text, email)
> 
> I run it on all servers except the GEKKOs! If you have a gekko and you
> would like to follow the software updates, please run this SQL code
> yourself!
> 
> Later I will extend the deb package to make these kind of updates
> automatically.
> 
> Miki
> 



-- 
Miklós Bán, PhD
MTA-DE "Lendület" Behavioural Ecology Research Group
Department of Evolutionary Zoology, University of Debrecen
H-4010 Debrecen, Egyetem tér 1.
Phone:  +36 52 512-900 ext. 62356
http://zoology.unideb.hu/?m=Miklos_Ban
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~