ForeignKey between User and UserRole with Spring Security-CodePudding

Actually i have an authentication that works with rbac. The problem is that, i encoutered a case where a user was deleted, but in the database, the user id and role were still present.

When the user was recreated he got the roles of a former user who had this id. Acutally i can't delete user role of a user that has been delete because it's an enum..

Is it possible to create a relationship between users and roles while keeping this enumeration principle? Or another solution ?

public class AppUser {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    ....

    @ElementCollection(fetch = FetchType.EAGER)
    @Enumerated(EnumType.STRING)
    List<AppUserRole> appUserRoles;
    
}

public enum AppUserRole implements GrantedAuthority {
    ROLE_ADMIN, ROLE_DEMO;

    public String getAuthority() {
        return name();
    }

}

CodePudding user response：

When deleting an entity with an @ElementCollection the delete is cascaded automatically. When doing this through SQL this (might) not be the case, depending on how cascade rules are applied in your database.

But with your setup that should happen automatically.

CodePudding user response：

Here is an example of how the roles table for the @ElementCollection can be created (PostgreSQL syntax):

CREATE TABLE user_roles (
  user_id int,
  role text,
  PRIMARY KEY (user_id, role),
  FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

The foreign key with the ON DELETE CASCADE makes sure that the associated roles are deleted when user is deleted.