spring boot oauth2 with jdbc token store gives oauth_access_token relation doesn't exist

Question

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;

Are you sure you are connected to the same database as the app when you verify the data? Looks to me like it just can't find the table. Bad credentials in config? — Dave Syer, Dec 09 '15 at 18:34
Its hitting the right database. There was a print statement in CustomUserDetailsService which retrieves the user `USER IS AccountInfo{accountId=1, accountName='HAppy', addressLine1='null', addressLine2='null', city='null', state='null', country='null', username='test', email='null'}`. **This was the curl request** `curl -X POST -vu clientapp:123456 http://localhost:8080/oauth/token -H "Accept: application/json" -d "password=test&username=test&grant_type=password&scope=read%20write&client_secret=123456&client_id=clientapp"` — Appy, Dec 09 '15 at 19:33
@DaveSyer: Also, would you recommend using InMemory store or JdbcTokenStore for persisting tokens in a production environment? — Appy, Dec 09 '15 at 19:54
Ok the problem is spring-boot is looking for the oauth specific tables in the default schema(public) instead of the test schema as defined by me. This is weird since for the rest of the user defined tables it is hitting the right schema. Disclaimer - I am not generating my schema automatically using spring boot's db initializing feature. — Appy, Dec 10 '15 at 04:03
@Appy I am stuck at this point but with mysql , would you please share your code repo if that's not sensitive . thanks — naila naseem, Apr 13 '18 at 07:38
@nailanaseem Set your current schema to make it work. See answer below. In MySql you do `USE `; before running any Sql command. — Appy, Apr 13 '18 at 20:10

score 8 · Answer 1 · answered Dec 10 '15 at 09:05

8

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.

answered Dec 10 '15 at 09:05

Dave Syer

56,583
10
155
143

So I need to use the default schema ? I tried to set the current schema in the URL but unfortunately, that did not work. – Appy Dec 10 '15 at 21:08
Most 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). – Dave Syer Dec 11 '15 at 09:37

score 0 · Answer 2 · answered Jul 21 '17 at 10:25

Resolved this by deleting everything from oauth_access_token table and adding setting tokenStore on both resource server and authentication server as follows:

@Override
    public void configure(AuthorizationServerEndpointsConfigurer endpoints) throws Exception {
        endpoints.authenticationManager(authenticationManager)
                .tokenStore(tokenStore())
}

score 0 · Answer 3 · answered Feb 10 '19 at 22:24

Got it working by

ALTER USER postgres SET search_path TO test,public;

where USER is deprecated, so instead

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.

Alternatively or more appropriately, double check test?currentSchema=test to make sure the db and the schema are as described. When working with docker, it sets up the db based off POSTGRES_USER unless specified with POSTGRES_DB so my root (your case postgres) user wasn't seeing test.

spring boot oauth2 with jdbc token store gives oauth_access_token relation doesn't exist

3 Answers3