At Least Once

Users in the database

Replace the in-memory user with JPA entities, roles, and a database-backed UserDetailsService that accepts username or email.

  • Part 5
  • intermediate
  • about 60 minutes

You will build

users, roles and account-state tables, JPA entities, and a UserDetailsService that logs in by username or email

You will understand

how Spring Security account flags map to columns, why authentication queries fetch roles eagerly, and where the ROLE_ prefix belongs

  • six migrations, two entities, one service

OAuth client persistence is only half of the problem. The human user who authenticates at /login also needs durable identity and account state.

In this part we add:

users
roles
user_roles

and implement a database-backed UserDetailsService.

Schema

V4 — users

src/main/resources/db/migration/V4__create_users.sql:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

V5 — roles

V5__create_roles.sql:

CREATE TABLE roles (
    id UUID PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255)
);

V6 — user/role relationship

V6__create_user_roles.sql:

CREATE TABLE user_roles (
    user_id UUID NOT NULL,
    role_id UUID NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
);

V7 — Spring Security account state

V7__add_user_account_status.sql:

ALTER TABLE users
    ADD COLUMN account_non_expired BOOLEAN NOT NULL DEFAULT TRUE,
    ADD COLUMN account_non_locked BOOLEAN NOT NULL DEFAULT TRUE,
    ADD COLUMN credentials_non_expired BOOLEAN NOT NULL DEFAULT TRUE;

These booleans map directly to Spring Security’s UserDetails account-state model.

Entity model

erDiagram
    USERS ||--o{ USER_ROLES : has
    ROLES ||--o{ USER_ROLES : assigned

    USERS {
      UUID id PK
      varchar username UK
      varchar email UK
      varchar password
      boolean enabled
      boolean account_non_expired
      boolean account_non_locked
      boolean credentials_non_expired
    }

    ROLES {
      UUID id PK
      varchar name UK
      varchar description
    }

RoleEntity

Use a UUID primary key, unique role name, and optional description.

UserEntity

Important mapping:

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(
        name = "user_roles",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
)
private Set<RoleEntity> roles = new HashSet<>();

Use @PrePersist / @PreUpdate for application timestamps if you want them managed in Java as well.

Repositories

public interface RoleRepository
        extends JpaRepository<RoleEntity, UUID> {

    Optional<RoleEntity> findByName(String name);
}

For users:

public interface UserRepository
        extends JpaRepository<UserEntity, UUID> {

    @EntityGraph(attributePaths = "roles")
    Optional<UserEntity> findByUsername(String username);

    @EntityGraph(attributePaths = "roles")
    Optional<UserEntity> findByUsernameOrEmail(
            String username,
            String email
    );

    boolean existsByUsername(String username);
    boolean existsByEmail(String email);
}

Why @EntityGraph? Authentication needs authorities immediately. It is better to explicitly fetch the role relationship than to accidentally depend on an open persistence session.

We already disabled:

spring:
  jpa:
    open-in-view: false

which makes those boundaries clearer.

Implement DatabaseUserDetailsService

@Service
public class DatabaseUserDetailsService
        implements UserDetailsService {

    private final UserRepository userRepository;

    public DatabaseUserDetailsService(
            UserRepository userRepository
    ) {
        this.userRepository = userRepository;
    }

    @Override
    public UserDetails loadUserByUsername(String login) {

        UserEntity user = userRepository
                .findByUsernameOrEmail(login, login)
                .orElseThrow(() ->
                        new UsernameNotFoundException(
                                "Invalid username or email"
                        )
                );

        String[] authorities = user.getRoles()
                .stream()
                .map(role -> "ROLE_" + role.getName())
                .toArray(String[]::new);

        return User.builder()
                .username(user.getUsername())
                .password(user.getPassword())
                .authorities(authorities)
                .disabled(!user.isEnabled())
                .accountExpired(!user.isAccountNonExpired())
                .accountLocked(!user.isAccountNonLocked())
                .credentialsExpired(!user.isCredentialsNonExpired())
                .build();
    }
}

The generic error message is intentional:

Invalid username or email

Authentication endpoints should avoid becoming an easy username-enumeration oracle.

Seed the baseline USER role through Flyway

Instead of creating essential role definitions dynamically every boot, seed a deterministic role in V8__seed_default_user_role.sql:

INSERT INTO roles (id, name, description)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  'USER',
  'Default application user'
)
ON CONFLICT (name) DO NOTHING;

Later add ADMIN the same way, in V9__seed_admin_role.sql:

INSERT INTO roles (id, name, description)
VALUES (
  '00000000-0000-0000-0000-000000000002',
  'ADMIN',
  'Authorization server administrator'
)
ON CONFLICT (name) DO NOTHING;

Password encoding

Never store a raw password.

The application should pass user-provided passwords through the configured Spring PasswordEncoder:

user.setPassword(
        passwordEncoder.encode(rawPassword)
);

Authentication then uses PasswordEncoder.matches(...) internally through Spring Security.

Login by username or email

Because findByUsernameOrEmail(login, login) uses the same input for both columns, users can authenticate with either:

user

or:

user@example.com

but the resulting Spring Security principal name remains the canonical username.

That becomes useful later when we store OAuth authorization state under principal_name.

Figure: authentication path

sequenceDiagram
    participant B as Browser
    participant S as Spring Security
    participant UDS as DatabaseUserDetailsService
    participant DB as PostgreSQL

    B->>S: username/email + password
    S->>UDS: loadUserByUsername(login)
    UDS->>DB: users + roles
    DB-->>UDS: UserEntity
    UDS-->>S: UserDetails + ROLE_*
    S->>S: PasswordEncoder.matches
    S-->>B: authenticated session or failure

Verification

Create a development user (temporarily via SQL or initializer), then start the server and perform the same browser authorization flow.

Test both:

username: user
password: password

and:

username: user@example.com
password: password

Both should authenticate the same account.

Common mistakes

Lazy initialization error for roles

Use an entity graph on authentication queries or another explicit fetch strategy. Do not re-enable Open Session in View simply to hide a repository boundary issue.

Roles missing ROLE_

Spring’s role checks expect authorities such as:

ROLE_ADMIN

Store the role in your domain as:

ADMIN

and add the prefix at the Spring Security boundary.

Hibernate tries to create tables

Confirm:

spring:
  jpa:
    hibernate:
      ddl-auto: validate

Flyway should remain the schema owner.

The code

This part corresponds to these commits in the repository:

  • aa39e61 feat: add user and role persistence schema
  • db7ba08 feat: add database-backed user authentication
  • 7326d9c feat: support account status and email login

Check out the last one to see the project exactly as this part leaves it:

git checkout 7326d9c

References

Next: public registration, API error handling, and keeping all demo credentials strictly inside the dev profile.