OidcLocalUserService throw an Exception when executing createLocalUser in OIDC profile

[xyohn]

• [x] I have checked the discussions
• [x] I have searched the issues of this repository and believe that this is not a duplicate.
• [x] I have checked the FAQ of this repository and believe that this is not a duplicate.

Describe the bug A clear and concise description of what the bug is. Recently,i try to debug apollo portal in order to understand how to implement authentication.However,I find a exception when com.ctrip.framework.apollo.portal.spi.oidc.OidcLocalUserServiceImpl#createLocalUser executing.

In OIDC profile,com.ctrip.framework.apollo.portal.spi.oidc.OidcAuthenticationSuccessEventListener will listen the event called AuthenticationSuccessEvent which is from Spring Security.if person who is first time to login, it will execute com.ctrip.framework.apollo.portal.spi.oidc.OidcLocalUserServiceImpl#createLocalUser to create user in database.Then the method will call JdbcUserDetailsManager#createUser,which is configured in com.ctrip.framework.apollo.portal.spi.configuration.AuthConfiguration.SpringSecurityAuthAutoConfiguration#jdbcUserDetailsManager,but the parameter does not contain email,which is set to non-null in com.ctrip.framework.apollo.portal.entity.po.UserPO.So it cause a JdbcSQLIntegrityConstraintViolationException. Although I use the h2 database,I guess it's the same situation under MySQL because of the configuration in com.ctrip.framework.apollo.portal.spi.configuration.AuthConfiguration.SpringSecurityAuthAutoConfiguration#jdbcUserDetailsManager

I would like to ask, is this a bug? To Reproduce Steps to reproduce the behavior:

1. use h2 database(but I think it is same uder MySQL)
2. use OIDC properties,and configure it correctly
3. try to login through OIDC, and observe the com.ctrip.framework.apollo.portal.spi.oidc.OidcLocalUserServiceImpl#createLocalUser
4. that is all

Expected behavior A clear and concise description of what you expected to happen.

I think it should not throw an exception and save the data correctly. Screenshots If applicable, add screenshots to help explain your problem.

Additional Details & Logs

• Version 2.2.0
• Error logs

  
  2024-04-17 19:19:43.997 ERROR 69260 --- [nio-8070-exec-3] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO "Users" ("Username", "Password", "Enabled") values (?,?,?)]; NULL not allowed for column "Email"; SQL statement: INSERT INTO "Users" ("Username", "Password", "Enabled") values (?,?,?) [23502-214]; nested exception is org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "Email"; SQL statement: INSERT INTO "Users" ("Username", "Password", "Enabled") values (?,?,?) [23502-214] at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:251) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015) at org.springframework.security.provisioning.JdbcUserDetailsManager.createUser(JdbcUserDetailsManager.java:200) at com.ctrip.framework.apollo.portal.spi.oidc.OidcLocalUserServiceImpl.createLocalUser(OidcLocalUserServiceImpl.java:68) at com.ctrip.framework.apollo.portal.spi.oidc.OidcAuthenticationSuccessEventListener.oauthUserLogin(OidcAuthenticationSuccessEventListener.java:90) at com.ctrip.framework.apollo.portal.spi.oidc.OidcAuthenticationSuccessEventListener.onApplicationEvent(OidcAuthenticationSuccessEventListener.java:69) at com.ctrip.framework.apollo.portal.spi.oidc.OidcAuthenticationSuccessEventListener.onApplicationEvent(OidcAuthenticationSuccessEventListener.java:37) at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:176) at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:169) at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:143) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:421) at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:378) at org.springframework.security.authentication.DefaultAuthenticationEventPublisher.publishAuthenticationSuccess(DefaultAuthenticationEventPublisher.java:99) at org.springframework.security.authentication.ProviderManager.authenticate(ProviderManager.java:226) at org.springframework.security.oauth2.client.web.OAuth2LoginAuthenticationFilter.attemptAuthentication(OAuth2LoginAuthenticationFilter.java:195) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:227) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:217) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.oauth2.client.web.OAuth2AuthorizationRequestRedirectFilter.doFilterInternal(OAuth2AuthorizationRequestRedirectFilter.java:178) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.oauth2.client.web.OAuth2AuthorizationRequestRedirectFilter.doFilterInternal(OAuth2AuthorizationRequestRedirectFilter.java:178) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:112) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:82) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:142) at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:102) 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:167) 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.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:389) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791) 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:833) Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "Email"; SQL statement: INSERT INTO "Users" ("Username", "Password", "Enabled") values (?,?,?) [23502-214] at org.h2.message.DbException.getJdbcSQLException(DbException.java:508) at org.h2.message.DbException.getJdbcSQLException(DbException.java:477) at org.h2.message.DbException.get(DbException.java:223) at org.h2.message.DbException.get(DbException.java:199) at org.h2.table.Column.validateConvertUpdateSequence(Column.java:365) at org.h2.table.Table.convertInsertRow(Table.java:926) at org.h2.command.dml.Insert.insertRows(Insert.java:167) at org.h2.command.dml.Insert.update(Insert.java:135) at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74) at org.h2.command.CommandContainer.update(CommandContainer.java:169) at org.h2.command.Command.executeUpdate(Command.java:252) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:209) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:169) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) ... 85 common frames omitted

- Configuration
- Platform and Operating System

[dosubot[bot]]

The issue you're encountering with OidcLocalUserServiceImpl#createLocalUser throwing an exception due to a missing email address in the UserPO entity appears to be related to how the OIDC user information is handled, particularly regarding the email attribute. This situation suggests that the OIDC provider might not be supplying an email address, or the application is not correctly configured to request or handle this attribute.

To address this, you should:

1. Confirm that your OIDC provider is configured to include the email address in the user information it supplies.
2. Ensure your application's OIDC configuration requests the email scope from the OIDC provider.

The method createLocalUser in OidcLocalUserServiceImpl is designed to create a new user with the provided UserInfo, which should include the email. If the email is not being set, it could lead to the JdbcSQLIntegrityConstraintViolationException due to the email column being non-null in your database schema.

If after verifying the OIDC provider configuration and your application's request for the email scope the problem persists, it might indicate a need to debug the application further to understand why the email attribute is not being correctly processed or populated.

Sources

• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcAuthenticationSuccessEventListener.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcLocalUserServiceImpl.java

#### About Dosu

This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. If you want to continue the conversation, start your reply with @dosu-bot.

[xyohn]

The issue you're encountering with OidcLocalUserServiceImpl#createLocalUser throwing an exception due to a missing email address in the UserPO entity appears to be related to how the OIDC user information is handled, particularly regarding the email attribute. This situation suggests that the OIDC provider might not be supplying an email address, or the application is not correctly configured to request or handle this attribute.

To address this, you should:

1. Confirm that your OIDC provider is configured to include the email address in the user information it supplies.
2. Ensure your application's OIDC configuration requests the email scope from the OIDC provider.

The method createLocalUser in OidcLocalUserServiceImpl is designed to create a new user with the provided UserInfo, which should include the email. If the email is not being set, it could lead to the JdbcSQLIntegrityConstraintViolationException due to the email column being non-null in your database schema.

If after verifying the OIDC provider configuration and your application's request for the email scope the problem persists, it might indicate a need to debug the application further to understand why the email attribute is not being correctly processed or populated.

Sources

• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcAuthenticationSuccessEventListener.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcLocalUserServiceImpl.java

About Dosu

It is not up to UserInfo,because JdbcUserDetailsManager.createUser need UserDetails,which is created in OidcLocalUserServiceImpl#createLocalUser.

    UserDetails user = new User(newUserInfo.getUserId(),
        this.placeholderDelegatingPasswordEncoder.encode(""), authorities);

The object user does not have the attribute email,which I think this is one of the reasons why cause the JdbcSQLIntegrityConstraintViolationException.

[dosubot[bot]]

The root cause of the JdbcSQLIntegrityConstraintViolationException you're encountering is indeed due to the createLocalUser method in OidcLocalUserServiceImpl not including the email attribute when creating a UserDetails object. This omission conflicts with your database schema where the 'Email' column is non-null.

To address this, you have a couple of options:

1. Modify the createLocalUser Method: Adjust the method to ensure that the email attribute is included when creating a new user. Since the User class from Spring Security used here doesn't support an email field directly, you might need to extend it or use a different approach to include the email information. This could involve creating a custom UserDetails implementation that includes email or ensuring that the email is set in a subsequent step that can handle the database's non-null constraint for the email column.

2. Database Schema Adjustment: Another approach could be to modify your database schema to allow the 'Email' column to be null temporarily, ensuring that the user creation process does not fail. Then, ensure that the email is updated immediately after the user is created. This approach, however, might not be ideal if the email attribute is critical for every user from the start.

Given the structure of your application and the constraints of your database, the first option might be more aligned with ensuring data integrity and consistency. It would require a closer look at how UserDetails is created and possibly customizing this process to include all necessary attributes from the start.

Sources

• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcAuthenticationSuccessEventListener.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcLocalUserServiceImpl.java
• scripts/sql/profiles/mysql-database-not-specified/apolloportaldb.sql

#### About Dosu

This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. If you want to continue the conversation, start your reply with @dosu-bot.

[xyohn]

The root cause of the JdbcSQLIntegrityConstraintViolationException you're encountering is indeed due to the createLocalUser method in OidcLocalUserServiceImpl not including the email attribute when creating a UserDetails object. This omission conflicts with your database schema where the 'Email' column is non-null.

To address this, you have a couple of options:

1. Modify the createLocalUser Method: Adjust the method to ensure that the email attribute is included when creating a new user. Since the User class from Spring Security used here doesn't support an email field directly, you might need to extend it or use a different approach to include the email information. This could involve creating a custom UserDetails implementation that includes email or ensuring that the email is set in a subsequent step that can handle the database's non-null constraint for the email column.
2. Database Schema Adjustment: Another approach could be to modify your database schema to allow the 'Email' column to be null temporarily, ensuring that the user creation process does not fail. Then, ensure that the email is updated immediately after the user is created. This approach, however, might not be ideal if the email attribute is critical for every user from the start.

Given the structure of your application and the constraints of your database, the first option might be more aligned with ensuring data integrity and consistency. It would require a closer look at how UserDetails is created and possibly customizing this process to include all necessary attributes from the start.

Sources

• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcAuthenticationSuccessEventListener.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcLocalUserServiceImpl.java
• scripts/sql/profiles/mysql-database-not-specified/apolloportaldb.sql

About Dosu

Yeah,and the problem also exists in the userDisplayName attribute.

[dosubot[bot]]

To resolve the issue with createLocalUser in OidcLocalUserServiceImpl not including email and userDisplayName attributes, leading to a JdbcSQLIntegrityConstraintViolationException, you'll need to ensure these attributes are included when creating a UserDetails object. The default User class from Spring Security doesn't support these additional fields directly, so you might consider creating a custom UserDetails implementation that includes email and userDisplayName.

Here's a brief outline of what you could do:

1. Implement a Custom UserDetails Class: Create a class that extends UserDetails and includes fields for email and userDisplayName. This class will be used to create UserDetails objects that contain all necessary information.

2. Modify the createLocalUser Method: Adjust this method to use your custom UserDetails implementation instead of the default User class. Ensure that it populates the email and userDisplayName fields based on the information available in UserInfo.

Here is a conceptual example of how the adjusted createLocalUser method might look:

@Transactional(rollbackFor = Exception.class)
@Override
public void createLocalUser(UserInfo newUserInfo) {
  CustomUserDetails user = new CustomUserDetails(newUserInfo.getUserId(),
      this.placeholderDelegatingPasswordEncoder.encode(""), newUserInfo.getEmail(), newUserInfo.getName(), authorities);
  userDetailsManager.createUser(user);
  this.updateUserInfoInternal(newUserInfo);
}

In this example, CustomUserDetails is your new class that includes the additional attributes. This approach ensures that the user creation process aligns with your database schema requirements, avoiding exceptions due to missing non-null fields.

Remember, this solution requires you to also adjust any other parts of your application that interact with UserDetails to accommodate the new attributes.

Sources

• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcAuthenticationSuccessEventListener.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/spi/oidc/OidcLocalUserServiceImpl.java
• apollo-portal/src/main/java/com/ctrip/framework/apollo/portal/entity/bo/UserInfo.java

#### About Dosu

This response is meant to be useful and save you time. It is **not meant to be a precise solution**, but rather a starting point for your own research. Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant. If you want to continue the conversation, start your reply with @dosu-bot.

[nobodyiam]

@vdiskg are you aware of this issue?