SSO doesn't work as expected

[nipunaupeksha]

Describe the issue: When a user tries to log in to the application using the SSO, it produces an error saying that a constraint has not been added for the IDN_AUTH_SESSION_APP_INFO and hence the following query cannot be executed, INSERT INTO IDN_AUTH_SESSION_APP_INFO(SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID,IDP_NAME)VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID,IDP_NAME) DO UPDATE SET SESSION_ID = EXCLUDED.SESSION_ID, SUBJECT = EXCLUDED.SUBJECT, APP_ID = EXCLUDED.APP_ID, INBOUND_AUTH_TYPE = EXCLUDED.INBOUND_AUTH_TYPE, USER_ID = EXCLUDED.USER_ID, IDP_NAME = EXCLUDED.IDP_NAME; due to the following error, Caused by: org.postgresql.util.PSQLException: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ... After adding a constraint for the SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE, USER_ID and IDP_NAME, by using the following query, ALTER TABLE IDN_AUTH_SESSION_APP_INFO ADD CONSTRAINT constraint_name UNIQUE (SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID,IDP_NAME); the above error is getting resolved.

But, if an IdP has been set up(e.g. google) and user is logging the first time there is no issue, but if the same user is again trying to log out the following issue is generated. Caused by: org.wso2.carbon.database.utils.jdbc.exceptions.DataAccessException: Error in performing Database query: '%s'INSERT INTO IDN_AUTH_SESSION_APP_INFO(SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID,IDP_NAME)VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID,IDP_NAME) DO UPDATE SET SESSION_ID = EXCLUDED.SESSION_ID, SUBJECT = EXCLUDED.SUBJECT, APP_ID = EXCLUDED.APP_ID, INBOUND_AUTH_TYPE = EXCLUDED.INBOUND_AUTH_TYPE, USER_ID = EXCLUDED.USER_ID, IDP_NAME = EXCLUDED.IDP_NAME; ... Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "idn_auth_session_app_info_pkey"... This is because if theIDP_NAME or USER_ID value is changed between two login attempts it is trying to insert the new values to the database by checking whether there is a conflict between SESSION_ID,SUBJECT,APP_ID,INBOUND_AUTH_TYPE,USER_ID and IDP_NAME columns. And since there is no conflict with them if IDP_NAME or USER_ID values have been changed, the query is trying to insert the record to the database. But since the primary key has been defined only using SESSION_ID, SUBJECT, APP_ID and INBOUND_AUTH_TYPE, the duplicate key value violation is raised.

How to reproduce:

1. Add an Idp using Identity Providers → Add
2. Provide a name for the Idp and go to Federated Authenticators → Google Configuration
3. After providing client-id and client-secret register the Idp.
4. Add an SP using Service Providers → Add (e.g. travelocity.com)
5. Provide a name for the SP and go to Local & Outbound Authentication Configuration and select the Federated Authentication (The Idp we created) as the Authentication Type.
6. Then go to Inbound Authentication Configuration → SAML2 Web SSO Configuration → Configure
7. Give as issuer name (travelocity.com) and assertion consumer URL.
8. Enable IdP Initiated SSO and add the SP configurations.
9. Type https://localhost:9443/samlsso?spEntityID= in browser and log in to the SP via google authentication. (In this case the IdP name will be google)
10. After logging in open a new tab and again type https://localhost:9443/samlsso?spEntityID= to prompt the login page.
11. During this login you will be getting the above mentioned duplicate key error.

Expected behavior: There should be no errors.

Environment information (Please complete the following information; remove any unnecessary fields) :

• Product Version: IS 5.10.0
• OS: Mac
• Database: H2, Postgres
• Userstore: LDAP

---

[GANGANI]

This issue is not reproducible in IS-6.0.0 or in the public as the fix[1] is not available there.

[1] https://github.com/wso2-support/carbon-identity-framework/pull/1815