使用PostgreSQL时出现Spring Security JDBC身份验证默认模式错误

Question

真的不可能使用默认模式的Spring Security与PostgreSQL，因为部分varchar_ignorecase不存在不能被替换？

我只是在测试默认设置：

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

下面是错误：

引起原因：
org.springframework.beans.factory.Bean定义存储异常：工厂方法[公共javax.servlet.过滤器org.springframework.security.config.annotation.web.configuration.WebSecurityConfiguration.springSecurityFilterChain（）抛出java.lang.异常]抛出异常；

嵌套异常是
org。springframework。jdbc。数据源。初始化。ScriptStatementFailedException:未能在资源类路径resource的第1行执行SQL脚本语句
[org/springframework/security/core/userdetails/jdbc/users.ddl]：创建表用户（username varchar_ignorecase（50）not null主键，password varchar_ignorecase（500）not null，enabled boolean not null）；

嵌套异常是
org.postgresql.util.PSQLExcture： ERROR： type"varchar_ignorecase"不存在

Answer 1

PGSQL的架构：

模式。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)
);

data.sql上的用户注入

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

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

感谢Mr.Q.我完全使用了你的MySQL模式，并将其适用于PGSQL。

Answer 2

没有必要添加DefaultSchema（）。只需用正确的列名和外键约束定义schema.sql和data.sql。下面是MySQL（在Spring Boot中）的示例配置。

模式。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)
);

然后定义用户和权限（data.sql）

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

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

最后，定义身份验证机制

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

Answer 3

由于默认模式不适合PostgreSQL，我需要创建自己的模式，并实现自己的用户和权限查询查询。

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