Spring Security OAuth2 Provider 之数据库存储

程序员文章站 2022-05-01 08:45:58

...

默认配置都是InMemory的，比如授权码，令牌，客户端信息等，实际应用时，应该是存入数据库里的。这里以PostgreSQL为例。

相关文章：
Spring Security OAuth2 Provider 之最小实现
Spring Security OAuth2 Provider 之数据库存储
Spring Security OAuth2 Provider 之第三方登录简单演示
Spring Security OAuth2 Provider 之自定义开发
Spring Security OAuth2 Provider 之整合JWT

（1）修改代码
基于前一篇最小化实现，需要改动以下代码：

pom.xml

<dependency>  
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
</dependency>

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/oauth2
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=user
spring.datasource.password=pass

@Configuration
@EnableAuthorizationServer
static class OAuthAuthorizationConfig extends AuthorizationServerConfigurerAdapter {
    @Autowired
    private Environment env;
    
    @Bean
    public DataSource dataSource() {
        final DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring.datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        return dataSource;
    }
    
    @Bean
    public ApprovalStore approvalStore() {
        return new JdbcApprovalStore(dataSource());
    }
    @Bean
    protected AuthorizationCodeServices authorizationCodeServices() {
        return new JdbcAuthorizationCodeServices(dataSource());
    }
    @Bean
    public TokenStore tokenStore() {
        return new JdbcTokenStore(dataSource());
    }
    
    @Override
    public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
        clients.jdbc(dataSource()); // oauth_client_details
    }
    
    @Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
		endpoints.approvalStore(approvalStore()) 							// oauth_approvals
		         .authorizationCodeServices(authorizationCodeServices()) 	// oauth_code
				 .tokenStore(tokenStore()); 								// oauth_access_token & oauth_refresh_token
    }
}

（2）数据库表

总共有5张表：

oauth_access_token：访问令牌
oauth_refresh_token：更新令牌
oauth_client_details：客户端信息
oauth_code：授权码
oauth_approvals：授权记录

*** oauth_client_token表比较特殊不用于Provider，是客户端用的

CREATE TABLE oauth_access_token
(
  token_id character varying(256), -- MD5加密的access_token的值
  token bytea, -- OAuth2AccessToken.java对象序列化后的二进制数据
  authentication_id character varying(256), -- MD5加密过的username,client_id,scope
  user_name character varying(256), -- 登录的用户名
  client_id character varying(256), -- 客户端ID
  authentication bytea, -- OAuth2Authentication.java对象序列化后的二进制数据
  refresh_token character varying(256) -- MD5加密果的refresh_token的值
);
COMMENT ON COLUMN oauth_access_token.token_id IS 'MD5加密的access_token的值';
COMMENT ON COLUMN oauth_access_token.token IS 'OAuth2AccessToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_access_token.authentication_id IS 'MD5加密过的username,client_id,scope';
COMMENT ON COLUMN oauth_access_token.user_name IS '登录的用户名';
COMMENT ON COLUMN oauth_access_token.client_id IS '客户端ID';
COMMENT ON COLUMN oauth_access_token.authentication IS 'OAuth2Authentication.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_access_token.refresh_token IS 'MD5加密果的refresh_token的值';

CREATE TABLE oauth_approvals
(
  userid character varying(256), -- 登录的用户名
  clientid character varying(256), -- 客户端ID
  scope character varying(256), -- 申请的权限
  status character varying(10), -- 状态（Approve或Deny）
  expiresat timestamp without time zone, -- 过期时间
  lastmodifiedat timestamp without time zone -- 最终修改时间
);
COMMENT ON COLUMN oauth_approvals.userid IS '登录的用户名';
COMMENT ON COLUMN oauth_approvals.clientid IS '客户端ID';
COMMENT ON COLUMN oauth_approvals.scope IS '申请的权限';
COMMENT ON COLUMN oauth_approvals.status IS '状态（Approve或Deny）';
COMMENT ON COLUMN oauth_approvals.expiresat IS '过期时间';
COMMENT ON COLUMN oauth_approvals.lastmodifiedat IS '最终修改时间';

CREATE TABLE oauth_client_details
(
  client_id character varying(256) NOT NULL, -- 客户端ID
  resource_ids character varying(256), -- 资源ID集合,多个资源时用逗号(,)分隔
  client_secret character varying(256), -- 客户端密匙
  scope character varying(256), -- 客户端申请的权限范围
  authorized_grant_types character varying(256), -- 客户端支持的grant_type
  web_server_redirect_uri character varying(256), -- 重定向URI
  authorities character varying(256), -- 客户端所拥有的Spring Security的权限值，多个用逗号(,)分隔
  access_token_validity integer, -- 访问令牌有效时间值(单位:秒)
  refresh_token_validity integer, -- 更新令牌有效时间值(单位:秒)
  additional_information character varying(4096), -- 预留字段
  autoapprove character varying(256), -- 用户是否自动Approval操作
  CONSTRAINT oauth_client_details_pkey PRIMARY KEY (client_id)
);
COMMENT ON COLUMN oauth_client_details.client_id IS '客户端ID';
COMMENT ON COLUMN oauth_client_details.resource_ids IS '资源ID集合,多个资源时用逗号(,)分隔';
COMMENT ON COLUMN oauth_client_details.client_secret IS '客户端密匙';
COMMENT ON COLUMN oauth_client_details.scope IS '客户端申请的权限范围';
COMMENT ON COLUMN oauth_client_details.authorized_grant_types IS '客户端支持的grant_type';
COMMENT ON COLUMN oauth_client_details.web_server_redirect_uri IS '重定向URI';
COMMENT ON COLUMN oauth_client_details.authorities IS '客户端所拥有的Spring Security的权限值，多个用逗号(,)分隔';
COMMENT ON COLUMN oauth_client_details.access_token_validity IS '访问令牌有效时间值(单位:秒)';
COMMENT ON COLUMN oauth_client_details.refresh_token_validity IS '更新令牌有效时间值(单位:秒)';
COMMENT ON COLUMN oauth_client_details.additional_information IS '预留字段';
COMMENT ON COLUMN oauth_client_details.autoapprove IS '用户是否自动Approval操作';

CREATE TABLE oauth_client_token
(
  token_id character varying(256), -- MD5加密的access_token值
  token bytea, -- OAuth2AccessToken.java对象序列化后的二进制数据
  authentication_id character varying(256), -- MD5加密过的username,client_id,scope
  user_name character varying(256), -- 登录的用户名
  client_id character varying(256) -- 客户端ID
);
COMMENT ON COLUMN oauth_client_token.token_id IS 'MD5加密的access_token值';
COMMENT ON COLUMN oauth_client_token.token IS 'OAuth2AccessToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_client_token.authentication_id IS 'MD5加密过的username,client_id,scope';
COMMENT ON COLUMN oauth_client_token.user_name IS '登录的用户名';
COMMENT ON COLUMN oauth_client_token.client_id IS '客户端ID';

CREATE TABLE oauth_code
(
  code character varying(256), -- 授权码(未加密)
  authentication bytea -- AuthorizationRequestHolder.java对象序列化后的二进制数据
);
COMMENT ON COLUMN oauth_code.code IS '授权码(未加密)';
COMMENT ON COLUMN oauth_code.authentication IS 'AuthorizationRequestHolder.java对象序列化后的二进制数据';

CREATE TABLE oauth_refresh_token
(
  token_id character varying(256), -- MD5加密过的refresh_token的值
  token bytea, -- OAuth2RefreshToken.java对象序列化后的二进制数据
  authentication bytea -- OAuth2Authentication.java对象序列化后的二进制数据
);
COMMENT ON COLUMN oauth_refresh_token.token_id IS 'MD5加密过的refresh_token的值';
COMMENT ON COLUMN oauth_refresh_token.token IS 'OAuth2RefreshToken.java对象序列化后的二进制数据';
COMMENT ON COLUMN oauth_refresh_token.authentication IS 'OAuth2Authentication.java对象序列化后的二进制数据';

手动插入一条客户端信息：

INSERT INTO oauth_client_details(client_id, resource_ids, client_secret, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove)
    VALUES ('oauth_client', null, 'oauth_client_secret', 'read,write', 'authorization_code,refresh_token', 'http://default-oauth-callback.com', 'ROLE_USER', 1800, 86400, null, false);

以上DDL文修改自官方提供的测试版本：
https://github.com/spring-projects/spring-security-oauth/blob/master/spring-security-oauth2/src/test/resources/schema.sql

测试确认的过程和上一篇完全一样。每执行完一步，可以到数据库对应的表中查看确认值。

上一篇：鸡兔同笼

下一篇： MQTT入门（6）- 主题Topics

Spring Security OAuth2 Provider 之数据库存储

使用JWT作为Spring Security OAuth2的token存储问题

Spring Security Oauth2基于JDBC存储令牌

Spring Security Oauth2 之架构源码解读

Hello Spring Cloud Alibaba（八）之使用spring security oAuth2

Spring Security OAuth2 Provider 之数据库存储

使用JWT作为Spring Security OAuth2的token存储问题

Spring Security OAuth2 Provider 之 数据库存储

使用JWT作为Spring Security OAuth2的token存储问题

Spring Security Oauth2基于JDBC存储令牌

Spring Security Oauth2 之 架构源码解读

Hello Spring Cloud Alibaba（八）之使用spring security oAuth2

Spring Security OAuth2 Provider 之 数据库存储

使用JWT作为Spring Security OAuth2的token存储问题

Spring Security OAuth2 Provider 之数据库存储

Spring Security Oauth2 之架构源码解读

Spring Security OAuth2 Provider 之数据库存储