🐈

Spring Authorization ServerでデータをDBに保存する

2023/03/19に公開

Spring Authorization Serverで認可サーバを作成するの記事では、クライアントやトークンに関するデータはインメモリに格納していましたが、実際に本番で利用する場合などはDBに格納したいことが多いと思います。
ということで今回はDBに格納する方法の記事です。

事前設定

今回はspring-boot-starter-jdbcとh2を利用して行います。

build.gradle
dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.springframework.boot:spring-boot-starter-jdbc'
	implementation 'org.springframework.security:spring-security-oauth2-authorization-server:1.0.0'
	implementation 'com.h2database:h2'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
application.yaml
spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:testdb
    username: sa
    password:
    initialization-mode: always
  # ここ以下は検証用の設定
  h2:
    console:
      enabled: true
logging:
  level:
    org:
      springframework:
        jdbc:
          core: TRACE

Config設定

基本的にはSpring Authorization Serverで認可サーバを作成するの記事と一緒なので異なる箇所を記載します。

テーブルの作成

クライアントに関する情報とトークンを格納するテーブルを作成します。
こちらはSpring Authorization ServerがSQLを用意してくれているのでそれを設定すればOKです。

@Bean
public DataSource dataSource(){
  return new EmbeddedDatabaseBuilder()
      .setType(EmbeddedDatabaseType.H2)
      .setScriptEncoding("UTF-8")
      .addScript("org/springframework/security/oauth2/server/authorization/oauth2-authorization-schema.sql")
      .addScript("org/springframework/security/oauth2/server/authorization/client/oauth2-registered-client-schema.sql")
      .build();
}

RegisteredClientRepositoryのBeanを作成

RegisteredClientRepositoryをJdbcRegisteredClientRepositoryのクラスで作成します。

@Bean
public RegisteredClientRepository registeredClientRepository(JdbcTemplate jdbcTemplate) {
  RegisteredClient registeredClient = RegisteredClient.withId(UUID.randomUUID().toString())
      .clientId("messaging-client")
      .clientSecret("{noop}secret")
      .clientAuthenticationMethod(ClientAuthenticationMethod.CLIENT_SECRET_BASIC)
      .authorizationGrantType(AuthorizationGrantType.AUTHORIZATION_CODE)
      .authorizationGrantType(AuthorizationGrantType.REFRESH_TOKEN)
      .authorizationGrantType(AuthorizationGrantType.CLIENT_CREDENTIALS)
      .redirectUri("http://127.0.0.1:8080/login/oauth2/code/messaging-client-oidc")
      .redirectUri("http://127.0.0.1:8080/authorized")
      .scope(OidcScopes.OPENID)
      .scope(OidcScopes.PROFILE)
      .scope("message.read")
      .scope("message.write")
      .clientSettings(ClientSettings.builder().requireAuthorizationConsent(true).build())
      .build();

  RegisteredClientRepository registeredClientRepository = new JdbcRegisteredClientRepository(jdbcTemplate);
  registeredClientRepository.save(registeredClient);
  return registeredClientRepository;
}

OAuth2AuthorizationServiceの作成

OAuth2AuthorizationServiceをJdbcOAuth2AuthorizationServiceのクラスで作成します。

@Bean
OAuth2AuthorizationService authorizationService(JdbcTemplate jdbcTemplate, RegisteredClientRepository registeredClientRepository) {
  return new JdbcOAuth2AuthorizationService(jdbcTemplate, registeredClientRepository);
}

以上です。

確認

以下のように作成したテーブルにデータを作成するSQLが流れていることが確認できます。

起動時
2023-03-19T17:28:06.437+09:00 DEBUG 9528 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO oauth2_registered_client(id, client_id, client_id_issued_at, client_secret, client_secret_expires_at, client_name, client_authentication_methods, authorization_grant_types, redirect_uris, scopes, client_settings,token_settings) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [56670475-3b5b-45e8-b4b6-f9a3b1f082c5], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [messaging-client], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [2023-03-19 17:28:06.406494], value class [java.sql.Timestamp], SQL type 93
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [{noop}secret], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [null], value class [null], SQL type 93
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 6, parameter value [56670475-3b5b-45e8-b4b6-f9a3b1f082c5], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 7, parameter value [client_secret_basic], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 8, parameter value [refresh_token,client_credentials,authorization_code], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 9, parameter value [http://127.0.0.1:8080/authorized,http://127.0.0.1:8080/login/oauth2/code/messaging-client-oidc], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.438+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 10, parameter value [openid,profile,message.read,message.write], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.439+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 11, parameter value [{"@class":"java.util.Collections$UnmodifiableMap","settings.client.require-proof-key":false,"settings.client.require-authorization-consent":true}], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.439+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 12, parameter value [{"@class":"java.util.Collections$UnmodifiableMap","settings.token.reuse-refresh-tokens":true,"settings.token.id-token-signature-algorithm":["org.springframework.security.oauth2.jose.jws.SignatureAlgorithm","RS256"],"settings.token.access-token-time-to-live":["java.time.Duration",300.000000000],"settings.token.access-token-format":{"@class":"org.springframework.security.oauth2.server.authorization.settings.OAuth2TokenFormat","value":"self-contained"},"settings.token.refresh-token-time-to-live":["java.time.Duration",3600.000000000],"settings.token.authorization-code-time-to-live":["java.time.Duration",300.000000000]}], value class [java.lang.String], SQL type 12
2023-03-19T17:28:06.440+09:00 TRACE 9528 --- [           main] o.s.jdbc.core.JdbcTemplate               : SQL update affected 1 rows

トークン作成時
2023-03-19T17:29:05.101+09:00 DEBUG 9528 --- [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO oauth2_authorization (id, registered_client_id, principal_name, authorization_grant_type, authorized_scopes, attributes, state, authorization_code_value, authorization_code_issued_at, authorization_code_expires_at,authorization_code_metadata,access_token_value,access_token_issued_at,access_token_expires_at,access_token_metadata,access_token_type,access_token_scopes,oidc_id_token_value,oidc_id_token_issued_at,oidc_id_token_expires_at,oidc_id_token_metadata,refresh_token_value,refresh_token_issued_at,refresh_token_expires_at,refresh_token_metadata) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]
2023-03-19T17:29:05.101+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [2db4a46e-64c5-445e-befe-6f4b14d95b26], value class [java.lang.String], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [56670475-3b5b-45e8-b4b6-f9a3b1f082c5], value class [java.lang.String], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [user], value class [java.lang.String], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [authorization_code], value class [java.lang.String], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [null], value class [null], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 7, parameter value [6xdyJ105U2EPF0U8aQsj0FFLf53vG08_X0duRYmvvHo=], value class [java.lang.String], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 9, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 10, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 13, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 14, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 16, parameter value [null], value class [null], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 17, parameter value [null], value class [null], SQL type 12
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 19, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 20, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 23, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.102+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 24, parameter value [null], value class [null], SQL type 93
2023-03-19T17:29:05.103+09:00 TRACE 9528 --- [nio-8080-exec-3] o.s.jdbc.core.JdbcTemplate               : SQL update affected 1 rows

H2コンソールをSpringSecurityを適用した状態で確認する

今回の内容と直接関係ありませんが、
http://localhost:8080/h2-console を確認しようとした際に403が発生して見れずに若干苦戦したので、その設定内容を記載しておきます。

import static org.springframework.boot.autoconfigure.security.servlet.PathRequest.toH2Console;

@Bean
@Order(2)
public SecurityFilterChain defaultSecurityFilterChain(HttpSecurity http)
      throws Exception {
  http
      .authorizeHttpRequests()
      .requestMatchers(toH2Console()).permitAll()
      .anyRequest().authenticated()
      .and()
      .formLogin(Customizer.withDefaults())
      .csrf().ignoringRequestMatchers(toH2Console())
      .and()
      .headers().frameOptions().sameOrigin();

  return http.build();
}

Discussion