Spring JdbcTokenStore doesn't store/ create tokens. (Failed to fetch token)

Question

So I've been trying for over a week to get the JdbcTokenStore to work, but I can't seem to figure out what is wrong.
I'm not using spring boot and I'll try my best to explain what I'm doing.

So let's start with the database for the tokens:
I'm using PostgreSQL which is why I use BYTEA

DROP TABLE IF EXISTS oauth_client_details;
CREATE TABLE oauth_client_details (
  client_id               VARCHAR(255) PRIMARY KEY,
  resource_ids            VARCHAR(255),
  client_secret           VARCHAR(255),
  scope                   VARCHAR(255),
  authorized_grant_types  VARCHAR(255),
  web_server_redirect_uri VARCHAR(255),
  authorities             VARCHAR(255),
  access_token_validity   INTEGER,
  refresh_token_validity  INTEGER,
  additional_information  VARCHAR(4096),
  autoapprove             VARCHAR(255)
);

DROP TABLE IF EXISTS oauth_client_token;
CREATE TABLE oauth_client_token (
  token_id          VARCHAR(255),
  token             BYTEA,
  authentication_id VARCHAR(255) PRIMARY KEY,
  user_name         VARCHAR(255),
  client_id         VARCHAR(255)
);

DROP TABLE IF EXISTS oauth_access_token;
CREATE TABLE oauth_access_token (
  token_id          VARCHAR(255),
  token             BYTEA,
  authentication_id VARCHAR(255) PRIMARY KEY,
  user_name         VARCHAR(255),
  client_id         VARCHAR(255),
  authentication    BYTEA,
  refresh_token     VARCHAR(255)
);

DROP TABLE IF EXISTS oauth_refresh_token;
CREATE TABLE oauth_refresh_token (
  token_id       VARCHAR(255),
  token          BYTEA,
  authentication BYTEA
);

DROP TABLE IF EXISTS oauth_code;
CREATE TABLE oauth_code (
  code           VARCHAR(255),
  authentication BYTEA
);

DROP TABLE IF EXISTS oauth_approvals;
CREATE TABLE oauth_approvals (
  userId         VARCHAR(255),
  clientId       VARCHAR(255),
  scope          VARCHAR(255),
  status         VARCHAR(10),
  expiresAt      TIMESTAMP,
  lastModifiedAt TIMESTAMP
);

DROP TABLE IF EXISTS ClientDetails;
CREATE TABLE ClientDetails (
  appId                  VARCHAR(255) PRIMARY KEY,
  resourceIds            VARCHAR(255),
  appSecret              VARCHAR(255),
  scope                  VARCHAR(255),
  grantTypes             VARCHAR(255),
  redirectUrl            VARCHAR(255),
  authorities            VARCHAR(255),
  access_token_validity  INTEGER,
  refresh_token_validity INTEGER,
  additionalInformation  VARCHAR(4096),
  autoApproveScopes      VARCHAR(255)
);

I'm also inserting the client_details:

INSERT INTO oauth_client_details (client_id, client_secret, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity,
                                  additional_information, autoapprove)
VALUES ('my-trusted-client', '$2a$04$Ovgng6BUO6tPPnZNkp8OuOjeBIM1mj5KVvo4r1a9Zh/py14yA0w9u', 'trust,read,write',
        'password,authorization_code,refresh_token', NULL, NULL, 36000, 36000, NULL, TRUE);

I'm using BCrypt which is why the password is encrypted in the insert. (password = secret)

OAuth2SecurityConfig AKA WebSecurityConfigurerAdapter

@Configuration
@EnableWebSecurity
@ComponentScan(basePackages = "com.hitmax.server")
@Order(1)
public class OAuth2SecurityConfig extends WebSecurityConfigurerAdapter {

    @Autowired
    private ClientDetailsService clientDetailsService;

    @Autowired
    private AuthenticationService authenticationService;

    @Autowired
    private PasswordEncoder passwordEncoder;

    @Autowired
    private DataSource dataSource;

    @Autowired
    public void globalUserDetails(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(authenticationService).passwordEncoder(passwordEncoder);
    }

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.requestMatchers()
                .antMatchers("/login", "/oauth/authorize")
                .and()
                .authorizeRequests()
                .anyRequest().authenticated()
                .and()
                .formLogin()
                .loginPage("/login")
                .usernameParameter("username").passwordParameter("password")
                .permitAll();
    }

    @Override
    @Bean
    public AuthenticationManager authenticationManagerBean() throws Exception {
        return super.authenticationManagerBean();
    }

    @Bean
    public PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

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

    @Bean
    @Autowired
    public TokenStoreUserApprovalHandler userApprovalHandler(TokenStore tokenStore) {
        TokenStoreUserApprovalHandler handler = new TokenStoreUserApprovalHandler();
        handler.setTokenStore(tokenStore);
        handler.setRequestFactory(new DefaultOAuth2RequestFactory(clientDetailsService));
        handler.setClientDetailsService(clientDetailsService);
        return handler;
    }

    @Bean
    @Autowired
    public ApprovalStore approvalStore(TokenStore tokenStore) throws Exception {
        TokenApprovalStore store = new TokenApprovalStore();
        store.setTokenStore(tokenStore);
        return store;
    }

}

AuthorizationServerConfig

@Configuration
@EnableAuthorizationServer
public class AuthorizationServerConfig extends AuthorizationServerConfigurerAdapter {


    private static String REALM = "MY_OAUTH_REALM";
    private final UserApprovalHandler userApprovalHandler;
    private final AuthenticationManager authenticationManager;
    private final PasswordEncoder passwordEncoder;
    private final TokenStore tokenStore;

    @Autowired
    public AuthorizationServerConfig(UserApprovalHandler userApprovalHandler, @Qualifier("authenticationManagerBean") AuthenticationManager authenticationManager, PasswordEncoder passwordEncoder, TokenStore tokenStore) {
        this.userApprovalHandler = userApprovalHandler;
        this.authenticationManager = authenticationManager;
        this.passwordEncoder = passwordEncoder;
        this.tokenStore = tokenStore;
    }

    @Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
        clients.inMemory()
                .withClient("my-trusted-client")
                .resourceIds("my_rest_api")
                .authorizedGrantTypes("authorization_code", "refresh_token")
                .autoApprove(false)
                .authorities("ROLE_CLIENT", "ROLE_TRUSTED_CLIENT")
                .scopes("read", "write", "trust")
                .secret("$2a$04$Ovgng6BUO6tPPnZNkp8OuOjeBIM1mj5KVvo4r1a9Zh/py14yA0w9u")
                .accessTokenValiditySeconds(120).//Access token is only valid for 2 minutes.
                refreshTokenValiditySeconds(600);//Refresh token is only valid for 10 minutes.
    }

    @Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
        endpoints.tokenStore(tokenStore).userApprovalHandler(userApprovalHandler)
                .authenticationManager(authenticationManager);
    }

    @Override
    public void configure(AuthorizationServerSecurityConfigurer oauthServer) throws Exception {
        oauthServer.realm(REALM + "/client")
                .tokenKeyAccess("permitAll()")
                .checkTokenAccess("isAuthenticated()")
                .allowFormAuthenticationForClients()
                .passwordEncoder(passwordEncoder);
    }


}

ResourceServerConfig

@Configuration
@EnableResourceServer
public class ResourceServerConfig extends ResourceServerConfigurerAdapter {

    private static final String RESOURCE_ID = "my_rest_api";

    @Autowired
    TokenStore tokenStore;

    @Override
    public void configure(ResourceServerSecurityConfigurer resources) {
        resources.tokenStore(tokenStore).resourceId(RESOURCE_ID).stateless(false);
    }
    
    @Override
    public void configure(HttpSecurity http) throws Exception {
        http
                .authorizeRequests()
                .antMatchers("/resources/**").permitAll()
                .anyRequest().authenticated()
                .and().
                anonymous().disable()
                .requestMatchers().antMatchers("/protected/**")
                .and().authorizeRequests()
                .antMatchers("/admin/**").access("hasRole('ROLE_ADMIN')")
                .and().exceptionHandling().accessDeniedHandler(new OAuth2AccessDeniedHandler());
    }
    
}

Action1 :

Action2 : inserting credentials

By reading the logs I can can confirm that it did found this user, also I see the authorize page

So after pressing Authorize I see this in my restClient (Insomnia)

I've been going through the logs. I'll post the entire log + the parts that seemed important to me.

Full log
https://pastebin.com/ALLLw8Ng

important logs according to me
1

22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.security.oauth2.provider.code.AuthorizationCodeTokenGranter - Getting access token for: my-trusted-client
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select token_id, token from oauth_access_token where authentication_id = ?]
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:postgresql://localhost:5432/hitmaxServer]
22:18:54.657 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
22:18:54.657 [http-nio-8080-exec-8] DEBUG org.springframework.security.oauth2.provider.token.store.JdbcTokenStore - Failed to find access token for authentication org.springframework.security.oauth2.provider.OAuth2Authentication@3aa38da: Principal: com.hitmax.server.mvc.dao.service.user.AuthenticationService$1@2dc4f5d4; Credentials: [PROTECTED]; Authenticated: true; Details: null; Granted Authorities: ROLE_USER

2

22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.security.oauth2.provider.code.AuthorizationCodeTokenGranter - Getting access token for: my-trusted-client
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL query
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.core.JdbcTemplate - Executing prepared SQL statement [select token_id, token from oauth_access_token where authentication_id = ?]
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
22:18:54.580 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DriverManagerDataSource - Creating new JDBC DriverManager Connection to [jdbc:postgresql://localhost:5432/hitmaxServer]
22:18:54.657 [http-nio-8080-exec-8] DEBUG org.springframework.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
22:18:54.657 [http-nio-8080-exec-8] DEBUG org.springframework.security.oauth2.provider.token.store.JdbcTokenStore - Failed to find access token for authentication org.springframework.security.oauth2.provider.OAuth2Authentication@3aa38da: Principal: com.hitmax.server.mvc.dao.service.user.AuthenticationService$1@2dc4f5d4; Credentials: [PROTECTED]; Authenticated: true; Details: null; Granted Authorities: ROLE_USER

Any kind of help would be appreciated! I'm kind of desperate now.

Answer 1

So after rewriting my code using https://github.com/adamzareba/company-structure-spring-security-oauth2-authorities as an guide. My current project looks as followed:

ServerSecurityConfig AKA WebSecurityConfigurerAdapter

@Configuration
@EnableWebSecurity
@ComponentScan(basePackages = "com.hitmax.server")
@Order(1)
@Import(Encoders.class)
public class ServerSecurityConfig extends WebSecurityConfigurerAdapter {

    // region: fields
    @Autowired
    private AuthenticationService authenticationService;

    @Autowired
    private PasswordEncoder userPasswordEncoder;
    // endregion: fields

    // region: methods
    @Override
    @Bean
    public AuthenticationManager authenticationManagerBean() throws Exception {
        return super.authenticationManagerBean();
    }

    @Override
    protected void configure(AuthenticationManagerBuilder auth) throws Exception {
        auth.userDetailsService(authenticationService).passwordEncoder(userPasswordEncoder);
    }
    // endregion: methods
}

ResourceServerConfig

@Configuration
@EnableResourceServer
public class ResourceServerConfig extends ResourceServerConfigurerAdapter {

    // region: fields
    private static final String RESOURCE_ID = "resource-server-rest-api";
    private static final String SECURED_READ_SCOPE = "#oauth2.hasScope('read')";
    private static final String SECURED_WRITE_SCOPE = "#oauth2.hasScope('write')";
    private static final String SECURED_PATTERN = "/secured/**";
    // endregion: fields
    // region: methods
    @Override
    public void configure(ResourceServerSecurityConfigurer resources) {
        resources.resourceId(RESOURCE_ID);
    }

    @Override
    public void configure(HttpSecurity http) throws Exception {
        http.requestMatchers()
                .antMatchers(SECURED_PATTERN).and().authorizeRequests()
                .antMatchers(HttpMethod.POST, SECURED_PATTERN).access(SECURED_WRITE_SCOPE)
                .anyRequest().access(SECURED_READ_SCOPE);
    }
    // endregion: methods
}

AuthorizationServerConfig

@Configuration
@EnableAuthorizationServer
public class AuthorizationServerConfig extends AuthorizationServerConfigurerAdapter {

    // region: fields
    @Autowired
    private DataSource dataSource;

    @Autowired
    private AuthenticationManager authenticationManager;

    @Autowired
    private UserDetailsService userDetailsService;

    @Autowired
    private PasswordEncoder oauthClientPasswordEncoder;

    // endregion: fields

    // region: methods
    // region: beans
    @Bean
    public TokenStore tokenStore() {
        String insertAccessTokenSql = "insert into oauth_access_token (token_id, token, authentication_id, email, client_id, authentication, refresh_token) values (?, ?, ?, ?, ?, ?, ?)";
        String selectAccessTokensFromUserNameAndClientIdSql = "select token_id, token from oauth_access_token where email = ? and client_id = ?";
        String selectAccessTokensFromUserNameSql = "select token_id, token from oauth_access_token where email = ?";
        String selectAccessTokensFromClientIdSql = "select token_id, token from oauth_access_token where client_id = ?";
        String insertRefreshTokenSql = "insert into oauth_refresh_token (token_id, token, authentication) values (?, ?, ?)";

        JdbcTokenStore jdbcTokenStore = new JdbcTokenStore(dataSource);
        jdbcTokenStore.setInsertAccessTokenSql(insertAccessTokenSql);
        jdbcTokenStore.setSelectAccessTokensFromUserNameAndClientIdSql(selectAccessTokensFromUserNameAndClientIdSql);
        jdbcTokenStore.setSelectAccessTokensFromUserNameSql(selectAccessTokensFromUserNameSql);
        jdbcTokenStore.setSelectAccessTokensFromClientIdSql(selectAccessTokensFromClientIdSql);
        jdbcTokenStore.setInsertRefreshTokenSql(insertRefreshTokenSql);


        return jdbcTokenStore;
    }

    @Bean
    public OAuth2AccessDeniedHandler oauthAccessDeniedHandler() {
        return new OAuth2AccessDeniedHandler();
    }

    // endregion: beans
    @Override
    public void configure(AuthorizationServerSecurityConfigurer oauthServer) {
        oauthServer.tokenKeyAccess("permitAll()")
                .checkTokenAccess("isAuthenticated()")
                .passwordEncoder(oauthClientPasswordEncoder);
    }

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

    @Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) {
        endpoints.tokenStore(tokenStore()).authenticationManager(authenticationManager).userDetailsService(userDetailsService);
    }
    // endregion: methods
}

Whil this setup still didn't work, I got more info trough the logs then usual. In the logs I read that my model Role had to be Serializable. This is because my User model has a many to one relationship with Role. (User was already Serialized) So I had 2 options 1: @JsonIgnore roles in User or 2. add Serializable to Role.

Another big change was to edit the JdbcTokenStore queries using the setters.

Final word

So the reason why the tokens weren't stored in the database:
1. User had a relationship mapped to roles, which meant that Role's also had to be Serializable.
2. (extra, because this wouldn't be needed if I used the preset database structure) Rewriting the preset queries in JdbcTokenStore to match my database tables.

All of this also explains why the authorization key was generated, but never stored in the database.