module.py

#!/usr/bin/python
# -*- coding: UTF-8 -*-

from cStringIO import StringIO
from datetime import datetime, timedelta
from email.mime.application import MIMEApplication
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from threading import Thread
from uuid import uuid4
import base64
import cPickle
import functools
import hashlib
import json
import os
import smtplib

from PIL import Image, ImageDraw, ImageFont
from flask import Flask
from flask import jsonify
from flask import render_template, send_from_directory 
from flask import request
from flask import send_file
from flask import session
from flask import url_for
from flask_compress import Compress
from flask_session import Session
from werkzeug import abort, redirect
import gnupg
import pyotp
import pytz
import time
import qrcode
import webauthn

from NIST import NISTf

from const import pfsp
from functions import float_or_null
from functions import pbkdf2, AESCipher
from functions import pil2buffer
from functions import random_data
from functions import render_jinja_html
from functions import rotate_image_upon_exif
import config

Image.MAX_IMAGE_PIXELS = 1 * 1024 * 1024 * 1024

################################################################################

app = Flask( __name__ )
app.config.from_pyfile( 'config.py' )

Compress( app )
Session( app )

debug = os.environ.get( "DEBUG", False )
debug = debug in [ "True", "true", "1" ]

baseurl = os.environ.get( "BASEURL", "" )
envtype = os.environ.get( "ENVTYPE", "" )

################################################################################

gnupg._parsers.Verify.TRUST_LEVELS[ "ENCRYPTION_COMPLIANCE_MODE" ] = 23

################################################################################
#    Decorators

def session_field_required( field, value ):
    def decorator( func ):
        @functools.wraps( func )
        def wrapper_login_required( *args, **kwargs ):
            if not field in session:
                return redirect( url_for( "login" ) )
            
            elif not session.get( field ) == value:
                return redirect( url_for( "login" ) )
            
            return func( *args, **kwargs )
    
        return wrapper_login_required
    
    return decorator

def login_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not session.get( 'logged', False ) :
            return redirect( url_for( "login" ) )
        
        return func( *args, **kwargs )

    return wrapper_login_required

def referer_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not request.headers.get( "Referer", False ):
            return "referrer needed", 404
        
        return func( *args, **kwargs )

    return wrapper_login_required

def admin_required( func ):
    @functools.wraps( func )
    def wrapper_login_required( *args, **kwargs ):
        if not session.get( 'logged', False ) or not session.get( 'account_type', None ) == 1:
            return redirect( url_for( "login" ) )
        
        return func( *args, **kwargs )

    return wrapper_login_required

def redis_cache( ttl = 3600 ):
    def decorator( func ):
        @functools.wraps( func )
        def wrapper_cache( *args, **kwargs ):
            lst = []
            lst.append( func.__name__ )
            lst.extend( args )
            index = "_".join( lst )
            index = hashlib.sha256( index ).hexdigest()
            
            d = config.redis_shared.get( index )
            
            if d != None:
                buff = StringIO()
                buff.write( base64.b64decode( d ) )
                buff.seek( 0 )
                
                return cPickle.load( buff )
            
            else:
                d = func( *args, **kwargs )
                
                buff = StringIO()
                cPickle.dump( d, buff )
                buff.seek( 0 )
                d_cached = base64.b64encode( buff.getvalue() )
                
                config.redis_shared.set( index, d_cached, ex = ttl )
                
                return d
    
        return wrapper_cache
    return decorator

################################################################################
#    Generic routing

@app.route( '/ping' )
@app.route( baseurl + '/ping' )
def ping():
    if not config.db.check():
        config.db.connect()
        return abort( 500 )
    else:
        return "pong"

@app.route( baseurl + '/version' )
def version():
    from version import __version__, __branch__, __commit__, __commiturl__, __treeurl__
    return jsonify( {
        'error': False,
        'version': __version__,
        'branch': __branch__,
        'commit': __commit__,
        'commiturl': __commiturl__,
        'treeurl': __treeurl__
    } )

################################################################################
#   CDN serving

@app.route( baseurl + '/cdn/<path:subpath>' )
def send_cdn_files( subpath ):
    return send_from_directory( 'cdn', subpath )

################################################################################
#    App serving

@app.route( baseurl + '/app/<path:subpath>' )
def send_app_files( subpath ):
    return send_from_directory( 'app', subpath )

@app.route( baseurl + '/static/<path:subpath>' )
def send_static_files( subpath ):
    return send_from_directory( 'static', subpath )

################################################################################
#    Sessions

@app.before_request
def renew_session():
    session.permanent = True
    app.permanent_session_lifetime = timedelta( seconds = config.session_timeout )

@app.route( baseurl + '/is_logged' )
def is_logged():
    if session.get( "logged", False ):
        return "ok"
    
    else:
        return abort( 403 )

@app.route( baseurl + '/logout' )
def logout():
    session.clear()
    return redirect( url_for( 'home' ) )

def session_clear_and_prepare():
    session.clear()
    
    session[ 'process' ] = "login"
    session[ 'need_to_check' ] = [ 'password' ]
    session[ 'logged' ] = False
    session[ 'session_security_key' ] = str( uuid4() )

@app.route( baseurl + '/login' )
def login():
    session_clear_and_prepare()
    
    return render_template( 
        "login.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + '/do_login', methods = [ 'POST' ] )
def do_login():
    need_to_check = session.get( "need_to_check", [ 'password' ] )
    try:
        current_check = need_to_check[ 0 ]
    except:
        current_check = None
    
    session[ 'need_to_check' ] = need_to_check
    
    ############################################################################
    
    if current_check == "password":
        q = config.db.query( 'SELECT * FROM users WHERE username = %s', ( request.form.get( "username" ), ) )
        user = q.fetchone()
        
        if user == None:
            session_clear_and_prepare()
            
            return jsonify( {
                'error': False,
                'logged': False
            } )
        
        form_password = request.form.get( "password", None )
        
        if form_password == None or not pbkdf2( form_password, user[ 'password' ] ).verify():
            session_clear_and_prepare()
            
            return jsonify( {
                'error': False,
                'logged': False,
            } )
        
        elif not user[ 'active' ]:
            session_clear_and_prepare()
            
            return jsonify( {
                'error': False,
                'logged': False,
                'message': 'Your account is not activated. Please contact an administrator (icnml@unil.ch).'
            } )
        
        else:
            session[ 'username' ] = user[ 'username' ]
            session[ 'user_id' ] = user[ 'id' ]
            session[ 'password_check' ] = True
            
            session[ 'need_to_check' ].remove( current_check )
            session[ 'password' ] = pbkdf2( form_password, "AES256", 50000 ).hash()
            
            sql = "SELECT count( * ) FROM webauthn WHERE user_id = %s AND active = TRUE"
            security_keys_count = config.db.query_fetchone( sql, ( user[ 'id' ], ) )[ 'count' ]
            
            if security_keys_count > 0:
                session[ 'need_to_check' ].append( 'securitykey' )
            
            elif user[ 'totp' ]:
                session[ 'need_to_check' ].append( 'totp' )
            
            else:
                session_clear_and_prepare()
                
                return jsonify( {
                    "error": False,
                    "logged": False,
                    "message": "Second factor missing. Contact the ICNML administrator (icnml@unil.ch)."
                } )
    
    elif current_check == 'totp':
        q = config.db.query( 'SELECT username, totp FROM users WHERE username = %s', ( session[ 'username' ], ) )
        user = q.fetchone()
        
        if not pyotp.TOTP( user[ 'totp' ] ).verify( request.form[ "totp" ], valid_window = 2 ):
            session[ 'logged' ] = False
            return jsonify( {
                "error": False,
                "logged": False,
                "message": "Wrong TOTP",
                "time": time.time()
            } )
        
        else:
            session[ 'need_to_check' ].remove( current_check )
    
    if len( session[ 'need_to_check' ] ) == 0 and session.get( "password_check", False ):
        for key in [ 'process', 'need_to_check', 'password_check' ]:
            if key in session:
                session.pop( key )
            
        session[ 'logged' ] = True
        
        q = config.db.query( 'SELECT type FROM users WHERE username = %s', ( session[ 'username' ], ) )
        user = q.fetchone()
        session[ 'account_type' ] = user[ 'type' ]
        
        return jsonify( {
            'error': False,
            'logged': True,
        } )
    
    else:
        return jsonify( {
            'error': False,
            'next_step': session[ 'need_to_check' ][ 0 ]
        } )

################################################################################
#    Reset

@app.route( baseurl + '/reset_password' )
def password_reset():
    session.clear()
    session[ 'process' ] = "request_password_reset"
    
    return render_template( 
        "users/password_reset.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        envtype = envtype
    )

@app.route( baseurl + '/do_reset_password', methods = [ 'POST' ] )
def do_password_reset():
    email = request.form.get( "email", None )
    
    Thread( target = do_password_reset_thread, args = ( email, ) ).start()
    
    return jsonify( {
        'error': False,
        'message': 'OK'
    } )

def do_password_reset_thread( email ):
    q = config.db.query( 'SELECT id, username, email FROM users' )
    users = q.fetchall()
    
    for user in users:
        if not user[ 'email' ].startswith( "pbkdf2$" ):
            continue
        
        elif pbkdf2( email, user[ 'email' ] ).verify():
            id = hashlib.sha512( random_data( 100 ) ).hexdigest()
            
            ####################################################################
            
            data = {
                'process': 'password_reset',
                'process_id': id,
                'user_id': user[ 'id' ]
            }
            data = json.dumps( data )
            data = base64.b64encode( data )
            
            config.redis_shared.set( "reset_" + id, data, ex = 24 * 3600 )
            
            ####################################################################
            
            email_content = render_jinja_html( 
                "templates/email", "reset.html",
                id = id,
                url = config.domain + baseurl + "/reset_password_stage2",
                username = user[ "username" ]
            )
            
            msg = MIMEText( email_content, "html" )
            
            msg[ 'Subject' ] = 'ICNML - User password reset'
            msg[ 'From' ] = config.sender
            msg[ 'To' ] = email
            
            s = smtplib.SMTP( config.smtpserver )
            s.sendmail( config.sender, [ email ], msg.as_string() )
            s.quit()

@app.route( baseurl + '/reset_password_stage2/<id>', methods = [ 'GET', 'POST' ] )
def password_reset_stage2( id ):
    id = str( id )
    data = config.redis_shared.get( "reset_" + id )
    
    if data != None:
        data = base64.b64decode( data )
        data = json.loads( data )
        
        password = request.form.get( "password", None )
        
        userid = data.get( "user_id", None )
        
        if password != None:
            password = pbkdf2( password, random_data( 50 ), 50000 ).hash()
            config.db.query( "UPDATE users SET password = %s WHERE id = %s", ( password, userid ) )
            config.db.commit()
            
            config.redis_shared.delete( "reset_" + id )
            
            return jsonify( {
                'error': False,
                'password_updated': True
            } )
            
        else:
            return render_template( 
                "users/password_reset_stage2.html",
                baseurl = baseurl,
                id = id,
                js = config.cdnjs,
                css = config.cdncss,
                envtype = envtype
            ) 
        
    else:
        return jsonify( {
            'error': True,
            'message': 'Reset procedure not found/expired'
        } )

################################################################################
#    U2F keys

@app.route( baseurl + '/u2f/admin' )
@login_required
def u2f_admin():
    return render_template( 
        "u2f/admin.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        admin = int( session[ 'account_type' ] ) == 1,
        keys = do_u2f_get_list_of_keys( all = True ),
        envtype = envtype
    )

def do_u2f_get_list_of_keys( uid = None, all = False ):
    user_id = session.get( "user_id", uid )
    
    sql = "SELECT id, key_name as name, created_on, last_usage, usage_counter, active FROM webauthn WHERE user_id = %s"
    if not all:
        sql += " AND active = true"
    sql += " ORDER BY usage_counter DESC"
    
    q = config.db.query( sql, ( user_id, ) )
    keys = q.fetchall()
    
    data = []
    for key in keys:
        data.append( dict( key ) )
    
    return data

@app.route( baseurl + '/u2f/begin_activate', methods = [ 'POST' ] )
@login_required
def u2f_begin_activate():
    session[ 'key_name' ] = request.form.get( "key_name", None )
    
    username = session.get( "username" )
    
    challenge = pyotp.random_base32( 64 )
    ukey = pyotp.random_base32( 64 )
    
    session[ 'challenge' ] = challenge
    session[ 'register_ukey' ] = ukey

    make_credential_options = webauthn.WebAuthnMakeCredentialOptions( 
        challenge, config.rp_name, config.RP_ID,
        ukey, username, username,
        None
    )
    
    registration_dict = make_credential_options.registration_dict
    registration_dict[ "authenticatorSelection" ] = {
      "authenticatorAttachment": "cross-platform",
      "requireResidentKey": False,
      "userVerification": "discouraged"
    }
    
    return jsonify( registration_dict )

@app.route( baseurl + '/u2f/verify', methods = [ 'POST' ] )
@login_required
def u2f_verify():
    challenge = session[ 'challenge' ]
    user_id = session[ 'user_id' ]
    key_name = session.get( "key_name", None )
    ukey = session[ 'register_ukey' ]
    
    response = request.form
    
    webauthn_registration_response = webauthn.WebAuthnRegistrationResponse( 
        config.RP_ID,
        config.ORIGIN,
        response,
        challenge
    )
    
    try:
        webauthn_credential = webauthn_registration_response.verify()
    
    except Exception as e:
        return jsonify( {
            'error': True,
            'message': 'Registration failed. Error: {}'.format( e )
        } )
    
    config.db.query( 
        """
            INSERT INTO webauthn
            ( user_id, key_name, ukey, credential_id, pub_key, sign_count )
            VALUES ( %s, %s, %s, %s, %s, %s )
        """,
        ( 
            user_id, key_name,
            ukey, webauthn_credential.credential_id,
            webauthn_credential.public_key, webauthn_credential.sign_count,
        )
    )
    config.db.commit()
    
    return jsonify( {
        'success': 'User successfully registered.'
    } )

################################################################################

@app.route( baseurl + '/u2f/delete', methods = [ 'POST' ] )
@login_required
def u2f_delete_key():
    key_id = request.form.get( "key_id", False )
    key_name = request.form.get( "key_name", False )
    userid = session[ 'user_id' ]
    
    try:
        config.db.query( "DELETE FROM webauthn WHERE id = %s AND key_name = %s AND user_id = %s", ( key_id, key_name, userid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except Exception as e:
        return jsonify( {
            'error': True
        } );

@app.route( baseurl + '/u2f/disable', methods = [ 'POST' ] )
@login_required
def u2f_disable_key():
    key_id = request.form.get( "key_id", False )
    key_name = request.form.get( "key_name", False )
    userid = session[ 'user_id' ]
    
    try:
        config.db.query( "UPDATE webauthn SET active = False WHERE id = %s AND key_name = %s AND user_id = %s", ( key_id, key_name, userid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except Exception as e:
        return jsonify( {
            'error': True
        } );

@app.route( baseurl + '/u2f/enable', methods = [ 'POST' ] )
@login_required
def u2f_enable_key():
    key_id = request.form.get( "key_id", False )
    key_name = request.form.get( "key_name", False )
    userid = session[ 'user_id' ]
    
    try:
        config.db.query( "UPDATE webauthn SET active = True WHERE id = %s AND key_name = %s AND user_id = %s", ( key_id, key_name, userid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except Exception as e:
        return jsonify( {
            'error': True
        } );

@app.route( baseurl + '/u2f/rename', methods = [ 'POST' ] )
@login_required
def u2f_rename_key():
    key_id = request.form.get( "key_id", False )
    key_name = request.form.get( "key_name", False )
    userid = session[ 'user_id' ]
    
    try:
        config.db.query( "UPDATE webauthn SET key_name = %s WHERE id = %s AND user_id = %s", ( key_name, key_id, userid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except Exception as e:
        return jsonify( {
            'error': True
        } );

@app.route( baseurl + '/u2f/begin_assertion' )
def u2f_begin_assertion():
    user_id = session.get( "user_id" )
    
    if 'challenge' in session:
        del session[ 'challenge' ]
    
    challenge = pyotp.random_base32( 64 )
    session[ 'challenge' ] = challenge
    
    q = config.db.query( "SELECT * FROM webauthn WHERE user_id = %s AND active = true", ( user_id, ) )
    key_list = q.fetchall()
    
    credential_id_list = []
    for key in key_list:
        credential_id_list.append( {
            'type': 'public-key',
            'id': key[ 'credential_id' ],
            'transports': [ 'usb', 'nfc', 'ble', 'internal' ]
        } )
     
    assertion_dict = {
        'challenge': challenge,
        'timeout': 60000,
        'allowCredentials': credential_id_list,
        'rpId': config.RP_ID,
    }
    
    return jsonify( {
        'error': False,
        'data': assertion_dict
    } )

@app.route( baseurl + '/u2f/verify_assertion', methods = [ 'POST' ] )
def verify_assertion():
    challenge = session.get( 'challenge' )
    assertion_response = request.form
    credential_id = assertion_response.get( 'id' )
    
    q = config.db.query( "SELECT * FROM webauthn WHERE credential_id = %s", ( credential_id, ) )
    user = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        None, session[ 'username' ], None, None,
        user[ 'credential_id' ], user[ 'pub_key' ], user[ 'sign_count' ], config.RP_ID
    )

    webauthn_assertion_response = webauthn.WebAuthnAssertionResponse( 
        webauthn_user,
        assertion_response,
        challenge,
        config.ORIGIN,
        uv_required = False
    )
    
    try:
        sign_count = webauthn_assertion_response.verify()
        
    except Exception as e:
        return jsonify( {
            'error': True,
            'message': 'Assertion failed. Error: {}'.format( e )
        } )
    
    else:
        dt = datetime.now( pytz.timezone( 'Europe/Zurich' ) )
        q = config.db.query( "UPDATE webauthn SET sign_count = %s, last_usage = %s, usage_counter = usage_counter + 1 WHERE credential_id = %s", ( sign_count, dt, credential_id, ) )
        config.db.commit()
        
        session[ 'need_to_check' ].remove( "securitykey" )
        do_login()
        
        return jsonify( {
            'error': False
        } )

################################################################################
#    New user

@app.route( baseurl + '/signin' )
def new_user():
    q = config.db.query( "SELECT id, name FROM account_type WHERE can_singin = true" )
    r = q.fetchall()
    
    account_type = []
    for rr in r:
        account_type.append( dict( rr ) )
    
    return render_template( 
        "users/signin.html",
        baseurl = baseurl,
        list_account_type = account_type,
        js = config.cdnjs,
        css = config.cdncss,
        envtype = envtype
    )

@app.route( baseurl + '/do_signin', methods = [ 'POST' ] )
def add_account_request_to_db():
    try:
        first_name = request.form[ 'first_name' ]
        last_name = request.form[ 'last_name' ]
        email = request.form[ 'email' ]
        account_type = request.form[ 'account_type' ]
        uuid = str( uuid4() )
        
        sql = "SELECT name FROM account_type WHERE id = %s"
        account_type_name = config.db.query_fetchone( sql, ( account_type, ) )[ 'name' ]
        account_type_name = account_type_name.lower()
        username_id = config.db.query_fetchone( "SELECT nextval( 'username_" + account_type_name + "_seq' ) as id" )[ 'id' ]
        
        config.db.query( 
            """
                INSERT INTO signin_requests
                ( first_name, last_name, email, account_type, uuid, username_id )
                VALUES ( %s, %s, %s, %s, %s, %s )
            """,
            ( first_name, last_name, email, account_type, uuid, username_id, )
        )
        config.db.commit()
        
        return jsonify( {
            'error': False,
            'uuid': uuid
        } )
        
    except:
        return jsonify( {
            'error': True
        } )

@app.route( baseurl + '/validate_signin' )
@admin_required
def validate_signin():
    q = config.db.query( """
        SELECT signin_requests.*, account_type.name as account_type 
        FROM signin_requests
        LEFT JOIN account_type ON signin_requests.account_type = account_type.id
        WHERE signin_requests.status = 'pending'
    """ )
    
    r = q.fetchall()
    users = []
    for rr in r:
        users.append( dict( rr ) )
    
    return render_template( 
        "users/validate_signin.html",
        baseurl = baseurl,
        users = users,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype
    )

@app.route( baseurl + '/do_validate_signin', methods = [ 'POST' ] )
@admin_required
def do_validate_signin():
    request_id = request.form.get( "id" )
    
    q = config.db.query( 'SELECT * FROM signin_requests WHERE id = %s', ( request_id, ) )
    s = q.fetchone()
    s = dict( s )
    
    r = {}
    
    r[ 'user' ] = s
    r[ 'user' ][ 'request_time' ] = str( r[ 'user' ][ 'request_time' ] ) 
    r[ 'user' ][ 'validation_time' ] = str( r[ 'user' ][ 'validation_time' ] ) 
    
    r[ 'acceptance' ] = {}
    r[ 'acceptance' ][ 'username' ] = session[ 'username' ]
    r[ 'acceptance' ][ 'time' ] = str( datetime.now() )
    
    j = json.dumps( r )
    challenge = base64.b64encode( j )
    challenge = challenge.replace( "=", "" )
    session[ 'validation_user_challenge' ] = challenge
    
    ############################################################################
    
    user_id = session[ 'user_id' ]
    
    q = config.db.query( "SELECT * FROM webauthn WHERE user_id = %s AND usage_counter > 0 ORDER BY last_usage DESC LIMIT 1", ( user_id, ) )
    key = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        key[ 'ukey' ], session[ 'username' ], session[ 'username' ], None,
        key[ 'credential_id' ], key[ 'pub_key' ], key[ 'sign_count' ], config.RP_ID
    )
    webauthn_assertion_options = webauthn.WebAuthnAssertionOptions( webauthn_user, challenge )
    
    return jsonify( {
        'error': False,
        'data': webauthn_assertion_options.assertion_dict
    } )

@app.route( baseurl + '/do_validate_signin_2', methods = [ 'POST' ] )
@admin_required
def do_validate_signin_2():
    challenge = session.get( 'validation_user_challenge' )
    assertion_response = request.form
    assertion_response_s = base64.b64encode( json.dumps( assertion_response ) )
    credential_id = assertion_response.get( 'id' )
    
    q = config.db.query( "SELECT * FROM webauthn WHERE credential_id = %s", ( credential_id, ) )
    user = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        user[ 'ukey' ], session[ 'username' ], session[ 'username' ], None,
        user[ 'credential_id' ], user[ 'pub_key' ], user[ 'sign_count' ], "icnml.unil.ch"
    )

    webauthn_assertion_response = webauthn.WebAuthnAssertionResponse( 
        webauthn_user,
        assertion_response,
        challenge,
        config.ORIGIN,
        uv_required = False
    )
    
    try:
        sign_count = webauthn_assertion_response.verify()
        
    except Exception as e:
        return jsonify( {
            'error': True,
            'message': 'Assertion failed. Error: {}'.format( e )
        } )
    
    ############################################################################
    
    if len( challenge ) % 4 != 0:
        challenge += "=" * ( 4 - ( len( challenge ) % 4 ) )
    
    newuser = base64.b64decode( challenge )
    newuser = json.loads( newuser )
    user_type = newuser[ 'user' ][ 'account_type' ]
    email = newuser[ 'user' ][ 'email' ]
    email_hash = pbkdf2( email, random_data( 100 ), 50000 ).hash()
    request_uuid = newuser[ 'user' ][ 'uuid' ]
    request_id = newuser[ 'user' ][ 'id' ]
    username_id = newuser[ 'user' ][ 'username_id' ]
    
    n = config.db.query( "SELECT name FROM account_type WHERE id = %s", ( user_type, ) )
    n = n.fetchone()
    n = n[ 0 ]
    
    username = n + "_" + str( username_id )
    username = username.lower()
    
    try:
        config.db.query( "UPDATE signin_requests SET validation_time = now(), assertion_response = %s, status = 'validated' WHERE id = %s", ( assertion_response_s, request_id ) )
        config.db.query( "INSERT INTO users ( username, email, type ) VALUES ( %s, %s, %s )", ( username, email_hash, user_type ) )
        config.db.commit()
    
    except:
        return jsonify( {
            'error': True,
            'message': 'Can not insert into database.'
        } )
    
    ############################################################################
    
    email_content = render_jinja_html( 
        "templates/email", "signin.html",
        username = username,
        url = "https://icnml.unil.ch" + url_for( "config_new_user", uuid = newuser[ 'user' ][ 'uuid' ] )
    )
    
    msg = MIMEText( email_content, "html" )
    
    msg[ 'Subject' ] = 'ICNML - Login information'
    msg[ 'From' ] = config.sender
    msg[ 'To' ] = email
    
    s = smtplib.SMTP( config.smtpserver )
    s.sendmail( config.sender, [ email ], msg.as_string() )
    s.quit()
    
    ############################################################################
    
    return jsonify( {
        'error': False
    } )

@app.route( baseurl + '/do_validation_reject', methods = [ "POST" ] )
def do_validation_reject():
    request_id = request.form.get( "id" )
    
    try:
        sql = "UPDATE signin_requests SET status = 'rejected' WHERE id = %s"
        r = config.db.query( sql, ( request_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except Exception as e:
        print e
        
        return jsonify( {
            "error": True
        } )

@app.route( baseurl + '/config/<uuid>' )
def config_new_user( uuid ):
    session.clear()
    
    q = config.db.query( "SELECT email FROM signin_requests WHERE uuid = %s", ( uuid, ) )
    r = q.fetchone()
    try:
        email = r[ 'email' ]
        session[ 'signin_user_validation_email' ] = email
        session[ 'signin_user_validation_uuid' ] = uuid
        
        return render_template( 
            "users/config.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            envtype = envtype,
            next_step = "do_config_new_user"
        )
    
    except:
        return redirect( url_for( 'home' ) )

@app.route( baseurl + '/do_config', methods = [ 'POST' ] )
def do_config_new_user():
    email = session[ 'signin_user_validation_email' ]
    uuid = session[ 'signin_user_validation_uuid' ]
    username = request.form.get( "username" )
    password = request.form.get( "password" )
    
    session[ 'username' ] = username
    
    ############################################################################
    
    q = config.db.query( "SELECT count(*) FROM signin_requests WHERE uuid = %s AND email = %s", ( uuid, email, ) )
    r = q.fetchone()
    r = r[ 0 ]
    
    if r == 0:
        return jsonify( {
            'error': True,
            'message': "no signin request"
        } )
    
    q = config.db.query( "SELECT * FROM users WHERE username = %s", ( username, ) )
    user = q.fetchone()
    
    if user == None:
        return jsonify( {
            'error': True,
            'message': "no user"
        } )