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

ikhsan · Accepted Answer · 2014-06-13 07:30:32Z

30

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=?");

answered Jun 13, 2014 at 7:30

ikhsan

8106 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hinotori Over a year ago

Why the schema is "not suitable" for PostgreSQL and MySQL? BTW, it's working... tks

Lyle Over a year ago

@Hinotori The default schema (per docs.spring.io/spring-security/site/docs/current/reference/html/…) is written in a SQL dialect for HSQLDB (particularly, PostgreSQL does not have the 'varchar_ignorecase' type which is present in the default user schema). Users of other databases need to translate that to something which works. See the documentation on initializing a database with Spring JDBC here, it has some excellent tips: docs.spring.io/spring-boot/docs/current/reference/html/…

Christian Bongiorno Over a year ago

I've done as this says and it still executes the ddl.

Manuel Jordan Over a year ago

To avoid the dll execution you must remove the withDefaultSchema() sentence

Eamorr Over a year ago

@ManuelJordan It's after midnight here... I've spent 4 hours today on this. Thank you for your comment!

Mr.Q · Accepted Answer · 2021-05-31 07:42:24Z

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);
}

Israelm · Accepted Answer · 2021-10-15 00:12:03Z

0

Schema for PGSQL:

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  INT      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)
);

User injection on data.sql

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

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

Thanks to Mr.Q. I totally used your MySQL schema and adapted it for PGSQL.

answered Oct 15, 2021 at 0:12

Israelm

1,7054 gold badges23 silver badges29 bronze badges

Collectives™ on Stack Overflow

Spring Security JDBC authentication default schema error when using PostgreSQL

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related