search

wso2 / product-is

Welcome to the WSO2 Identity Server source code! For info on working with the WSO2 Identity Server repository and contributing code, click the link below.
http://wso2.github.io/
Apache License 2.0
746 stars 724 forks source link

Getting 500 Internal Server Error when Navigating to "Login Flow" of Created Application #20872

Closed NilukaSripalim closed 2 months ago

NilukaSripalim commented 2 months ago

Describe the issue: We are encountering a 500 Internal Server Error 

https://localhost:9443/api/server/v1/identity-providers?filter=isEnabled+eq+%22true%22&limit=15&offset=0&requiredAttributes=federatedAuthenticators,provisioning

with the message "Error while connecting to the database. Error occurred while retrieving Identity Provider for tenant: carbon.super" when navigating to the "Login Flow" section of a created application.

This issue is specifically occurring when the primary database is configured with PostgreSQL. The root cause is an error in the SQL query:

org.postgresql.util.PSQLException: ERROR: column "VALUE" does not exist

Additionally, the UI icons are not displaying as expected in the "Login Flow > Visual Editor" section, particularly the "Add Multi-factor Login" icons.

[2024-08-09 18:03:30,156] [72f95d86-89b5-4352-b328-6db551114e5c] ERROR {org.wso2.carbon.identity.api.server.idp.v1.core.ServerIdpManagementService} - errorCode: IDP-65006 | message: Error while connecting database. Error occurred while retrieving Identity Provider for tenant: carbon.super org.wso2.carbon.idp.mgt.IdentityProviderManagementServerException: Error while connecting database. Error occurred while retrieving Identity Provider for tenant: carbon.super
    at org.wso2.carbon.idp.mgt.util.IdPManagementUtil.handleServerException(IdPManagementUtil.java:265)
    at org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.getIdPsSearch(IdPManagementDAO.java:363)
    at org.wso2.carbon.idp.mgt.dao.CacheBackedIdPMgtDAO.getPaginatedIdPsSearch(CacheBackedIdPMgtDAO.java:147)
    at org.wso2.carbon.idp.mgt.IdentityProviderManager.getIdPs(IdentityProviderManager.java:493)
    at org.wso2.carbon.identity.api.server.idp.v1.core.ServerIdpManagementService.getIDPs(ServerIdpManagementService.java:203)
    at org.wso2.carbon.identity.api.server.idp.v1.impl.IdentityProvidersApiServiceImpl.getIDPs(IdentityProvidersApiServiceImpl.java:175)
    at org.wso2.carbon.identity.api.server.idp.v1.IdentityProvidersApi.getIDPs(IdentityProvidersApi.java:434)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201)
    at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:307)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:265)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:304)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doGet(AbstractHTTPServlet.java:222)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:529)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:279)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
    at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:119)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
    at org.wso2.carbon.identity.context.rewrite.valve.OrganizationContextRewriteValve.invoke(OrganizationContextRewriteValve.java:123)
    at org.wso2.carbon.tomcat.ext.valves.SameSiteCookieValve.invoke(SameSiteCookieValve.java:38)
    at org.wso2.carbon.identity.cors.valve.CORSValve.invoke(CORSValve.java:83)
    at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:149)
    at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:153)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:110)
    at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49)
    at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:71)
    at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:152)
    at org.wso2.carbon.extension.identity.x509Certificate.valve.X509CertificateAuthenticationValve.invoke(X509CertificateAuthenticationValve.java:59)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
    at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:63)
    at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49)
    at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:137)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1794)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.postgresql.util.PSQLException: ERROR: column "VALUE" does not exist
  Position: 357
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:507)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at jdk.internal.reflect.GeneratedMethodAccessor58.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
    at com.sun.proxy.$Proxy54.executeQuery(Unknown Source)
    at org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.getIdpQueryResultSet(IdPManagementDAO.java:572)
    at org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.getIdPsSearch(IdPManagementDAO.java:357)
    ... 62 more

deploymnt.toml 

[server]
hostname = "localhost"
node_ip = "127.0.0.1"
base_path = "https://$ref{server.hostname}:${carbon.management.port}"

[super_admin]
username = "admin"
password = "admin"
create_admin_account = true

[user_store]
type = "database_unique_id"

[keystore.primary]
file_name = "wso2carbon.jks"
password = "wso2carbon"
type="JKS"

[truststore]
file_name="client-truststore.jks"
password="wso2carbon"
type="JKS"

[account_recovery.endpoint.auth]
hash= "66cd9688a2ae068244ea01e70f0e230f5623b7fa4cdecb65070a09ec06452262"

[identity.auth_framework.endpoint]
app_password= "dashboard"

[database.identity_db]
type = "postgre"
hostname = "localhost"
name = "psqlmar01"
username = "postgres"
password = "<DBPASSWORD>"
port = "5432"

[database.shared_db]
type = "postgre"
hostname = "localhost"
name = "psqlmar01"
username = "postgres"
password = "<DBPASSWORD>"
port = "5432"

https://github.com/user-attachments/assets/5234617b-af2b-4f00-9c6d-da763ce1b12d

How to reproduce:

  1. Configure the primary database with PostgreSQL.
  2. Create an application.
  3. Navigate to the "Login Flow" section of the created application.
  4. Observe the 500 Internal Server Error and the error message related to the database.
  5. In the "Login Flow > Visual Editor," check the "Add Multi-factor Login" section and note the missing UI icons.

Expected behavior:

  1. The "Login Flow" page should load without errors.
  2. The UI icons, especially the "Add Multi-factor Login" icons, should display correctly.

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

https://wso2.org/jenkins/job/products/job/product-is/

Lakshan-Banneheke commented 2 months ago

Below info is just a preliminary analysis and this needs to be further investigated. Providing the below info as it might be a helpful starting point when analysing this issue.

Issue seems to be coming from this query https://github.com/wso2/carbon-identity-framework/blob/master/components/idp-mgt/org.wso2.carbon.idp.mgt/src/main/java/org/wso2/carbon/idp/mgt/util/IdPManagementConstants.java#L195

This is likely not a schema issue since VALUE coloumn is present in the postgres schema https://github.com/wso2/carbon-identity-framework/blob/27d1c98096285c8c218c6ca98cd13ea982535276/features/identity-core/org.wso2.carbon.identity.core.server.feature/resources/dbscripts/postgresql.sql#L747

Issue could be due to the postgres query using \"VALUE\"

MSSQL query is just VALUE with no quotation marks https://github.com/wso2/carbon-identity-framework/blob/master/components/idp-mgt/org.wso2.carbon.idp.mgt/src/main/java/org/wso2/carbon/idp/mgt/util/IdPManagementConstants.java#L171

asha15 commented 2 months ago

As mentioned by @Lakshan-Banneheke, The issue was reported due to the incorrect reference to the value column in the PostgreSQL query. The column was mistakenly referenced as "VALUE" with double quotes, which caused a case sensitivity error.

The issue has been fixed with: https://github.com/wso2/carbon-identity-framework/pull/5852