Spring Security JDBC authentication default schema error when using PostgreSQL

Question

Is it really impossible to use default schema for Spring Security with PostgreSQL, because the part "varchar_ignorecase" does not exist can't be replaced?

I'm just testing the default settings:

auth.jdbcAuthentication()
            .dataSource(dataSource)
            .withDefaultSchema();

And below is the error:

Caused by:
org.springframework.beans.factory.BeanDefinitionStoreException: Factory method [public javax.servlet.Filter org.springframework.security.config.annotation.web.configuration.WebSecurityConfiguration.springSecurityFilterChain() throws java.lang.Exception] threw exception;

nested exception is
org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource
[org/springframework/security/core/userdetails/jdbc/users.ddl]: create table users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null);

nested exception is
org.postgresql.util.PSQLException: ERROR: type "varchar_ignorecase" does not exist

Answer 1

Cause the default schema is not suitable for PostgreSQL, I need to create my own schema and implement own query for user and authority query.

auth.jdbcAuthentication()
                .dataSource(dataSource)
                .usersByUsernameQuery(
                        "select username,password, enabled from users where username=?")
                .authoritiesByUsernameQuery(
                        "select username, role from user_roles where username=?");

Answer 2

There is no need to adding withDefaultSchema(). simply define your schema.sql and data.sql with proper column names and foreign key constraints. Following is the example configuration for Mysql (in the Spring Boot).

schema.sql

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS authorities;

CREATE TABLE users
(
   username VARCHAR(50)  NOT NULL,
   password VARCHAR(100) NOT NULL,
   enabled  TINYINT      NOT NULL DEFAULT 1,
   PRIMARY KEY (username)
 );

CREATE TABLE authorities
(
username  VARCHAR(50) NOT NULL,
authority VARCHAR(50) NOT NULL,
FOREIGN KEY (username) REFERENCES users (username)
);

Then define users and authorities (data.sql)

INSERT INTO users (username, password, enabled)
values ('user','pass',1);

INSERT INTO authorities (username, authority)
values ('user', 'ROLE_USER');

finally, define the authentication mechanism

protected void configure(AuthenticationManagerBuilder auth) throws Exception {
    auth
            .jdbcAuthentication()
            .passwordEncoder(NoOpPasswordEncoder.getInstance())
            .dataSource(dataSource);
}