Spring Boot JdbcUserDetailsManager can't create tables and insert

Question

I'm trying to make an application with spring security and the JdbcUserDetailsManager

When I start the spring boot application I get an error message:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into users (username, password, enabled) values (?,?,?)]

I thought this is cause there is no table 'users', although the JdbcUserDetailsManager should create it on its own.

I did create the tables on manually and still the error persists.

Any Idea What could be the cause?

@Configuration
public class UserDetailsConfig {


    @Autowired
    private DataSource dataSource;

    @Bean
    UserDetailsManager users(DataSource dataSource) {
        System.out.println();
        UserDetails defaultUser = User.builder()
                .username("admin")
                .password(this.passwordEncoder().encode("admin"))
                .roles("ADMIN", "USER")
                .build();
        JdbcUserDetailsManager users = new JdbcUserDetailsManager(dataSource);
        try {
            //users.deleteUser(defaultUser.getUsername());
            users.createUser(defaultUser);
        } catch (Exception e) {
            System.out.println("database not yet initialized");
            e.printStackTrace();
        }
        return users;
    }

    @Bean
    PasswordEncoder passwordEncoder() {
        return new BCryptPasswordEncoder();
    }

}

spring.datasource.url=jdbc:postgresql://localhost:5432/food_production
spring.datasource.username=postgres
spring.datasource.password=123
spring.datasource.driver-class-name=org.postgresql.Driver

spring.jpa.hibernate.ddl-auto=update

spring.jpa.show-sql=true

server.port=6969

spring.security.user.name=admin
spring.security.user.password=admin
spring.security.user.roles=admin

spring version: 3.2.2

Java version 17

Postgresql version 16.2

Answer 1

Ok, so I found that what M. Deinum sad is true, there are these ddls, but I tried to create the database with the ddl script, but varchar_ignorecase() does not exist in postgres so you have to remove the _ignorecase then itworks;

Solution:

remove _ignorecase from the spring suggested script when initializing the database