spring boot oauth2 with jdbc token store gives oauth_access_token relation doesn't exist
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
.
Appy
I am a product person. I like taking up side projects to build something. I am currently working on a Javascript project. Languages I work with are Java, Python, Javascript, Sql and Scala
Updated on June 17, 2022Comments
-
Appy almost 2 years
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
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
@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
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;
-
Appy over 8 yearsSo I need to use the default schema ? I tried to set the current schema in the URL but unfortunately, that did not work.
-
Dave Syer over 8 yearsMost people just use the default schema I think. It's easy to set the default schema for the user if you log into the server (just google it).