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

score 29 · Accepted Answer · answered Jun 13 '14 at 07:30

29

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 '14 at 07:30

ikhsan

800
5
11

2

Why the schema is "not suitable" for PostgreSQL and MySQL? BTW, it's working... tks – Hinotori Nov 17 '15 at 19:34
1

@Hinotori The default schema (per http://docs.spring.io/spring-security/site/docs/current/reference/html/appendix-schema.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: http://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html#howto-initialize-a-database-using-spring-jdbc – Lyle Oct 13 '16 at 22:11
I've done as this says and it still executes the ddl. – Christian Bongiorno Jun 01 '17 at 21:19
4

To avoid the `dll` execution you must remove the `withDefaultSchema()` sentence – Manuel Jordan Aug 21 '17 at 19:35
1

@ManuelJordan It's after midnight here... I've spent 4 hours today on this. Thank you for your comment! – Eamorr Apr 07 '18 at 23:26

score 1 · Answer 2 · answered May 31 '21 at 07:42

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

score 0 · Answer 3 · answered Oct 15 '21 at 00:12

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.

Spring Security JDBC authentication default schema error when using PostgreSQL

3 Answers3

Linked