Spring Security创建springSecurityFilterChain Bean失败问题求助

问题描述 #

搭建首个Spring Security认证功能时，启动报错Error creating bean with name 'springSecurityFilterChain'，核心异常为ERROR: The "users" relation does not exist，但已手动创建所有数据库表和字段，排除名称拼写错误。同时org.springframework.boot Maven插件存在红色高亮提示。

相关代码与配置 #

SecurityConfig中的users Bean方法 #

@Bean
public JdbcUserDetailsManager users(DataSource dataSource) {
    UserDetails user = User.builder()
            .username("user")
            .password("{bcrypt}$2y$12$0./a1TvfPwxj.OTlQLF1HONCpHXHtrcrn7CR5sPE7UHSWLbkPjxZG")
            .roles("USER")
            .build();
    UserDetails admin = User.builder()
            .username("admin")
            .password("{bcrypt}$2y$12$0./a1TvfPwxj.OTlQLF1HONCpHXHtrcrn7CR5sPE7UHSWLbkPjxZG")
            .roles("ADMIN", "USER")
            .build();
    JdbcUserDetailsManager jdbcUserDetailsManager = new JdbcUserDetailsManager(dataSource);
    if (jdbcUserDetailsManager.userExists(user.getUsername())) {
        jdbcUserDetailsManager.deleteUser(user.getUsername());
    }
    if (jdbcUserDetailsManager.userExists(admin.getUsername())) {
        jdbcUserDetailsManager.deleteUser(admin.getUsername());
    }
    jdbcUserDetailsManager.createUser(user);
    jdbcUserDetailsManager.createUser(admin);
    return jdbcUserDetailsManager;
}

完整异常栈 #

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'springSecurityFilterChain' defined in class path resource [org/springframework/security/config/annotation/web/configuration/WebSecurityConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.servlet.Filter]: Factory method 'springSecurityFilterChain' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'users' defined in class path resource [com/example/vkr2/configs/SecurityConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.security.provisioning.JdbcUserDetailsManager]: Factory method 'users' threw exception; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select username from users where username = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: The "users" relation does not exist

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.servlet.Filter]: Factory method 'springSecurityFilterChain' threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'users' defined in class path resource [com/example/vkr2/configs/SecurityConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.security.provisioning.JdbcUserDetailsManager]: Factory method 'users' threw exception; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select username from users where username = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: The "users" relation does not exist

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'users' defined in class path resource [com/example/vkr2/configs/SecurityConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.springframework.security.provisioning.JdbcUserDetailsManager]: Factory method 'users' threw exception; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select username from users where username = ?]; nested exception is org.postgresql.util.PSQLException: ERROR: The "users" relation does not exist

application.yaml配置 #

server:
  port: 8080

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQL95Dialect
        temp:
          use_jdbc_metadata_defaults: false
  datasource:
    driver-class-name: org.postgresql.Driver
    url: jdbc:postgresql://localhost:5432/postgres?currentSchema=security
    username: postgres
    password: 12345

pom.xml配置 #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>vkr2</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>vkr2</name>
    <description>vkr2</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

问题排查与解决 #

1. users表不存在的核心原因 #

JdbcUserDetailsManager默认使用的SQL语句查询users表，可能的问题点：

• Schema匹配问题：配置指定了currentSchema=security，但手动创建的表不在该Schema下，或PostgreSQL中Schema名称大小写敏感（如创建的是Security而非security）。
• 表名大小写问题：PostgreSQL默认将小写表名转为小写存储，若手动创建的是大写表名（如USERS），与JdbcUserDetailsManager使用的小写users不匹配。
• 表结构不匹配：JdbcUserDetailsManager要求的默认表结构需完全一致，缺失字段或类型不符也会导致查询失败。

2. users表问题的解决方法 #

• 验证Schema与表的存在性：登录PostgreSQL执行\dt security.*，确认users表存在于securitySchema下。
• 指定自定义SQL语句：若表名/字段名与默认不一致，在JdbcUserDetailsManager中手动设置SQL：

  jdbcUserDetailsManager.setUsersByUsernameQuery("select username,password,enabled from security.users where username = ?");
  jdbcUserDetailsManager.setAuthoritiesByUsernameQuery("select username,authority from security.authorities where username = ?");
  

• 延迟Bean初始化：若表初始化（如Flyway/Liquibase）在Security Bean之后执行，将users Bean设置为懒加载：

  @Bean
  @Lazy
  public JdbcUserDetailsManager users(DataSource dataSource) {
      // 原有代码
  }
  

3. Maven插件红色高亮的解决方法 #

• 刷新Maven依赖：在IDE中执行Maven -> Reload Project，重新下载插件元数据。
• 确认插件版本兼容性：Spring Boot 2.6.4对应的spring-boot-maven-plugin版本需与parent版本一致，若仍有问题，手动指定版本：

  <plugin>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-maven-plugin</artifactId>
      <version>2.6.4</version>
      <configuration>
          <excludes>
              <exclude>
                  <groupId>org.projectlombok</groupId>
                  <artifactId>lombok</artifactId>
              </exclude>
          </excludes>
      </configuration>
  </plugin>
  

• 清理IDE缓存：如IntelliJ IDEA执行File -> Invalidate Caches... -> Invalidate and Restart。

内容的提问来源于stack exchange，提问作者user19862959