title: Spring Boot 2.1.1 Issue Accessing Tables in PostgreSQL that are within Oauth2
body: <p>I am trying to add postgresql support for OAuth2 with Spring Boot after following some examples to clean up my code.</p>
  
  <p>The problem is that I have to add a schema manually to my own schema scripts for the database to create despite specifying certain properties  in the latest Spring Boot. Only after doing so do the tables become recognizable. Also, OAuth2 continues to fail, not finding any table. The issue seems schema related.</p>
  
  <p>I was able to get my own schema to be recognized without specifying one in the entity by disabling auto configuration but cannot figure out why OAuth2 tables are not found unless I use the public schema.</p>
  
  <p>I have added the following properties file with attempts to get Spring boot to recognize my schemas:</p>
  
  <pre><code>    server.port=8081
  
      ## Spring DATASOURCE (DataSourceAutoConfiguration &amp; DataSourceProperties)
      # The SQL dialect makes Hibernate generate better SQL for the chosen database
  
      # Hibernate ddl auto (create, create-drop, validate, update)
      spring.datasource.jdbcUrl=jdbc:postgresql://xxxxxx:xxx/oauth2
      spring.datasource.username= xxxxx
      spring.datasource.password= xxxxx
      spring.datasource.hikari.minimumIdle=5
  spring.datasource.hikari.maximumPoolSize=20
  spring.datasource.hikari.idleTimeout=30000
  spring.datasource.hikari.poolName=SpringBootJPAHikariCP
  spring.datasource.hikari.maxLifetime=2000000
  spring.datasource.hikari.connectionTimeout=30000
  spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true;
  spring.jpa.properties.hibernate.current_session_context_class=thread
  spring.jpa.hibernate.ddl-auto=none
  spring.datasource.tomcat.default-auto-commit=true
  
  # The SQL dialect makes Hibernate generate better SQL for the chosen database
  spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQL95Dialect
  spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal.HikariCPConnectionProvider
  spring.jpa.properties.hibernate.show_sql=true
  spring.jpa.properties.hibernate.format_sql=true
  spring.jpa.properties.hibernate.use_sql=true
  spring.jpa.properties.hibernate.id.new_generator_mappings=false
  spring.jpa.properties.hibernate.search.autoregister_listeners=false
  spring.jpa.properties.hibernate.bytecode.use_reflection_optimizer=false
  spring.jpa.properties.hibernate.default_schema = public
  
  
  # spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
  # spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  
  # Hibernate ddl auto (create, create-drop, validate, update)
  
  
  spring.thymeleaf.templateResolverOrder=1
  logging.level.org.springframework=INFO
  entitymanager.packagesToScan = com.si.model
  
  
  logging.level.org.springframework.web=DEBUG
  logging.level.guru.springframework.controllers=DEBUG
  logging.level.org.hibernate=INFO
  spring.main.allow-bean-definition-overriding=true
  
  spring.thymeleaf.cache=false
  
  logging.level.org.hibernate.SQL=DEBUG
  logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
  
  spring.datasource.schema=classpath:schema.sql
  spring.datasource.initialization-mode=always
  spring.jpa.properties.hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  
  spring.template.cache: false
      spring.jpa.properties.hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
  </code></pre>
  
  <p>I have also tried setting the Data Source for each object in the Authorization Server:</p>
  
  <pre><code>@Configuration
  @EnableAuthorizationServer
  class AuthorizationServerConfiguration extends AuthorizationServerConfigurerAdapter {
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    def oauthDataSource: DataSource = DataSourceBuilder.create.build
  
    @Autowired
    private var authenticationManager: AuthenticationManager = null
  
    @Bean
    def clientDetailsService = new JdbcClientDetailsService(oauthDataSource)
  
    @Bean
    def tokenStore = new JdbcTokenStore(oauthDataSource)
  
    @Bean
    def approvalStore = new JdbcApprovalStore(oauthDataSource)
  
    @Bean
    def authorizationCodeServices = new JdbcAuthorizationCodeServices(oauthDataSource)
  
    @Bean
    def passwordEncoder(): Pbkdf2PasswordEncoder = new Pbkdf2PasswordEncoder()
  
    @throws[Exception]
    override def configure(clients: ClientDetailsServiceConfigurer): Unit = {
      clients.jdbc(oauthDataSource)
    }
  
    @throws[Exception]
    override def configure(oauthServer: AuthorizationServerSecurityConfigurer): Unit = {
      oauthServer.passwordEncoder(passwordEncoder)
    }
  
    @throws[Exception]
    override def configure(endpoints: AuthorizationServerEndpointsConfigurer): Unit = {
      endpoints
          .authenticationManager(authenticationManager)
          .approvalStore(approvalStore)
          .authorizationCodeServices(authorizationCodeServices)
          .tokenStore(tokenStore)
    }
  }
  </code></pre>
  
  <p>Finally, I set up my web configuration as follows:</p>
  
  <pre><code>class WebSecurityConfiguration extends WebSecurityConfigurerAdapter {
    @Autowired
    private var passwordEncoder: Pbkdf2PasswordEncoder = null
  
    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    def dataSource: DataSource = DataSourceBuilder.create.`type`(classOf[HikariDataSource]).build
  
    @Bean
    @throws[Exception]
    override def authenticationManagerBean: AuthenticationManager = super.authenticationManagerBean
  
    @Bean
    @throws[Exception]
    override def userDetailsServiceBean = new JdbcUserDetails()
  
    @throws[Exception]
    override def configure(web: WebSecurity): Unit = {
      web.ignoring.antMatchers("/webjars/**", "/resources/**")
    }
  
    @throws[Exception]
    override protected def configure(http: HttpSecurity): Unit = {
      http
        .csrf().disable()
        .authorizeRequests
          .antMatchers("/login", "/logout.do")
            .permitAll
          .antMatchers("/**")
            .authenticated
          .and.
          formLogin
            .loginProcessingUrl("/login.do")
            .usernameParameter("username")
            .passwordParameter("password")
            .loginPage("/login")
          .and
          .logout
            .logoutRequestMatcher(new AntPathRequestMatcher("/logout.do"))
        .and
          .userDetailsService(userDetailsServiceBean)
    }
  
    @throws[Exception]
    override protected def configure(auth: AuthenticationManagerBuilder): Unit = {
      auth
        .userDetailsService(userDetailsServiceBean)
        .passwordEncoder(passwordEncoder)
    }
  }
  </code></pre>
  
  <p>For some reason, I had to set my schemas to public in the schema script:</p>
  
  <pre><code>   drop table if exists public.oauth_client_token cascade;
  create table if not exists public.oauth_client_token (
    token_id VARCHAR(255) not null primary key,
    token bytea,
    authentication_id VARCHAR(255),
    user_name VARCHAR(255),
    client_id VARCHAR(255)
  );
  
  drop table if exists public.oauth_client_details cascade;
  CREATE TABLE if not exists public.oauth_client_details (
    client_id varchar(255) NOT NULL primary key,
    resource_ids varchar(255) DEFAULT NULL,
    client_secret varchar(255) DEFAULT NULL,
    scope varchar(255) DEFAULT NULL,
    authorized_grant_types varchar(255) DEFAULT NULL,
    web_server_redirect_uri varchar(255) DEFAULT NULL,
    authorities varchar(255) DEFAULT NULL,
    access_token_validity integer,
    refresh_token_validity integer,
    additional_information varchar(255) DEFAULT NULL,
    autoapprove varchar(255) DEFAULT NULL
  );
  
  drop table if exists public.oauth_access_token cascade;
  create table if not exists public.oauth_access_token(
    token_id VARCHAR(255),
    token bytea,
    authentication_id VARCHAR(255),
    user_name VARCHAR(255),
    client_id VARCHAR(255),
    authentication bytea,
    refresh_token VARCHAR(255)
  );
  
  drop table if exists public.oauth_refresh_token cascade;
  create table if not exists public.oauth_refresh_token(
    token_id VARCHAR(255),
    token bytea,
    authentication bytea
  );
  drop table if exists public.authorities cascade;
  CREATE TABLE if not exists public.authorities (
    id  bigserial not null primary key,
    authority varchar(255)
  );
  
  drop table if exists public.credentials cascade;
  CREATE TABLE if not exists public.credentials (
    id  bigserial not null primary key,
    enabled boolean not null,
    name varchar(255) not null,
    password varchar(255) not null,
    version integer
  );
  
  drop table if exists public.credentials_authorities cascade;
  CREATE TABLE if not exists public.credentials_authorities (
    credentials_id bigint not null,
    authorities_id bigint not null
  );
  
  drop table if exists public.oauth_code cascade;
  create table if not exists public.oauth_code (
    code VARCHAR(255),
    authentication bytea
  );
  
  drop table if exists public.oauth_approvals cascade;
  create table if not exists public.oauth_approvals (
      userId VARCHAR(255),
      clientId VARCHAR(255),
      scope VARCHAR(255),
      status VARCHAR(10),
      expiresAt timestamp without time zone,
      lastModifiedAt timestamp without time zone
  );
  </code></pre>
  
  <p>Access to the oauth2 tables from the clientDetailsRepository or Oauth2 application results in errors such as the following:</p>
  
  <pre><code>org.postgresql.util.PSQLException: ERROR: relation "oauth_client_details" does not exist
  </code></pre>
  
  <p>As a last resort, I set the search path in postgresql to public for my user and still get this error.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>The following query works in pgadmin4 and is the source of my failure:</p>
  
  <pre><code>select client_id, client_secret, resource_ids, scope, authorized_grant_types, web_server_redirect_uri, authorities, access_token_validity, refresh_token_validity, additional_information, autoapprove from oauth_client_details order by client_id
  </code></pre>
  
  <p>Any ideas as to why this happens? How can I resolve this?</p>