Spring JdbcTokenStore 不存储/创建 token 。 (获取 token 失败)

Question

所以我已经尝试了一个多星期让 JdbcTokenStore 工作，但我似乎无法弄清楚出了什么问题。
我没有使用 Spring Boot ，我会尽力解释我在做什么。

因此，让我们从 token 的数据库开始:
我正在使用 PostgreSQL 这就是为什么我使用 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)
);

我还插入了 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);

我正在使用 BCrypt，这就是为什么在插入中加密密码的原因。 (密码= secret )

OAuth2SecurityConfig 又名 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;
    }

}

授权服务器配置

@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);
    }


}

资源服务器配置

@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());
    }

}

操作 1:



操作 2:插入凭据



通过阅读日志，我可以确认它确实找到了这个用户，我也看到了授权页面



所以按下授权后，我在我的 restClient (Insomnia) 中看到了这个



我一直在浏览日志。我将发布整个日志 + 对我来说似乎很重要的部分。

完整日志
https://pastebin.com/ALLLw8Ng

我认为的重要日志
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

任何形式的帮助将不胜感激!我现在有点绝望。 . .

Answer 1

所以在使用 https://github.com/adamzareba/company-structure-spring-security-oauth2-authorities 重写我的代码之后作为指南。我当前的项目如下所示:

ServerSecurityConfig 又名 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
}

资源服务器配置

@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
}

授权服务器配置

@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
}

虽然这个设置仍然不起作用，但我通过日志获得了更多信息。在日志中，我读到我的模型角色必须是可序列化的。这是因为我的 User 模型与 Role 具有多对一的关系。 (用户已经序列化)所以我有 2 个选项 1: @JsonIgnore用户中的角色或 2. 将 Serializable 添加到角色。

另一个重大变化是编辑 JdbcTokenStore使用 setter 查询。

结语

所以 token 没有存储在数据库中的原因:
1. 用户有一个映射到角色的关系，这意味着角色也必须是 Serializable .
2.(额外的，因为如果我使用预设的数据库结构就不需要了)重写 JdbcTokenStore中的预设查询匹配我的数据库表。

所有这些也解释了为什么生成了授权 key ，但从未存储在数据库中。