Spring OAuth2 JDBC认证报错invalid_grant: Bad credentials求助

Hi there! Let's work through why you're hitting that "Bad credentials" error with JDBC authentication. I've gone through your code, and here are the key fixes you need to apply:

1. Add the enabled field to your user query #

Spring Security's JDBC authentication expects your user query to return three mandatory columns by default: username, password, and enabled (a boolean flag indicating if the user account is active). Your current query only selects username and password, which makes Spring treat the user as disabled—this is a common cause of authentication failures.

Update your usersByUsernameQuery and uncomment the authorities query (you'll need role data later for authorization):

@Autowired
public void globalUserDetails(AuthenticationManagerBuilder auth) throws Exception {
    System.out.println("data source : " + dataSource.getConnection().isClosed());
    auth.jdbcAuthentication().dataSource(dataSource)
        // Include the 'enabled' column in your user query
        .usersByUsernameQuery("select username, password, enabled from users where username=?")
        // Uncomment this to fetch user roles
        .authoritiesByUsernameQuery("select username, role from user_roles where username=?");
}

Make sure your users table has an enabled column set to true for your test users.

2. Configure a password encoder #

Spring Security requires a password encoder to validate credentials, even if you're using plain text (though you should never use plain text in production!). In your in-memory setup, this was handled implicitly, but JDBC authentication enforces this check.

Add a password encoder bean to your SecurityConfig:

@Bean
public PasswordEncoder passwordEncoder() {
    // Use this ONLY for testing (plain text passwords)
    return NoOpPasswordEncoder.getInstance();
    
    // For production, use BCrypt (and store hashed passwords in your DB):
    // return new BCryptPasswordEncoder();
}

Then attach it to your JDBC authentication config:

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

3. Verify role prefix matching #

Spring Security automatically prefixes roles with ROLE_ when using hasRole(). If your user_roles table stores roles like ADMIN instead of ROLE_ADMIN, you have two options:

• Update your authorities query to return the prefixed role:

  .authoritiesByUsernameQuery("select username, concat('ROLE_', role) from user_roles where username=?");
  

• Or use hasAuthority() instead of hasRole() in your ResourceServerConfig:

  .antMatchers("/api/**").access("hasAuthority('ADMIN') or hasAuthority('USER')")
  

4. Double-check your database data and Postman request #

• Confirm your users table has a test user with the username/password you're using in Postman, and enabled = true.
• Ensure your Postman request is correctly set up:
  • Use POST to /oauth/token
  • Set Content-Type to application/x-www-form-urlencoded
  • Include form data: grant_type=password, username=[your-user], password=[your-pass]
  • Add Basic Auth with client ID crmClient1 and secret crmSuperSecret

After making these changes, your JDBC OAuth2 authentication should work just like your in-memory setup did.

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