Spring-security-oauth: org.springframework.security.oauth2.provider.ClientAlreadyExistsException when use oauth2 and mysql

[tarlaw]

I want to try spring oauth2 jdbc with mysql, so I modify the test project from https://github.com/spring-projects/spring-security-oauth/tree/master/tests/annotation/jdbc.

The modified project locate at https://github.com/tarlaw/oauth2-mysql.

1. I add application.properties as:

server.port = 8090

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.ddl-auto=update

spring.datasource.url=jdbc:mysql://localhost:3306/oauth2
spring.datasource.username=oauth
spring.datasource.password=oauth
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

1. And modify

create table oauth_client_details (
  client_id VARCHAR(256) PRIMARY KEY,
  resource_ids VARCHAR(256),
  client_secret VARCHAR(256),
  scope VARCHAR(256),
  authorized_grant_types VARCHAR(256),
  web_server_redirect_uri VARCHAR(256),
  authorities VARCHAR(256),
  access_token_validity INTEGER,
  refresh_token_validity INTEGER,
  additional_information VARCHAR(4096),
  autoapprove VARCHAR(256)
);

to

create table oauth_client_details (
  client_id VARCHAR(256) PRIMARY KEY,
  resource_ids VARCHAR(256),
  client_secret VARCHAR(256),
  scope VARCHAR(256),
  authorized_grant_types VARCHAR(256),
  web_server_redirect_uri VARCHAR(256),
  authorities VARCHAR(256),
  access_token_validity INTEGER,
  refresh_token_validity INTEGER,
  additional_information VARCHAR(4096),
  autoapprove VARCHAR(256)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1;

Everything run well before restart.
But when I restart

1. A table already exists error occur, so I delete sr/main/resources/scehma.sql and rebuild package and the error solved.
2. When I execute:

curl -X POST http://localhost:8090/oauth/token \
-u my-client-with-secret:secret \
-d "username=dave&password=secret&grant_type=password"

An exception occur

{"timestamp":1424965061254,"status":401,"error":"Unauthorized","message":"Error creating bean with name 'scopedTarget.clientDetailsService' defined in class org.springframework.security.oauth2.config.annotation.configuration.ClientDetailsServiceConfiguration: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.security.oauth2.provider.ClientDetailsService]: Factory method 'clientDetailsService' threw exception; nested exception is org.springframework.security.oauth2.provider.ClientAlreadyExistsException: Client already exists: my-trusted-client","path":"/oauth/token"}

However, these data should be there.
When delete these data manully in mysql use:

delete from oauth_client_details;

everything goes well.

But I think clear oauth_client_details table everty time when server restart is wrong.

[dsyer]

You need to remove the withClient() calls from your ClientDetailsService configuration callback, otherwise they are going to be inserted on startup (actually on first usage of the ClientDetailsService). Just call jdbc() and set the data source.

[tarlaw]

Thanks for the reply. It works for me.
This makes change Client so convenient.

[johnosorio]

Thanks, it works for me too!.

[dhairyashil120]

I am trying to create a spring+Oauth2+mysql project.
For that, I am referring https://github.com/dsyer/spring-rest-service-oauth.
It works with imMemory Token store, But same is not working when I am changing it to jdbcTokenStore.

@Configuration
public class OAuth2ServerConfiguration {

    private static final String RESOURCE_ID = "restservice";

    @Configuration
    @EnableResourceServer
    protected static class ResourceServerConfiguration extends
            ResourceServerConfigurerAdapter {

        @Override
        public void configure(ResourceServerSecurityConfigurer resources) {
            // @formatter:off
            resources
                .resourceId(RESOURCE_ID);
            // @formatter:on
        }

        @Override
        public void configure(HttpSecurity http) throws Exception {
            // @formatter:off
            http
                .authorizeRequests()
                    .anyRequest().authenticated();
            // @formatter:on
        }

    }

    @Configuration
    @EnableAuthorizationServer
    protected static class AuthorizationServerConfiguration extends
            AuthorizationServerConfigurerAdapter {

        @Autowired
        private DataSource dataSource;

        private TokenStore tokenStore = new JdbcTokenStore(dataSource);

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

        @Bean
        protected AuthorizationCodeServices authorizationCodeServices() {
            return new JdbcAuthorizationCodeServices(dataSource);
        }

        @Override
        public void configure(AuthorizationServerEndpointsConfigurer endpoints)
                throws Exception {
            // @formatter:off
            endpoints.authorizationCodeServices(authorizationCodeServices())
                .tokenStore(tokenStore)
                .authenticationManager(authenticationManager)
                .approvalStoreDisabled();
            // @formatter:on
        }

        @Override
        public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
            // @formatter:off
            clients
                .jdbc(dataSource)
                    .withClient("clientapp")
                        .authorizedGrantTypes("password","refresh_token")
                        .authorities("USER")
                        .scopes("read", "write")
                        .resourceIds(RESOURCE_ID)
                        .secret("123456");
            // @formatter:on
        }

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

    }
}

[dsyer]

This issue is closed. If you have general usage questions (which this appears to be), please post them on stack overflow. Also have a look at the tests projects in this repo (there's one that uses JDBC for everything).

[houssemzaier]

thank you @dsyer for all your help whenever I have a question I find your answer , and guess what it do always work !

[kamalhashi]

Thank you @dsyer.. your answer helped me

[danieltnaves]

Thanks @dsyer help me a lot!

[znikola]

@dsyer
Your proposed solution to remove withClient() is working, but how to initially specify clients?

[PrithiviRajG]

@dsyer @danieltnaves @znikola
Please share some examples, whether I have to do like the below code (removed withClient() method)?

            .jdbc(databaseConfig.dataSource())

                    .authorizedGrantTypes("password","authorization_code", "refresh_token")
                    .authorities("USER")
                    .scopes("read", "write")
                    .resourceIds(RESOURCE_ID)
                    .secret("123456")
                        .accessTokenValiditySeconds(180)
                        .refreshTokenValiditySeconds(180);

But I am getting exception on the next line, not able to get answers from stack over flow too...
Please help me to resolve the same.

[znikola]

@PrithiviRajG
Try configuring it just like this:

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

[danieltnaves]

@PrithiviRajG

That's my ClientDetailsServiceConfigurer

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

If you want to create sample client on database.

@Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
        clients.
        jdbc(dataSource())
                .withClient("mobileAppClient")
                    .secret(java.util.UUID.randomUUID().toString())
                    .authorizedGrantTypes("password","authorization_code", "refresh_token")
                    .scopes("read");
    }

That's my datasource setup:

@Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
        dataSource.setUrl(env.getProperty("jdbc.url"));
        dataSource.setUsername(env.getProperty("jdbc.user"));
        dataSource.setPassword(env.getProperty("jdbc.pass"));
        return dataSource;
    }

And my application.properties

################## DataSource Configuration ####################
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/oauth2?useSSL=false
jdbc.user=root
jdbc.pass=

[sumit-mittal-ttnd]

@dsyer @houssemzaier @danieltnaves

@dsyer say to remove this .withClient("_").secret("_").... and only add clients.jdbc(dataSource).passwordEncoder(passwordEncoder) to work. But no idea how to initialize client on application start up ?

For any user if we are hitting http://localhost:8080/myapp/oauth/token very first time.... then its throwing this error -
{
"timestamp": 1480148009855,
"status": 401,
"error": "Unauthorized",
"message": "Bad credentials",
"path": "/myapp/oauth/token"
}

Please suggest me how can we solve this.???

[msauza]

@sumit-mittal-ttnd if you are using spring boot, go to Database initialization docs.

to solve some "bad credentials", save encoded value passwords on database. Verify that password encoder is configured in methods:

configure(ClientDetailsServiceConfigurer clients)
configure(AuthorizationServerSecurityConfigurer oauthServer)

[lisandromc]

I had this same problem.
Removing all the configuration details from the configure method solved the duplicate key issue. However, the tokens generated now lack all the defaults that were set in that callback (e.g., token expiration is being set to default 1 hour and 1 month):
clients.jdbc(dataSource) .withClient("someclient") .secret(env.getProperty("oauth.secret")) .authorizedGrantTypes("password", "authorization_code", "refresh_token") .scopes("read", "write", "delete") .accessTokenValiditySeconds(env.getProperty("oauth.accessTokenTtl", Integer.class)) .refreshTokenValiditySeconds(env.getProperty("oauth.refreshTokenTtl", Integer.class)) ;
If not here, where (or how) am I supposed to set these defaults?
Thanks
Lisandro

[etschelp]

@lisandromc I guess you are supposed to add the client details by providing an .sql script that inserts them. This is an example I found:

https://github.com/ToastShaman/spring-security-oauth2-example/blob/master/oauth-server/src/main/resources/db/migration/V2__data.sql

Generally it seems like a missing Spring feature to test if the client details are already persisted, otherwise the Java Config becomes useless as soon as you switch from in memory to jdbc.

[gklp]

@dsyer
Your proposed solution to remove withClient() is working, but how to initially specify clients?

For initially specify clients; you can use; loadClientByClientId.

public void configure(final ClientDetailsServiceConfigurer clients) throws Exception {
    ClientDetails clientDetails = clients.jdbc(dataSource()).build().loadClientByClientId("foo");
    if (clientDetails == null) {
        clients.jdbc(dataSource())
                .withClient("foo")
                .secret("secret")
                .authorizedGrantTypes("password", "authorization_code", "refresh_token")
                .scopes("foo", "read", "write")
                .accessTokenValiditySeconds(60);
    }

[cleemansen]

Thanks @gklp for that hint! My next problem is now that your client lookup happens before flyway could created the necessary table, so this approach will fail w/ a missing table exception.

Any ideas?

[swapnil-kadam-jiem]

@gklp your code is working properly sir thanks