java 带有 jdbc 令牌存储的 spring boot oauth2 给出 oauth_access_token 关系不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34170281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
spring boot oauth2 with jdbc token store gives oauth_access_token relation doesn't exist
提问by Appy
I am trying to integrate spring boot with OAuth2. I was able to get this to work with InMemoryStore for tokens by following this https://github.com/royclarkson/spring-rest-service-oauth. But when I try to implement it with JdbcTokenStore and a postgres database I get the error
我正在尝试将 Spring Boot 与 OAuth2 集成。通过遵循此https://github.com/royclarkson/spring-rest-service-oauth ，我能够将其与 InMemoryStore 一起用于令牌。但是当我尝试使用 JdbcTokenStore 和 postgres 数据库来实现它时，我得到了错误
Handling error: BadSqlGrammarException, PreparedStatementCallback; bad SQL grammar [select token_id, token from oauth_access_token where authentication_id = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "oauth_access_token" does not exist
I have checked my DB, the table exists.
我检查了我的数据库，该表存在。
Web Security Config
网络安全配置
@Configuration
@EnableWebSecurity
public class WebSecurityConfig extends WebSecurityConfigurerAdapter {
private static PasswordEncoder encoder;
@Autowired
private CustomUserDetailsService userDetailsService;
@Override
protected void configure(AuthenticationManagerBuilder auth) throws Exception {
auth.userDetailsService(userDetailsService).passwordEncoder(passwordEncoder());
}
@Override
@Bean
public AuthenticationManager authenticationManagerBean() throws Exception {
return super.authenticationManagerBean();
}
@Bean
public PasswordEncoder passwordEncoder() {
if(encoder == null) {
encoder = new BCryptPasswordEncoder();
}
return encoder;
}
}
Oauth2Config
Oauth2Config
@Configuration
public class OAuth2ServerConfiguration {
private static final String RESOURCE_ID = "restservice";
@Configuration
@EnableResourceServer
protected static class ResourceServerConfiguration extends
ResourceServerConfigurerAdapter {
@Autowired
private TokenStore tokenStore;
@Override
public void configure(ResourceServerSecurityConfigurer resources) {
// @formatter:off
resources
.tokenStore(tokenStore)
.resourceId(RESOURCE_ID);
// @formatter:on
}
@Override
public void configure(HttpSecurity http) throws Exception {
// @formatter:off
http
.authorizeRequests()
.antMatchers("/users").hasRole("ADMIN")
.antMatchers("/userAccounts/create").permitAll()
.antMatchers("/greeting").authenticated();
// @formatter:on
}
}
@Configuration
@EnableAuthorizationServer
protected static class AuthorizationServerConfiguration extends
AuthorizationServerConfigurerAdapter {
@Autowired
DataSource dataSource;
@Bean
public JdbcTokenStore tokenStore() {
return new JdbcTokenStore(dataSource);
}
private static PasswordEncoder encoder;
@Autowired
@Qualifier("authenticationManagerBean")
private AuthenticationManager authenticationManager;
@Autowired
private CustomUserDetailsService userDetailsService;
@Override
public void configure(AuthorizationServerEndpointsConfigurer endpoints)
throws Exception {
// @formatter:off
endpoints
//.tokenStore(new InMemoryTokenStore())
.tokenStore(tokenStore())
.authenticationManager(this.authenticationManager)
.userDetailsService(userDetailsService);
// @formatter:on
}
@Override
public void configure(ClientDetailsServiceConfigurer clients) throws Exception {
// @formatter:off
clients
//.inMemory()
.jdbc(dataSource)
.passwordEncoder(passwordEncoder());
//.withClient("clientapp")
//.authorizedGrantTypes("password", "refresh_token")
//.authorities("USER")
//.scopes("read", "write")
//.resourceIds(RESOURCE_ID)
//.secret("123456");
// @formatter:on
}
@Bean
@Primary
public DefaultTokenServices tokenServices() {
DefaultTokenServices tokenServices = new DefaultTokenServices();
tokenServices.setSupportRefreshToken(true);
tokenServices.setTokenStore(tokenStore());
//tokenServices.setTokenStore(new InMemoryTokenStore());
return tokenServices;
}
@Bean
public PasswordEncoder passwordEncoder() {
if(encoder == null) {
encoder = new BCryptPasswordEncoder();
}
return encoder;
}
}
}
CustomUserDetailsService
自定义用户详细信息服务
@Service
public class CustomUserDetailsService implements UserDetailsService {
private AccountInfoRepository accountInfoRepository;
@Autowired
public CustomUserDetailsService(AccountInfoRepository accountInfoRepository) {
this.accountInfoRepository = accountInfoRepository;
}
@Override
public UserDetails loadUserByUsername(String username) throws UsernameNotFoundException {
AccountInfo user = accountInfoRepository.findByUsername(username);
System.out.println("USER IS "+user);
if (user == null) {
throw new UsernameNotFoundException(String.format("User %s does not exist!", username));
}
return new UserRepositoryUserDetails(user);
}
private final static class UserRepositoryUserDetails extends AccountInfo implements UserDetails,Serializable {
private static final long serialVersionUID = 1L;
private UserRepositoryUserDetails(AccountInfo user) {
super(user);
}
@Override
public Collection<? extends GrantedAuthority> getAuthorities() {
return getRoles();
}
@Override
public boolean isAccountNonExpired() {
return true;
}
@Override
public boolean isAccountNonLocked() {
return true;
}
@Override
public boolean isCredentialsNonExpired() {
return true;
}
@Override
public boolean isEnabled() {
return true;
}
}
}
application.properties
应用程序属性
spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.default_schema=test
spring.jpa.hibernate.ddl-auto=none
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.schema=test
spring.profiles.active=dev
#Application specific
security.oauth2.client.client-id=clientapp
security.oauth2.client.client-secret=123456
security.oauth2.client.authorized-grant-types=password,refresh_token
security.oauth2.client.authorities=ROLE_USER
security.oauth2.client.scope=read,write
security.oauth2.client.resource-ids=restservice
security.oauth2.client.access-token-validity-seconds=1800
User object
用户对象
@JsonIgnoreProperties(ignoreUnknown = true)
@Entity
@Table(name = "account_info")
public class AccountInfo implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "account_id")
Integer accountId;
@Column(name = "account_name")
String accountName;
@Column(name = "address_line_1")
String addressLine1;
@Column(name = "address_line_2")
String addressLine2;
String city;
String state;
String country;
@NotEmpty
@Column(unique = true, nullable = false)
String username;
@NotEmpty
String password;
String email;
@JsonIgnore
@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = { @JoinColumn(name = "role_id") })
private Set<Role> roles = new HashSet<>();
public AccountInfo() {
}
public AccountInfo(AccountInfo accountInfo) {
this.accountId = accountInfo.getAccountId();
this.accountName = accountInfo.getAccountName();
this.username = accountInfo.getUsername();
this.password = accountInfo.getPassword();
this.roles = accountInfo.getRoles();
}
public boolean isSetup() {
return isSetup;
}
public void setSetup(boolean isSetup) {
this.isSetup = isSetup;
}
public Integer getAccountId() {
return accountId;
}
public void setAccountId(Integer accountId) {
this.accountId = accountId;
}
public String getAccountName() {
return accountName;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public String getAddressLine1() {
return addressLine1;
}
public void setAddressLine1(String addressLine1) {
this.addressLine1 = addressLine1;
}
public String getAddressLine2() {
return addressLine2;
}
public void setAddressLine2(String addressLine2) {
this.addressLine2 = addressLine2;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Set<Role> getRoles() {
return roles;
}
public void setRoles(Set<Role> roles) {
this.roles = roles;
}
This is how I create a user
这就是我创建用户的方式
account.setPassword(new BCryptPasswordEncoder().encode(account.getPassword()));
Set<Role> roles = new HashSet<>();
roles.add(new Role("ROLE_USER",1));
account.setRoles(roles);
AccountInfo savedAccount=accountInfoRepository.save(account);
OAuth2 tables
OAuth2 表
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)
);
ALTER TABLE oauth_client_details OWNER TO postgres;
CREATE TABLE oauth_client_token (
token_id VARCHAR(256),
token bytea,
authentication_id VARCHAR(256),
user_name VARCHAR(256),
client_id VARCHAR(256)
);
ALTER TABLE oauth_client_token OWNER TO postgres;
CREATE TABLE oauth_access_token (
token_id VARCHAR(256),
token bytea,
authentication_id VARCHAR(256),
user_name VARCHAR(256),
client_id VARCHAR(256),
authentication bytea,
refresh_token VARCHAR(256)
);
ALTER TABLE oauth_access_token OWNER TO postgres;
CREATE TABLE oauth_refresh_token (
token_id VARCHAR(256),
token bytea,
authentication bytea
);
ALTER TABLE oauth_refresh_token OWNER TO postgres;
CREATE TABLE oauth_code (
code VARCHAR(256), authentication bytea
);
ALTER TABLE oauth_code OWNER TO postgres;
回答by Dave Syer
The OAuth2 JDBC connectors don't know about schemas. You would need to add the default schema to your user's profile in the database or else specify it explicitly in the URL. Something like this: jdbc:postgresql://localhost:5432/test?currentSchema=test.
OAuth2 JDBC 连接器不了解模式。您需要将默认架构添加到数据库中的用户配置文件中，或者在 URL 中明确指定它。像这样的东西：jdbc:postgresql://localhost:5432/test?currentSchema=test。
回答by MUNGAI NJOROGE
Resolved this by deleting everything from oauth_access_token table and adding setting tokenStore on both resource server and authentication server as follows:
通过从 oauth_access_token 表中删除所有内容并在资源服务器和身份验证服务器上添加设置 tokenStore 来解决此问题，如下所示：
@Override
public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
endpoints.authenticationManager(authenticationManager)
.tokenStore(tokenStore())
}
回答by mosgjig
Got it working by
得到它的工作
ALTER USER postgres SET search_path TO test,public;
where USERis deprecated, so instead
whereUSER已弃用，所以改为
ALTER ROLE postgres SET search_path TO test,public;
The value can be verified with:
该值可以通过以下方式验证：
SHOW search_path;
This will include the schema in the dataSource's search path. So jdbc queries will now come across the tables.
这将包括数据源搜索路径中的架构。所以 jdbc 查询现在将遇到表。
Alternativelyor more appropriately, double check test?currentSchema=testto make sure the db and the schema are as described. When working with docker, it sets up the db based off POSTGRES_USERunless specified with POSTGRES_DBso my root(your case postgres) user wasn't seeing test.
或者或更恰当地，仔细检查test?currentSchema=test以确保数据库和架构如所述。使用docker 时，它会基于 off 设置数据库，POSTGRES_USER除非指定，POSTGRES_DB因此我的root（您的情况postgres）用户没有看到test.
