Fix SAML login: account upgrading is done via the email attribute, username is eduPersonPrincipalName.

2 files changed, 56 insertions, 51 deletions

diff --git a/scripts/db_update-20151106.sql b/scripts/db_update-20151106.sql
new file mode 100644
index 0000000..6a06dbe
--- /dev/null
+++ b/scripts/db_update-20151106.sql
@@ -0,0 +1,3 @@
+alter table codeq_user drop constraint codeq_user_uq1;
+create unique index codeq_user_username_saml_idx on codeq_user (username) where password is null;
+create unique index codeq_user_username_common_idx on codeq_user (username) where password is not null;
diff --git a/server/user_session.py b/server/user_session.py
index 8a2e88e..79dc93d 100644
--- a/server/user_session.py
+++ b/server/user_session.py
@@ -118,11 +118,25 @@ class UserSession(object):
                 db.return_connection(conn)
 
     def saml_login_or_signup(self, saml_data, gui_lang, can_upgrade_account, upgrade_password):
+        """Logs in the SAML-authenticated user using the provided SAML login data.
+        Algorithm outline:
+        - eduPersonPrincipalName is a required SAML attribute
+        - first UPDATE is tried by username=eduPersonPrincipalName and password is NULL
+        - if the UPDATE returns 0 rows then the account does not exist yet and needs to be created
+          - if email was not provided, create a new SAML account with no email
+          - else a SELECT FOR UPDATE is made by email=email
+          - if no rows are returned then the new SAML account is created
+          - else if merge data is not available throw an AccountMergeRequired
+          - else if merge is denied create a new SAML account
+          - else
+            - if password authentication succeeds upgrade the common account to a SAML account, setting password=NULL
+            - else throw an AuthenticationFailed
+        """
         #uuid = saml_data.get('schacUUID')
         #if uuid is None:
         #    raise AuthenticationFailed('SAML data does not contain schacUUID')
         name = saml_data.get('displayName')
-        #email = saml_data.get('mail')
+        email = saml_data.get('mail')
         eduPersonPrincipalName = saml_data.get('eduPersonPrincipalName')
         if eduPersonPrincipalName is None:
             raise AuthenticationFailed('SAML data does not contain eduPersonPrincipalName')
@@ -132,58 +146,46 @@ class UserSession(object):
             try:
                 cur = conn.cursor()
                 try:
-                    cur.execute('select id, gui_lang, date_joined, robot_address, gui_layout, username, password from codeq_user where email = %s for update', (eduPersonPrincipalName,))
-                    data = cur.fetchone()  # go through all the records and prefer the one without a password, that one will be the SAML account
-                    if data:
-                        stored_password = data[6]
-                        row = cur.fetchone()
-                        while row:
-                            if row[6] is None:
-                                data = row
-                            row = cur.fetchone()
-                    else:
-                        stored_password = None
-                    if data and ((not stored_password) or (can_upgrade_account is None) or can_upgrade_account):
-                        # the account already exists
-                        if stored_password:
-                            # the account has a password: an upgrade must be made
-                            if can_upgrade_account is None:
-                                raise AccountMergeRequired(data[5])
-                            # can_upgrade_account can only be True here
-                            if not verify_password(upgrade_password, stored_password):
-                                raise AuthenticationFailed('Password mismatch')
-                        cur.execute('update codeq_user set password = null, name = %s, email = %s, saml_data = %s, last_login = %s where id = %s', (name, eduPersonPrincipalName, psycopg2.extras.Json(saml_data), str(now), data[0]))
-                        self.uid = data[0]
-                        self.username = data[5]
-                        self.settings = {'gui_lang': gui_lang, 'robot_address': data[3], 'gui_layout': data[4]}
-                        date_joined = data[2]
-                    else:
-                        # a new account required
-                        cur.execute('insert into codeq_user (username, name, email, is_admin, is_active, date_joined, last_login, gui_lang, saml_data) values (%s, %s, %s, %s, %s, %s, %s, %s, %s) returning id', (eduPersonPrincipalName, name, eduPersonPrincipalName, False, True, str(now), str(now), gui_lang, psycopg2.extras.Json(saml_data)))
-                        row = cur.fetchone()
-                        if row is None:
-                            raise SignupFailed('Sign-up failed')
+                    cur.execute('update codeq_user set name = coalesce(%s, name), email = coalesce(%s, email), saml_data = %s, last_login = %s where username = %s and password is null returning id, gui_lang, date_joined, robot_address, gui_layout', (name, email, psycopg2.extras.Json(saml_data), str(now), eduPersonPrincipalName))
+                    row = cur.fetchone()
+                    if row:
                         self.uid = row[0]
                         self.username = eduPersonPrincipalName
-                        self.settings = {'gui_lang': gui_lang, 'robot_address': None, 'gui_layout': None}
-                        date_joined = now
-
-                    # cur.execute('update codeq_user set name = %s, email = %s, saml_data = %s, last_login = %s where username = %s and saml_data is not null returning id, gui_lang, date_joined, robot_address, gui_layout', (name, email, psycopg2.extras.Json(saml_data), str(now), uuid))
-                    # row = cur.fetchone()
-                    # if row:
-                    #     self.uid = row[0]
-                    #     self.username = uuid
-                    #     self.settings = {'gui_lang': row[1], 'robot_address': row[3], 'gui_layout': row[4]}
-                    #     return name, email, row[2], now
-                    # else:
-                    #     cur.execute('insert into codeq_user (username, name, email, is_admin, is_active, date_joined, last_login, gui_lang, saml_data) values (%s, %s, %s, %s, %s, %s, %s, %s, %s) returning id', (uuid, name, email, False, True, str(now), str(now), gui_lang, psycopg2.extras.Json(saml_data)))
-                    #     row = cur.fetchone()
-                    #     if row is None:
-                    #         raise SignupFailed('Sign-up failed')
-                    #     self.uid = row[0]
-                    #     self.username = uuid
-                    #     self.settings = {'gui_lang': gui_lang, 'robot_address': None, 'gui_layout': None}
-                    #     return name, email, now, now
+                        self.settings = {'gui_lang': row[1], 'robot_address': row[3], 'gui_layout': row[4]}
+                        date_joined = row[2]
+                    else:
+                        # the account does not exist yet, either create one or upgrade an existing one
+                        while True:  # a trick loop, so we are able to exit the code block where ever we want
+                            if email:
+                                cur.execute('select id, gui_lang, date_joined, robot_address, gui_layout, username, password from codeq_user where email = %s and password is not null for update', (email,))
+                                row = cur.fetchone()
+                                if row:
+                                    # there is a password-protected account: an upgrade must be made
+                                    if can_upgrade_account is None:
+                                        raise AccountMergeRequired(row[5])
+                                    if can_upgrade_account:
+                                        if not verify_password(upgrade_password, row[6]):
+                                            raise AuthenticationFailed('Password mismatch')
+                                        uid = row[0]
+                                        cur.execute('update codeq_user set password = null, username = %s, name = coalesce(%s, name), saml_data = %s, last_login = %s where id = %s returning gui_lang, date_joined, robot_address, gui_layout', (eduPersonPrincipalName, name, psycopg2.extras.Json(saml_data), str(now), uid))
+                                        row = cur.fetchone()
+                                        if row is None:
+                                            raise SignupFailed('Account upgrade failed')
+                                        self.uid = uid
+                                        self.username = eduPersonPrincipalName
+                                        self.settings = {'gui_lang': row[0], 'robot_address': row[2], 'gui_layout': row[3]}
+                                        date_joined = row[1]
+                                        break
+                            # insert a new SAML account
+                            cur.execute('insert into codeq_user (username, name, email, is_admin, is_active, date_joined, last_login, gui_lang, saml_data) values (%s, %s, %s, %s, %s, %s, %s, %s, %s) returning id', (eduPersonPrincipalName, name, email, False, True, str(now), str(now), gui_lang, psycopg2.extras.Json(saml_data)))
+                            row = cur.fetchone()
+                            if row is None:
+                                raise SignupFailed('Sign-up failed')
+                            self.uid = row[0]
+                            self.username = eduPersonPrincipalName
+                            self.settings = {'gui_lang': gui_lang, 'robot_address': None, 'gui_layout': None}
+                            date_joined = now
+                            break
                 finally:
                     cur.close()
                 conn.commit()