Spring-security-oauth: oauth_access_token Duplicate entry for PRIMARY key, MySQLIntegrityConstraintViolationException

[maxim-filkov]

Hello,

I'm experiencing a problem with spring-security-oauth (2.0.11.RELEASE) that is trying to create duplicating access tokens. On attempt to get access_token by refresh_token:

curl -X POST -vu app123:TBe6G8W3t7Uv http://localhost:8090/oauth/token -H "Accept: application/json" -d "refresh_token=eyJhbGciOiJIUz...Wk_vs2nSk&grant_type=refresh_token&scope=read%20write&client_secret=TBe6G8W3t7Uv&client_id=app123"

I get:

{"error":"server_error","error_description":"PreparedStatementCallback; SQL [insert into oauth_access_token (token_id, token, authentication_id, user_name, client_id, authentication, refresh_token) values (?, ?, ?, ?, ?, ?, ?)]; Duplicate entry '0bc2efb38e22bccb7e6f1f565165d243' for key 'PRIMARY'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '0bc2efb38e22bccb7e6f1f565165d243' for key 'PRIMARY'"}

Any help would be really appreciated.

Database table oauth_access_token

`oauth_access_token` (
  `token_id` varchar(256) DEFAULT NULL,
  `token` blob,
  `authentication_id` varchar(256) NOT NULL,
  `user_name` varchar(256) DEFAULT NULL,
  `client_id` varchar(256) DEFAULT NULL,
  `authentication` blob,
  `refresh_token` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`authentication_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Database config

@Configuration
@EnableTransactionManagement
public class MySQLConfig {

   @Value("#{environment.MARIADB_PORT_3306_TCP_ADDR}")
   private String serverMySQLHost;

   @Value("#{environment.MARIADB_PORT_3306_TCP_PORT}")
   private String serverMySQLPort;

   @Value("#{environment.MARIADB_ENV_MYSQL_ROOT_PASSWORD}")
   private String serverMySQLRootPassword;

   /**
    * Data source for MySQL database (mainly used to save user credentials).
    *
    * @return Data source bean.
    */
   @Bean
   public DataSource dataSource() {
      final DriverManagerDataSource dataSource = new DriverManagerDataSource();
      dataSource.setUrl(String.format("jdbc:mysql://%s:%s/authorizator", serverMySQLHost, serverMySQLPort));
      dataSource.setUsername("root");
      dataSource.setPassword(serverMySQLRootPassword);
      return dataSource;
   }

   @Bean
   public DataSourceTransactionManager txManager() {
      return new DataSourceTransactionManager(dataSource());
   }

}

OAuth2Configuration

@Configuration
public class OAuth2Configuration {

   private static final String RESOURCE_ID = "users";

   @Configuration
   @EnableResourceServer
   protected static class ResourceServerConfiguration extends ResourceServerConfigurerAdapter {

      @Autowired
      private JwtAccessTokenConverter jwtAccessTokenConverter;

      @Override
      public void configure(final ResourceServerSecurityConfigurer resources) {
         resources.resourceId(RESOURCE_ID).tokenStore(new JwtTokenStore(jwtAccessTokenConverter));
      }

      @Override
      public void configure(final HttpSecurity http) throws Exception {
         http
               .csrf().disable()
               .authorizeRequests()
               .antMatchers("/user/**").authenticated();
      }

   }

   @Configuration
   @EnableAuthorizationServer
   protected static class AuthorizationServerConfiguration extends AuthorizationServerConfigurerAdapter {

      @Autowired
      private DataSource dataSource;

      @Autowired
      private JwtAccessTokenConverter jwtAccessTokenConverter;

      @Autowired
      @Qualifier("authenticationManagerBean")
      private AuthenticationManager authenticationManager;

      @Autowired
      private CustomUserDetailsService userDetailsService;

      @Override
      public void configure(final AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
         endpoints
               .tokenStore(tokenStore())
               .authenticationManager(authenticationManager)
               .accessTokenConverter(jwtAccessTokenConverter)
               .userDetailsService(userDetailsService);
      }

      @Override
      public void configure(final ClientDetailsServiceConfigurer clients) throws Exception {
         clients
               .jdbc(dataSource);
      }

      @Bean
      @Transactional
      public DefaultTokenServices tokenServices() {
         final DefaultTokenServices tokenServices = new DefaultTokenServices();
         tokenServices.setSupportRefreshToken(true);
         tokenServices.setTokenStore(tokenStore());
         return tokenServices;
      }

      @Bean
      public TokenStore tokenStore() {
         return new JdbcTokenStore(dataSource);
      }

   }

}

[maxim-filkov]

A bit more info, the described above issue happens when second time ask for access_token by same refresh_token. Duplicated row is inserted into oauth_access_token. So it is not about race condition.

[benkiefer]

@dsyer @jgrandja This is a new issue that appears to have been introduced in spring security oauth2 with version 2.0.11.RELEASE.

We essentially cannot refresh access tokens once they've expired.

@maxim-filkov for now, we've downgraded back to 2.0.10.RELEASE.

[maxim-filkov]

Hi Ben, thank you. I've prepared a fix, I'm creating a pull request right now.
Here is the Pull Request.
Several tests are failed though :'(

[maxim-filkov]

I've just checked with 2.0.10.RELEASE in the code and also was able to reproduce it - the issue is there as well.

[born2snipe]

Here is a PR to address the underlying issue.

[born2snipe]

If you subclass DefaultAuthenticationKeyGenerator and override the method extractKey(...) and make it return a debuggable key like below:

Map<String, String> values = new LinkedHashMap<String, String>();
OAuth2Request authorizationRequest = authentication.getOAuth2Request();
if (!authentication.isClientOnly()) {
   values.put(USERNAME, authentication.getName());
}
values.put(CLIENT_ID, authorizationRequest.getClientId());
if (authorizationRequest.getScope() != null) {
  values.put(SCOPE, OAuth2Utils.formatParameterList(authorizationRequest.getScope()));
}
return values.toString();

You will see the scopes are some times out of order so the authentication_id will have a different checksum value.

[maxim-filkov]

Hi Dan, I'm still able to reproduce the issue with born2snipe:always-sort-scopes-before-digesting. Issue scenario is the same as described here.

[jgrandja]

Closing this issue as JwtAccessTokenConverter is meant to work with JwtTokenStore as per design. See further comments in #866